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:
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.

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).

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

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!