Re: Rapidly decaying performance repopulating a large table

"David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> writes:
> Foreign keys are definitely indexed (actually referencing a set of
> columns that the foreign table is UNIQUE on).

Are you loading any tables that are the targets of foreign key
references from other tables being loaded? If so, I'd bet on
Scott's theory being correct with respect to the plans for checks
of those FK constraints. The COPY itself hasn't got any plan,
and inserting rows into a table should be constant-time in itself,
so it seems to me there are only two possibilities for a progressive
slowdown:

* the cost of updating the indexes, which for standard btree indexes
ought to grow at about log(N) when there are already N entries

* bad plans in either foreign-key triggers or user-defined triggers
attached to the tables.

You failed to mention what PG version this is (tut tut) but if it's
less than 8.3 then ANALYZE alone won't fix bad plans in triggers;
you'd need to analyze and then start a fresh database session.

You said that you don't need to have the FK constraints present,
so I'd strongly suggest trying it without ...

regards, tom lane