pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert slow down on empty database
2+ messages / 2 participants
[nested] [flat]
* Insert slow down on empty database
@ 2005-06-03 23:13 Morgan <mkita@verseon.com>
2005-06-04 01:20 ` Re: Insert slow down on empty database Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 2+ messages in thread
From: Morgan @ 2005-06-03 23:13 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am having a problem with inserting a large amount of data with my libpqxx
program into an initially empty database. It appears to be the EXACT same
problem discussed here:
http://archives.postgresql.org/pgsql-bugs/2005-03/msg00183.php
In fact my situation is nearly identical, with roughly 5 major tables, with
foreign keys between each other. All the tables are being loaded into
similtaneously with about 2-3 million rows each. It seems that the problem
is caused by the fact that I am using prepared statments, that cause the
query planner to choose sequential scans for the foreign key checks due to
the table being initially empty. As with the post above, if I dump my
connection after about 4000 inserts, and restablish it the inserts speed up
by a couple of orders of magnitude and remain realtively constant through
the whole insertion.
At first I was using straight insert statments, and although they were a bit
slower than the prepared statments(after the restablished connection) they
never ran into this problem with the database being initially empty. I only
changed to the prepared statements because it was suggested in the
documentation for advice on bulk data loads =).
I can work around this problem, and I am sure somebody is working on fixing
this, but I thought it might be good to reaffirm the problem.
Thanks,
Morgan Kita
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Insert slow down on empty database
2005-06-03 23:13 Insert slow down on empty database Morgan <mkita@verseon.com>
@ 2005-06-04 01:20 ` Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 2+ messages in thread
From: Christopher Browne @ 2005-06-04 01:20 UTC (permalink / raw)
To: pgsql-performance
In an attempt to throw the authorities off his trail, "Morgan" <mkita@verseon.com> transmitted:
> At first I was using straight insert statments, and although they
> were a bit slower than the prepared statments(after the restablished
> connection) they never ran into this problem with the database being
> initially empty. I only changed to the prepared statements because
> it was suggested in the documentation for advice on bulk data loads
> =).
I remember encountering this with Oracle, and the answer being "do
some loading 'til it slows down, then update statistics and restart."
I don't know that there's an obvious alternative outside of perhaps
some variation on pg_autovacuum...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://linuxdatabases.info/info/spreadsheets.html
So long and thanks for all the fish.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-04 01:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-03 23:13 Insert slow down on empty database Morgan <mkita@verseon.com>
2005-06-04 01:20 ` Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox