Re: Rapidly decaying performance repopulating a large table

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.