The following bug has been logged online:

Bug reference: 1552
Logged by: Brian O'Reilly
Email address: fade (AT) deepsky (DOT) 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:

"Fast" machine, Debian, PSQL 7.4:

----------------------------------------------------------------------------
----------------------------------------------------
Index Scan using requirements_pkey on requirements (cost=0.00..4.82 rows=2
width=0) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (reqid = 10::bigint)
Total runtime: 0.134 ms
(3 rows)

and the query plan on the 'slow' machine:


QUERY PLAN
----------------------------------------------------------------------------
--------------------------
Seq Scan on requirements (cost=0.00..0.00 rows=1 width=0) (actual
time=0.002..0.002 rows=0 loops=1)
Filter: (reqid = 10::bigint)
Total runtime: 0.040 ms
(3 rows)

The script I am using to show this behaviour follows:

CREATE TABLE packages
(name text PRIMARY KEY);
CREATE TABLE binary_packages
(name text REFERENCES packages,
version text,
PRIMARY KEY(name, version));
CREATE TABLE requirements
(reqid bigint PRIMARY KEY,
name text,
version text,
FOREIGN KEY (name, version) REFERENCES
binary_packages);
CREATE TABLE constraints
(constid bigint PRIMARY KEY,
reqid bigint REFERENCES requirements,
type text,
name text REFERENCES packages,
version text DEFAULT '',
relation character(2));

explain analyze select 1 from only requirements where reqid='10';

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. Please let me know if I can
provide any more information.

Brian O'Reilly
System Architect.,
DeepSky Media Resources

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Brian,

They PGSQL-PERFORMANCE list is really the appropriate place for performance
issues like yours. Subscribe?

http://www.postgresql.org/community/lists

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Brian,

I don't think your experience on this one query is descriptive of PostgreSQL
in general. What I'm saying is that you most likely have a tuning problem,
not a bug.

If possibilities for improving the optimizer come out of your discussion,
well, about 1/2 of the primary postgresql programmers read the performance
list.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Josh Berkus <josh (AT) agliodbs (DOT) com> writes:
It might be a bug (or at least an unhelpful behavior) but the given case
didn't prove a thing. I'm still looking for an explanation of a common
case in which we'll cache a plan chosen too soon. I don't think
ordinary data loading situations will cause that problem. Choosing a
plan for an FK reference check while the referenced table is still empty
means that you're loading the tables in the wrong order, and your load
is going to fail anyhow.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Josh,

Brian and I are trying to put upwards of 80-90,000 rows into a table.
When we run on PostgreSQL 7.4, this takes about five minutes. On
comparable hardware, running PostgreSQL 8.0.1, it's taking almost two
hours. The query from the bug report shows that the planner is using
sequential searches, not index searches, to satisfy the foreign key
constraint.

Our scenario (inserting data into a new table) seems to be something
that a lot of users could run across. The performance drop we're seeing
shows up as a regression from earlier behaviour in PostgreSQL. That's
why, after discussing our issue with AndrewSN on #postgresql, Brian
filed a bug here.

We'll follow up with the PERFORMANCE list to find a workaround, but I
think this is an issue which should be addressed as a bug.

Thanks for your help,

Keith Browne
tuxedo (AT) deepsky (DOT) com





---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 2005-03-18 at 23:21 +0000, Brian O'Reilly wrote:
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.

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.

Yes, all of the above, plus more.

Best Regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Simon Riggs wrote:

Simon,

Brian and I are working together on this problem.

We're starting with an empty database, creating four tables, and
populating those tables with a total of 180,000-200,000 rows. Each
table has a primary key, and several of the tables reference foreign
keys in other tables. We've written a Python script, using psycopg,
which executes all the queries to create the tables and insert the rows.
The database is running on the same machine where the script runs.

I've seen similar performance when issuing a COMMIT after each
insertion, and also after batching insertions in blocks of 250 per
COMMIT, so batching the commits is not helping much. I've looked at the
possibility of using COPY, but in our production environment it will be
prohibitive to build a flat file with all this data. I'd rather
generate it on the fly, as we've been able to do with PostgreSQL 7.4.

When running with PostgreSQL 7.4 on a dual-CPU Athlon MP2400+ machine
with a gigabyte of RAM, running Debian Linux version 2.6.8.1, we were
able to insert all this data in 5-7 minutes. It's taken a while to
install Postgres 8.0.1 on the same machine, but now I have, and it's
taking 40-45 minutes to run the same insert script. This is similar to
the performance we saw on another machine, a fast single-CPU AMD64 box
running Gentoo.

