Hi again, Timo!

> Actually adding an index for PROPERTYDEF or modifying IX_DV_PV_PV to
> include PROPERTYDEF does not help. Each value (DATA) for a given
> property (PROPERTYDEF) is stored only once in the data tables (e.g.
> PV_TIMESTAMP, PV_LOOKUP), and if multiple object versions refer to
> this value, they use the same PROPERTYVALUE key in DV_PV. Therefore,
> adding PROPERTYDEF to IX_DV_PV_PV does not make that index more
> selective.

I see, and given PROPERTYDEF generally is less selective than DATA,
there's no point in giving the optimizer the choice of having C2 or C4
as the first tables of the query (before C3 and C5) and hence that
suggestion was of no use.

> SELECT C2.DV_OBJECTTYPE, C2.DV_DOCUMENTPART, C2.DV_VERSIONPART
> FROM DV_PV C2
> INNER JOIN PV_TIMESTAMP C3 ON C3.PROPERTYVALUE =C2.PROPERTYVALUE
> INNER JOIN DV_PV C4 ON C4.DV_OBJECTTYPE = C2.DV_OBJECTTYPE+0
> AND C4.DV_DOCUMENTPART = C2.DV_DOCUMENTPART
> AND C4.DV_VERSIONPART = C2.DV_VERSIONPART
> INNER JOIN PV_LOOKUP C5 ON C5.PROPERTYVALUE = C4.PROPERTYVALUE
> WHERE
> C2.PROPERTYDEF = 21
> AND C3.PROPERTYDEF = 21
> AND C3.DATA >= '24.12.2007'
> AND C4.PROPERTYDEF = 23
> AND C5.PROPERTYDEF = 23
> AND C5.DATA = 101
>
> However, the trick you suggested advices Firebird to start from C2
> using the following plan that results in a good performance:
>
> PLAN JOIN (C3 INDEX (IX_PV_TIMESTAMP_PD_DATA), C2 INDEX
> (IX_DV_PV_PV), C4 INDEX (PK_DV_PV), C5 INDEX (PK_PV_LOOKUP))
>
> Is your trick a reliable way to advice the optimizer?

The trick basically says to the optimizer: "You're not allowed to use
the primary key for C2 when joining to C4, but feel free to use it for
C4 if you want." And in this sense it is reliable. Though excepting
using the primary key for C2, the optimizer is free to choose any plan
it can use, which may result in a better or worse plan than the
original. In this particular case, the resulting 'good' plan is probably
the only remaining sensible choice to make.

I use tricks as this one whenever I'm not satisfied with the optimizers'
suggestion, though my situation is quite different from yours (still on
Firebird 1.5 with most tables containing no more than about 1 million rows).

Set