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/
