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

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: 27/May/15 04:00 PM
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

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

Environment: Any, ODS11 and higher only

QA Status: Done with caveats
Test Details:
### ACHTUNG ###

1) Since time of commit (, 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.

 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   Change History   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:
  Execute time = 10s

Expected GOOD plan:
  Execute time = 1.5s