> My question is should I create a Composite index to mimic the index
> created for the old PK? There is a note with this table that the CPK
> index was changed from Itemref, Regionref to RegionRef, ItemRef to
> improve performance (I assume in some specific area).
>
> I suppose the safest thing to do is just to mimic the old CPK index but
> my reading and gut makes this feel wrong. Almost all queries are Where
> ItemRef = x and regionref = y, is FB 2 smart enough to make use of the
> individual indexes.
>
> Also Should I drop the RegionRef index. It probably is more of an
> overhead then a benefit ?
>
> Any comments would be appreciated :)

PK, FK and unique constraints create an ascending index as an
implementation artifact. Looking at your table.

* You have a new surrogate PK that will be indexed. Selectivity is
excellent.
* You have a declared FK on ItemRef. This will be indexed and the
selectivity while not great is probably not too terrible either.
* You have a declared FK on RegionRef. This will be indexed and the
selectivity will be terrible, but Firebird 2 handles this much better
than 1.x ever did.
* You should declare a unique constraint on your old PK (RegionRef,
ItemRef) unless you are allowed to duplicate records. This will be
indexed and selectivity is excellent.

Any version of Firebird will be smart enough to use the index behind
any of these constraints. Even without the unique constraint, Firebird
could in memory combine both FK indices and take advantage of this.
But your decision to include or exclude this declared unique
constraint should be based on the rules of your data alone.

Adam