Re: Slow Inserts on 1 table?

[Dan Armbrust <daniel.armbrust.list@xxxxxxxxx>]

Alvaro Herrera wrote:

On Tue, Aug 02, 2005 at 10:01:50AM -0500, Dan Armbrust wrote:

  

I shouldn't have to manually run Analyze to make the DB be capable of 
handling inserts involving tables with foreign keys correctly.  My code 
that is doing the inserts is a java application that works across 
multiple DBS - MySQL, PostgreSQL, DB2, MS Access, Sybase, etc. 
    

[etc, rant removed]

You don't _have_ to be rude.  This is a known limitation, and people
have suggested the usual workarounds.  This is an open source project --
if you think you can make it better, please by all means post a patch.

HAND.

  

My apologies, I don't intend to be rude. 

But it is rather easy to get into rant mode when the prevailing opinion is that not being able to insert rows into a table with a foreign key without running Analyze after X rows is a misuse of the DB, rather than a bug.

I did not know that this is a known limitation, I have not been able to find any documentation that talks about how foreign keys, indexes, and the query planner relate.

My first assumption was that since foreign key creation implicitly creates the necessary indexes, that these indexes would always be used for foreign key checks.  I wouldn't have even guessed that the query planner was involved in this portion.  But, these are all (apparently wrong) guesses - I don't know the internals.  The performance probably is better on small tables to not use these indexes.  But it seems to me, that if you know that the statistics are out of date (which I would think that you should know, if analyze hasn't been run since the tables were created) that the safer choice would be to use the indexes, rather than not using the indexes.


Dan

-- 
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/