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 order if one table has a selective predicate and MIN is calculated for the other one [CORE5146] #5429

Closed
firebird-issue-importer opened this issue Mar 10, 2016 · 14 comments

Comments

@firebird-issue-importer

Submitted by: Marco Van Kan (mvk)

Attachments:
fb25x-checked.log

While testing FB 3.0 (RC2) we encountered a bad performance on a simple query:

SELECT MIN(HE.DATEVALUE)
FROM HOURITEMS HI INNER JOIN DIHOURENTRIES HE ON HI.HOURITEMID = HE.HOURITEMID
WHERE HI.PROJECTID = 30762

Indexes are on all fields of the query above (HI.HOURITEMID, HE.HOURITEMID, HI.PROJECTID AND HE.DATEVALUE)

FB 2.5 handles this query very fast (0.07sec). FB 3.0 is much slower (1.6sec). This performance lag is caused by 'weird' use of index. Below a comparison:

FB 2.5 uses the indexes on HI.PROJECTID AND HE.HOURITEMID
FB 3.0 RC2 uses the indexes on HI.HOURITEMID AND HE.DATEVALUE >> HI.PROJECTID is ignored!

A 'workaround' for FB 3.0 could be something like:

SELECT MIN(HE.DATEVALUE)
, COUNT(0) AS DUMMY
FROM HOURITEMS HI INNER JOIN DIHOURENTRIES HE ON HI.HOURITEMID = HE.HOURITEMID
WHERE HI.PROJECTID = 30762

Commits: eb68245 54cde2c 1472ab8 8905e15

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

WI-T4.0.0.98: PLAN JOIN (HE ORDER HE_DATEVL, HI INDEX (HI_ITEMID, HI_PROJID))
WI-T4.0.0.113: PLAN JOIN (HI INDEX (HI_PROJID), HE INDEX (HE_ITEMID))

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2016

Commented by: @dyemanov

What are the exact plans? AFAIU, the join order is different between v2.5 and v3.0.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2016

Commented by: Marco Van Kan (mvk)

FB 2.5:
PLAN JOIN (HI INDEX (HI.PROJECTID), HE INDEX (HE.HOURITEMID))

FB 3.0:
PLAN JOIN (HE ORDER HE.DATEVALUE, HI INDEX (HI.HOURITEMID))

The index on HE.DATEVALUE is ascending. When changing the query to select MAX instead of MIN, the performance is ok.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2016

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2016

Modified by: @dyemanov

summary: Index selectivity => Suboptimal join order if one table has a selective predicate and MIN is calculated for the other one

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 10, 2016

Modified by: @dyemanov

Version: 3.0 RC2 [ 10048 ]

Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 31, 2016

Modified by: @dyemanov

Fix Version: 3.0.0 [ 10740 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 31, 2016

Modified by: @dyemanov

Version: 4.0 Initial [ 10621 ]

Version: 3.0 RC1 [ 10584 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 2, 2016

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 4, 2016

Commented by: @pavel-zotov

> FB 2.5:
> PLAN JOIN (HI INDEX (HI.PROJECTID), HE INDEX (HE.HOURITEMID))

What exact version of 2.5 ? I can't get such plan on official releases 2.5.1 ... 2.5.5 plus recent 2.5.6 (checked builds 26980 and 26990 ).

Following script:

recreate table houritems(houritemid int, projectid int); -- hi
recreate table dihourentries(houritemid int, datevalue int); -- he

create index hi_itemid on houritems(houritemid);
create index hi_projid on houritems(projectid);
create index he_itemid on dihourentries(houritemid);
create index he_datevl on dihourentries(datevalue);

set plan on;

select min(he.datevalue)
from houritems hi inner join dihourentries he on hi.houritemid = he.houritemid
where hi.projectid = 30762;

-- produces on ALL versions of FB 2.5:

PLAN JOIN (HE ORDER HE_DATEVL, HI INDEX (HI_ITEMID, HI_PROJID))

(see attached file).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 4, 2016

Modified by: @pavel-zotov

Attachment: fb25x-checked.log [ 12934 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 4, 2016

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

Test Details: WI-T4.0.0.98: PLAN JOIN (HE ORDER HE_DATEVL, HI INDEX (HI_ITEMID, HI_PROJID))
WI-T4.0.0.113: PLAN JOIN (HI INDEX (HI_PROJID), HE INDEX (HE_ITEMID))

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 6, 2016

Commented by: Marco Van Kan (mvk)

I've tested with the fixed build and this is ok now.

If we run the query with a slightly different where-clause, the performance is poor again in FB 3.0 (FB 2.5 is much faster). Could you please look at this query and optimize the use of indices::

SELECT MIN(HE.DATEVALUE)
FROM HOURITEMS HI INNER JOIN DIHOURENTRIES HE ON HI.HOURITEMID = HE.HOURITEMID
WHERE CASE :USE_PROJECT_1 WHEN 1 THEN HI.PROJECTID1 ELSE HI.PROJECTID END = 30762

FB 2.5.2:
PLAN JOIN (HI NATURAL, HE INDEX (HE.HOURITEMID)) >> 0.2sec

FB 3.0 (3.0.0.32444-0_x64):
PLAN JOIN (HE ORDER HE.DATEVALUE, HI INDEX (HI.HOURITEMID)) >> 2.3sec

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 7, 2016

Commented by: @dyemanov

It should be fixed now. Please try the next snapshot and report back.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 8, 2016

Commented by: Marco Van Kan (mvk)

Tested on snapshot build 3.0.0.32465. Performance is ok, issue seems to be fixed! Thanks!

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