On Fri, 2005-03-18 at 23:21 +0000, Brian O'Reilly wrote:
> The following bug has been logged online:
>
> Bug reference: 1552
> Logged by: Brian O'Reilly
> Email address: fade@deepsky.com
> PostgreSQL version: 8.0.1
> Operating system: Linux 2.6.11
> Description: massive performance hit between 7.4 and 8.0.1
> Details:
>
> When doing a lot of inserts to an empty table with a foreign key to another
> table, there is an incredible performance degredation issue on 8.0.1. I have
> a program that is inserting rows in an iterative loop, and in this form it
> inserts about 110,000 rows. On postgresql 7.4 on a debian machine it takes a
> shade over 2 minutes to complete. On an amd64 box running gentoo, it takes
> over an hour and fourty minutes to complete. The query plan on the debian
> host that completes quickly follows:
>
This may be a bug, thanks for filing it.
However, we can't tell at the moment from what you've said.
The EXPLAINs you've enclosed are for SELECTs, yet your bug report
describes INSERTs as being the things that are slow.
[You may find better performance from using COPY]
Also, your tests have compared two systems, so it might be that the
hardware or configuration of one system is different from the other.
If you could repeat the test on one single system, then this would
assist in the diagnosis of this bug report. Also, if you could describe
the workload that is giving you a problem more exactly, that would help.
Specifically, can you confirm that you have run ANALYZE on the tables,
and also give us some idea of numbers of rows in each table at the time
you first run your programs.
> the query optimiser seems to be setting a default strategy of doing
> sequential scans on an empty table, which is a fast strategy when the table
> is empty and not particularly full, but obviously on a large table the
> performance is O(N^2).
> This is clearly a bug.
There is clearly a problem, but it is not yet clearly a bug. If it is a
bug, we're interested in solving it as much as you.
> Please let me know if I can
> provide any more information.
Yes, all of the above, plus more.
Best Regards, Simon Riggs