Re: Slow Inserts on 1 table?

From: Dan Armbrust <daniel(dot)armbrust(dot)list(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Slow Inserts on 1 table?
Date: 2005-08-02 15:41:01
Message-ID: 42EF940D.9040203@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Alvaro Herrera wrote:
<blockquote cite="mid20050802151616(dot)GB30066(at)alvh(dot)no-ip(dot)org" type="cite">
<pre wrap="">On Tue, Aug 02, 2005 at 10:01:50AM -0500, Dan Armbrust wrote:

</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap=""><!---->
[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.

</pre>
</blockquote>
<tt>My apologies, I don't intend to be rude.&nbsp; <br>
<br>
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.<br>
<br>
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.<br>
<br>
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.&nbsp; I wouldn't have even guessed that the query
planner was involved in this portion.&nbsp; But, these are all (apparently
wrong) guesses - I don't know the internals.&nbsp; The performance probably
is better on small tables to not use these indexes.&nbsp; 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.<br>
<br>
<br>
Dan<br>
</tt><br>
<pre class="moz-signature" cols="72">--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
<a class="moz-txt-link-freetext" href="http://informatics.mayo.edu/">http://informatics.mayo.edu/</a>
</pre>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 2.4 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message silasju@gmail.com 2005-08-02 15:43:41 Problems to install pg 8.0.3
Previous Message Dan Armbrust 2005-08-02 15:31:29 Re: Slow Inserts on 1 table?