Hi All,

We recently upgraded one of our servers from firebird 2.1.1 classic to firebird 2.1.2 classic, however, an issue with the way in which one of our statements was optimised forced us to roll back almost immediately.

The query essentially checks if a record in the TRACK table has 1 or more children records in the EVENT table.

select count(*)
from track
join event on track.trackno = event.trackno
where track.waybill='TEST0004' and
event.eventtype='D' and
event.manifest=0 and
event.hub='GRJ'
and event.branch=0;

TRACK has a compound primary key (WAYBILL, PARCELNO) and an index on TRACK.TRACKNO.

EVENT has a compound primary key (TRACKNO, HUB, EVENTTYPE, MANIFEST) and an index on EVENT.MANIFEST.

Strangely, the issue is only present on firebird 2.1.2 and only when the event table has a very large number of records ~40 million. We have only tested the classic build and this only in a Linux (CentOS) environment. A backup and restore was not done between migrations between 2.1.1 and 2.1.2 - to my knowledge this is not required.

Plan chosen on 2.1.1 (all conditions):

PLAN JOIN (TRACK INDEX (RDB$PRIMARY144), EVENT INDEX (RDB$PRIMARY158))

Plan chosen on 2.1.2 (~10 million records):

PLAN JOIN (TRACK INDEX (RDB$PRIMARY144), EVENT INDEX (RDB$PRIMARY158))

Plan chosen on 2.1.2 (~40 million records):

PLAN JOIN (EVENT INDEX (EV_MANIFEST), TRACK INDEX (TR_TRACKNO, RDB$PRIMARY144))


I know that the use of compound primary keys is not ideal and that there are several ways to optimise the above statement to work quickly under these conditions. I only post here because a) I'm not entirely certain that this is a bug as such and b) I'm concerned that there may have been a change to the optimiser (which I cannot find in the release notes) that may have an impact on the speed of queries executed c) Maybe I'm being foolish.


Does anybody have an idea?

Thanks,
David