> >
> > Here are some questions about foreign key indexes.
> >
> > Right now indexes are automatically created for every
> > foreign key. Those indexes can not be dropped and
> > require some backhand magic to rename.
>
> And unfortunately, there's no out-of-standard (proprietary
> extension) way
>to give automatic indexes a meaningful or custom name. I can give the
>constraint itself a name, but not to the underlying index. (Same for PKs and
>UKs but this might be another separated topic.)

My thought on that line is to provide syntax for renaming indexes and
if we decide to maintain the internal triggers that validate foreign
key indexes, update the tables those triggers depend on to reflect the
new names.

> > As a result, if
> > the parent table is small and the child table large, the
> > foreign key index has very poor selectivity and can have
> > a terrible effect on performance.
>
> I assume one can translate that in newbie terms as "it's faster
> to do a
>natural scan".

Not necessarily a natural scan. As you know, InterBase will use all
possible indexes to improve a query - omitting only duplicate indexes
and additional indexes after a unique index has been used, uniquely.
As a result, a reasonable indexed path may be chosen and then the
terrible foreign key index dropped into the plan - a true pessimization.

> >
> > 1) The optimizer should be improved so that it doesn't
> > use really terrible indexes, right?
>
> Although optimizers can be backed by strong mathematic models, I
> don't
>think they are easy to build nor easier to make them aware of all important
>facts in way that it doesn't take more time to run the optimizer than the
>query itself. In other words, I'm not sure I must demand more and more
>improvements to the optimizer.
> I thought the optimizer did something with the selectivity. Right
> or wrong?
>If it uses that information, it can take better decisions... but if the
>selectivity is totally outdated... disastrous performance. Also, the only
>ways to update selectivity seems to be SET STATISTICS INDEX and
>backup/restore. Activating/deactivating indexes is another way, but this is
>forbidden in the current design for PK/UK/FK.

Lets leave the optimizer for another thread. I suspect that the optimizer
should be able to recognize one of the truly dreadful indexes from selectivity
and will be interested to see why it doesn't.

> > 2) Does it make sense to maintain indexes that can never
> > be used?
>
> I'm not sure what was your idea here, Ann.

Sorry. What I meant was that we could define foreign key indexes for
limited domains and then rely on the optimizer never to use them in a
query. Automatically deleting indexes isn't my idea of reasonable
database behavior. The real question is, if the index isn't going to
do any good, shouldn't I be able to delete it without giving up the
consistency benefits of foreign keys.

Cheers,

Ann