From: Morgan <mkita@verseon.com>
To: pgsql-performance@postgresql.org
Subject: Insert slow down on empty database
Date: Fri, 3 Jun 2005 16:13:21 -0700
Message-ID: <d7qo6e$fq$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mkita@verseon.com
Subject: Re: Insert slow down on empty database
In-Reply-To: <d7qo6e$fq$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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