Re: BUG #1541: Unusually long INSERT times after fresh

On 2005-03-19, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> On Fri, 18 Mar 2005, Tom Lane wrote:
>> Hm. One thing we could do is to throw in some default values when we
>> see the table has exactly zero pages --- perhaps ye olde traditional
>> 1000/10, or possibly something else, but anyway not exactly 0/0.

I don't think that would necessarily do the job.

>> The reason I thought we didn't need to do this sort of hack anymore
>> is that pg_dump loads the tables first and then creates the RI
>> constraints. What exactly is the common case where the wrong thing
>> happens?
>
> Probably loading a schema only dump followed by a data load that doesn't
> turn off the constraint (as I believe that's non-default on data-only
> dumps now).

Both the original post in this thread, and the recent case seen on irc
(the bug report for which I guess is in the moderation queue somewhere)
were examples of data imports being done at the application level. i.e.
the application is taking data from some external source and loading its
tables as it would in normal usage. In the most recent example this
amounted to under 200k rows, which was taking about 3 minutes to process
on 7.4.x (clearly a reasonable time) and more than 90 minutes on 8.0.1
(clearly not reasonable).

I know on the irc channel there have been at least a couple of reports of
slowness on restoring data-only dumps, but I haven't been able to pin that
down to the same problem (especially since such a restore should be filling
the referenced table before the referencing table).

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services