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
Comments
Commented by: @dyemanov What are the exact plans? AFAIU, the join order is different between v2.5 and v3.0. |
Commented by: Marco Van Kan (mvk) FB 2.5: FB 3.0: The index on HE.DATEVALUE is ascending. When changing the query to select MAX instead of MIN, the performance is ok. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovsummary: Index selectivity => Suboptimal join order if one table has a selective predicate and MIN is calculated for the other one |
Modified by: @dyemanovFix Version: 3.0.0 [ 10740 ] |
Modified by: @dyemanovVersion: 4.0 Initial [ 10621 ] Version: 3.0 RC1 [ 10584 ] Fix Version: 4.0 Alpha 1 [ 10731 ] |
Commented by: @pavel-zotov > FB 2.5: 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 create index hi_itemid on houritems(houritemid); set plan on; select min(he.datevalue) -- produces on ALL versions of FB 2.5: PLAN JOIN (HE ORDER HE_DATEVL, HI INDEX (HI_ITEMID, HI_PROJID)) (see attached file). |
Modified by: @pavel-zotovAttachment: fb25x-checked.log [ 12934 ] |
Modified by: @pavel-zotovstatus: 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)) |
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) FB 2.5.2: FB 3.0 (3.0.0.32444-0_x64): |
Commented by: @dyemanov It should be fixed now. Please try the next snapshot and report back. |
Commented by: Marco Van Kan (mvk) Tested on snapshot build 3.0.0.32465. Performance is ok, issue seems to be fixed! Thanks! |
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))
The text was updated successfully, but these errors were encountered: