Skip site navigation (1) Skip section navigation (2)

Re: Rapidly decaying performance repopulating a large table

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Rapidly decaying performance repopulating a large table
Date: 2008-04-22 20:38:29
Message-ID: dcc563d10804221338p76ccaf1bj19e6dcbb7c1009ea@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, Apr 22, 2008 at 2:31 PM, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com> wrote:
> I have a fairly simple table (a dozen real/integer columns, a few
>  indexes, one foreign key reference) with ~120m rows. Periodically the
>  table is truncated or dropped and recreated and the data is
>  regenerated (slightly different data, of course, or the exercise would
>  be rather pointless). The regeneration occurs in batches of ~4000 data
>  points at a time, which are inserted into the table via COPY, and are
>  coming from several simultaneous processes.
>
>  The first several million data points are quite quick (the copy
>  executes in well under a quarter second). By the time the table
>  reaches 10-15m rows, however, each individual COPY is taking >20
>  seconds to execute. Is there anything I can do to improve this
>  performance? I can't drop/recreate the indices because some of the

The best bet is to issue an "analyze table" (with your table name in
there, of course) and see if that helps.  Quite often the real issue
is that pgsql is using a method to insert rows when you have 10million
of them that made perfect sense when you had 100 rows, but no longer
is the best way.

In response to

Responses

pgsql-general by date

Next:From: Jonathan Bond-CaronDate: 2008-04-22 20:44:20
Subject: Re: Schema migration tools?
Previous:From: David WilsonDate: 2008-04-22 20:31:42
Subject: Rapidly decaying performance repopulating a large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group