Helen,

> If you like, you can reconstruct it.

<SNIP>

thanks for taking the time to reconstruct the example. I have done just
a couple of tests (time is always a critical resource) and they seem to
show that one index is always used (sometimes it's the built-in index,
sometimes the redundant user-defined one). I have looked at the plans
and not timed the performance, though. I will do more thourough tests
whan I have more time.
BTW, any feedback from the original poster about what his problem turned
out to be in the end?

> Since the optimiser hasn't been changed significantly since 5.1, I would
> not anticipate that your results will be any less interesting with IB 6 or
> Firebird...for maximum fun, use composite varchar keys, Paradox-style, with
> foreign key columns overlapping PK columns.

Let's not confuse matters more and stay with integers PKs for now. :-)
Will get back to the list (or perhaps IB-Support would be better?) as
soon as I have more test results.

> btw, your comment "IB 4.2 tends to use them both" is a bit contradictory to
> "Never been able to reproduce it".

My memory failed. I should have said that IB4 tends to show the same
index multiple times in the resulting plan (which perhaps means it is
using the same index multiple times) in complex multiple joins,
something that IB5.6+ does not do anymore. This has nothing to do with
our discussion, I just tossed in a deteriorated piece of recollection.
Sorry for that.

> To have the optimiser choose two
> identical indexes would be rather defeating, wouldn't it?

Perhaps as much as it would if it picked up neither of them, yes.

> (I've never seen
> that, though, but it's a long time since I used 4.x).

Unfortunately I think I have several thousands around still :-(
Ciao
--
____
_/\/ando