From: Chris <dmagick@gmail.com>
To: Christian Bourque <christian.bourque@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
Date: Tue, 08 Apr 2008 13:32:56 +1000
Message-ID: <47FAE768.80200@gmail.com> (raw)
In-Reply-To: <47FAE418.3000406@postnewspapers.com.au>
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
<47FAE418.3000406@postnewspapers.com.au>
Craig Ringer wrote:
> Christian Bourque wrote:
>> Hi,
>>
>> I have a performance problem with a script that does massive bulk
>> insert in 6 tables. When the script starts the performance is really
>> good but will degrade minute after minute and take almost a day to
>> finish!
>>
> Would I be correct in guessing that there are foreign key relationships
> between those tables, and that there are significant numbers of indexes
> in use?
>
> The foreign key checking costs will go up as the tables grow, and AFAIK
> the indexes get a bit more expensive to maintain too.
>
> If possible you should probably drop your foreign key relationships and
> drop your indexes, insert your data, then re-create the indexes and
> foreign keys. The foreign keys will be rechecked when you recreate them,
> and it's *vastly* faster to do it that way. Similarly, building an index
> from scratch is quite a bit faster than progressively adding to it. Of
> course, dropping the indices is only useful if you aren't querying the
> tables as you build them.
If you are, add "analyze" commands through the import, eg every 10,000
rows. Then your checks should be a bit faster.
The other suggestion would be to do block commits:
begin;
do stuff for 5000 rows;
commit;
repeat until finished.
--
Postgresql & php tutorials
http://www.designmagick.com/
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: dmagick@gmail.com, christian.bourque@gmail.com
Subject: Re: bulk insert performance problem
In-Reply-To: <47FAE768.80200@gmail.com>
* 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