Issue Details (XML | Word | Printable)

Key: CORE-5146
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Marco Van Kan
Votes: 0
Watchers: 4
Operations

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

Suboptimal join order if one table has a selective predicate and MIN is calculated for the other one

Created: 10/Mar/16 10:11 AM   Updated: 08/Apr/16 06:18 AM
Component/s: None
Affects Version/s: 3.0 RC1, 3.0 RC2, 4.0 Initial
Fix Version/s: 3.0.0, 4.0 Alpha 1

File Attachments: 1. Text File fb25x-checked.log (3 kB)


QA Status: 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))


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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 10/Mar/16 10:23 AM
What are the exact plans? AFAIU, the join order is different between v2.5 and v3.0.

Marco Van Kan added a comment - 10/Mar/16 10:33 AM
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.

Pavel Zotov added a comment - 04/Apr/16 12:23 PM
> 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).

Marco Van Kan added a comment - 06/Apr/16 09:09 AM
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

Dmitry Yemanov added a comment - 07/Apr/16 01:39 PM
It should be fixed now. Please try the next snapshot and report back.

Marco Van Kan added a comment - 08/Apr/16 06:18 AM
Tested on snapshot build 3.0.0.32465. Performance is ok, issue seems to be fixed! Thanks!