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

Suboptimal join plan if there are selective non-indexed predicates involved [CORE2078] #2513

Closed
firebird-automations opened this issue Sep 12, 2008 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

Attachments:
TEST1.rar

The optimizer always had some trivial heuristics to estimate the effective stream selectivity even if no indices could be used for the retrieval. But this code hasn't been migrated into the new (post-ODS10) optimizer logic, thus causing ineffective join orders chosen for the cases when non-indexed predicates may noticeably truncate the leading stream output.

Commits: a7b6702 a082a6f 4b79215 cd08735 0c9b728 ddb3e8f

====== Test Details ======

#⁠#⁠#⁠ ACHTUNG #⁠#⁠#⁠

1) Since time of commit (3.0.0.31836, 15-May-2015) passed on only on SuperClassic. Failed on SuperServer. How could it be if test isn`t related to arch. specifics ?! Last letter to dimitr: 16-may-2015 13:55, subject: 'fix core-2078';

2) Representation of double precision values from rdb$indices.rdb$statistics differs on Linux vs Windows in 15th digit. Decided to change out-argument of procedure that does SET STATISTICS INDEX ... to numeric(12, 10);

3) Seems that some investigations should be in optimizer due to poor predictable PLAN when change initial values of rows number in tables.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Open [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

Fix Version: 2.1.2 [ 10270 ]

Fix Version: 2.0.5 [ 10222 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Archived database backup is attached. Sorry for the RAR format, but ZIP cannot compress it into less than 10MB enforced by Jira.

Test query:

select count(*)
from small_table sa
join big_table pg on pg.id_small = sa.id_small
join medium_table psg on psg.id_medium = pg.id_medium
where sa.id_fk_1 = 0 -- selective non-indexed boolean

Auto-generated BAD plan:
PLAN JOIN (PSG NATURAL, PG INDEX (BIG_TABLE_IDX2), SA INDEX (SMALL_TABLE_IDX1))
Execute time = 10s

Expected GOOD plan:
PLAN JOIN (SA NATURAL, PG INDEX (BIG_TABLE_IDX1), PSG INDEX (PK_MEDIUM_TABLE))
Execute time = 1.5s

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Attachment: TEST1.rar [ 11050 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

environment: Any => Any, ODS11 and higher only

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done with caveats

Test Details: #⁠#⁠#⁠ ACHTUNG #⁠#⁠#⁠

1) Since time of commit (3.0.0.31836, 15-May-2015) passed on only on SuperClassic. Failed on SuperServer. How could it be if test isn`t related to arch. specifics ?! Last letter to dimitr: 16-may-2015 13:55, subject: 'fix core-2078';

2) Representation of double precision values from rdb$indices.rdb$statistics differs on Linux vs Windows in 15th digit. Decided to change out-argument of procedure that does SET STATISTICS INDEX ... to numeric(12, 10);

3) Seems that some investigations should be in optimizer due to poor predictable PLAN when change initial values of rows number in tables.

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