pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Craig Ringer <craig@postnewspapers.com.au>
To: Christian Bourque <christian.bourque@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: bulk insert performance problem
Date: Tue, 08 Apr 2008 11:18:48 +0800
Message-ID: <47FAE418.3000406@postnewspapers.com.au> (raw)
In-Reply-To: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>
References: <a6ee49d30804072001mf776603se42c475548cde018@mail.gmail.com>

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.

Also, if you're loading data using stored procedures you should avoid 
the use of exception blocks. I had some major problems with my bulk data 
conversion code due to overuse of exception blocks creating large 
numbers of subtransactions behind the scenes and slowing everything to a 
crawl.

--
Craig Ringer

view thread (8+ messages)  latest in thread

Message-ID: <47FAE418.3000406@postnewspapers.com.au>
Permalink:  ../47FAE418.3000406@postnewspapers.com.au/
Also on:    postgresql.org/message-id/47FAE418.3000406@postnewspapers.com.au

copy link · copy postgr.es

reply

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: craig@postnewspapers.com.au, christian.bourque@gmail.com
  Subject: Re: bulk insert performance problem
  In-Reply-To: <47FAE418.3000406@postnewspapers.com.au>

* 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