Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

delete from T where T.C in (select T.C from T group by T.C having count(1) = 1) never stops [CORE2142] #2573

Open
firebird-automations opened this issue Oct 23, 2008 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Cosmin Apreutesei (cosmin_ap2)

Relate to CORE3518

Votes: 2

my exact query was:

delete from streets where street_type in (
select s.street_type from streets s group by s.street_type having count(1) = 1
)

the streets table gets 12 rows in the subselect, and has a total of 21000 rows.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What plan does this query use?

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

Plan
PLAN SORT ((S NATURAL))
PLAN (STREETS NATURAL)

Adapted Plan
PLAN SORT ((S NATURAL)) PLAN (STREETS NATURAL)

.. I have no index on street_type.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Then it sounds more like a "designed pitfall" rather than a bug. In Firebird, you have to index the street_type field in order to get a good performance.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cosmin Apreutesei (cosmin_ap2)

I know, I just thought it's missing an opportunity for optimization since the inner select returns only 12 rows. It gets the feeling of never stopping (I killed the query after about 5 minutes) even when the inner select returns 0 rows.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This is planned for improvement, maybe in the next version.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE3518 [ CORE3518 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 3.0 Beta 1 [ 10332 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants