Issue Details (XML | Word | Printable)

Key: CORE-2078
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Suboptimal join plan if there are selective non-indexed predicates involved

Created: 12/Sep/08 08:55 AM   Updated: 23/Feb/11 09:42 AM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1
Fix Version/s: 2.0.5, 2.1.2, 2.5 Beta 1

Time Tracking:
Not Specified

File Attachments: 1. File TEST1.rar (6.94 MB)

Environment: Any, ODS11 and higher only

Planning Status: Unspecified


 Description  « Hide
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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 12/Sep/08 09:15 AM
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