Tom,

> RDB$FOREIGN122 VACANCY 0.000100613746326417
> RDB$FOREIGN44 CLIENT 2.97956030408386E-5
> IDXSITE_REGIONID SITE 0.0625
> RDB$PRIMARY8 COMPANY 3.07843874907121E-5

The RegionID index has a lot of duplicates.

With v1.x this makes for a lousy index for the optimizer.

You have 3 choices:

1 - try to change your query to be WHERE REGIONID+0 IN (...) -- this
will allow the optimizer to ignore the index. Yes, it will result in a
NATURAL table scan but that might be better. If the change makes things
worse, please forward the new PLAN -- you might need to re-write the
query to SELECT on Sites and then JOIN from there.


2 - drop the current index and create a new multi-part index which has
the Region ID in the first position and another column which you
commonly use in a query with REGION ID. This will create small index
chains.


3 - upgrade to v2.x; it includes a new ODS structure which significantly
improves the performance of indexes with large numbers of duplicates.


Sean