Subject Re: [firebird-support] Re: foreign keys ans slow insert
Author Helen Borrie
At 02:37 PM 30/09/2004 +0200, you wrote:

>I have found it faster to use as few indices as possible. It speeds up
>query and SP execution time incredibly. Is this anything to do with this
>The reason I ask is because, coming from a dBase/Paradox background, I
>thought (in the beginning) that I needed indices for just about everything.
>With Firebird, I've learned not to put in indices.
>I thought this may have something to do with this problem.

In Firebird, it's not the number of indexes, per se. Firebird is a lot
smarter about putting indexes to multiple use so -- I agree with you --
Paradox schemas that are blindly reconstructed in a Firebird database
generally kill performance because you typically have a lot of redundant
indexes. But some indexes you are simply better off without.

But this particular case is a whole lot of low-selectivity indexes created
for foreign keys (10 or 11 in the table, as I recall). Basically, if
German insists on retaining them, he will just have to live with slow
performance that will get worse as the table gets bigger. In selects, a
custom plan might help; but for inserts those long dup chains are just
going to keep growing.