I don't think it's a hardware issue. I dug around a bit, and found
suggestions that this sort of problem could be worked around by breaking
the database connection and restarting it after the tables had been
partially filled. I modified our script to break and re-establish the
database connection when each table first has 4,000 records inserted,
and the performance is greatly improved; it now takes only about 3.5
minutes to insert 180,000+ rows.

I've since modified this script to build and populate a fifth table with
over 1.3 million rows. The fifth table has no primary key, but lists a
foreign key into one of the first four tables. With the above
modification (break and re-build the DB connection after 4,000 rows have
been inserted), the whole database can be populated in about 15 minutes.
I wouldn't have dared try to build a one-million-plus-row table until
I found this speed-up.

Just to see if it would help, I tried modifying the script to run an
ANALYZE against each table after 4,000 insertions, instead of breaking
and re-establishing the DB connection. I still saw ~45-minute times to
insert 180,000 rows. I then tried running ANALYZE against each table
after *each* 4,000 rows inserted, and again, it took about 45 minutes to
run the insert.

Each table is empty when I first run the program. I am dropping and
re-creating the database for each test run.

I'd be happy to run further tests or provide more details, if they'll
help. We now have a workaround which is allowing us to proceed with our
project, but I'd like to know if there's another way to do this. While
I understand that large or complex databases require careful tuning, I
was surprised to see a six- or seven-fold increase in run times between
PostgreSQL 7.4 and 8.0.1 on the same hardware, on an operation which
seems fairly straightforward: populating an empty table.

One other thing which puzzled me: as a test, I tried modifying our
script to spit out raw SQL statements instead of connecting to the
database and performing the inserts itself. Normally, our script
populates two tables in one pass, and then populates the third and
fourth tables in a second pass. I massaged the SQL by hand to group the
inserts together by table, so that the first table would be entirely
populated, then the second, etc. When I ran this SQL script by piping
it straight into psql, it finished in about four minutes. This is
comparable to the time it takes to run my modified script which breaks
and re-establishes the connection to the database.

It would appear that psql is doing something right here which we have
had to go out of our way to get with psycopg.

Keith Browne
tuxedo (AT) deepsky (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On 2005-03-23, Keith Browne <tuxedo (AT) deepsky (DOT) com> wrote:
Changing the order so that the referenced table is fully populated, or at
least populated with more than a handful of pages of rows, before doing
_any_ insert on a referencing table in the same session will avoid the
misplan of the FK trigger queries, because when the first insert happens
on a referencing table, there will be no reason for the planner to prefer
a sequential scan. So this result is not surprising at all.

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Andrew - Supernews <andrew+nonews (AT) supernews (DOT) com> writes:
I'm still looking for an example that demonstrates why this is a common
problem that we need to worry about. ISTM that if an FK reference is
hit when there are still zero entries in the referenced table, that
insertion will fail anyway, and so people wouldn't try to load data in
such an order.

In the long term it would be good to replan the FK plans when the
referenced tables have grown so much that the plan ought to change.
Once we have the plan invalidation machinery that Neil is working on,
it might be fairly practical to do that; but no such thing is going
to appear in existing release branches of course.

We could band-aid this in 8.0 as previously suggested (have the planner
assume > 0 pages when it sees actually 0 pages) but without seeing a
concrete example I can't tell if that will fix the complaint or not.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane wrote:

Tom,

We're filling pairs of tables with rows having nearly a one-to-one
mapping; very rarely, the second table will have multiple rows
corresponding to one row in the first table. When we insert the first
row in the second table, therefore, we've just put the corresponding row
into the first table, so the foreign key constraint is satisfied.

I can't say how common this sort of thing will be. It appears to me
that BUG #1541 is similar to what we're seeing, and a search of the
mailing lists also turns up this message:

http://archives.postgresql.org/pgsql...1/msg00416.php

which also describes symptoms similar to what I'm seeing.

It sounds like this could work for us, if it would disable sequential
searches into a table which grows from 0 to >60,000 rows in one session.
Is breaking and re-establishing the database session the best
workaround, or is there a better way to provide a hint to the planner?

Regards,

Keith Browne
tuxedo (AT) deepsky (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)