Re: Thousands INSERT going slow ...

=?iso-8859-15?q?Herv=E9=20Piedvache?= <herve(at)elma(dot)fr> writes:
> Table N2 have a reference on the Table N1 with the primary key ...
> It's not a transaction ... I have only a primary key on each 3 tables ...
> The tables where empty at the beginning and all the database vacuum full
> analysed before the test.
> Same result on PostgreSQL 7.2.3 and 7.3.2 ...

> So I get about 1000 inserts done in 10 seconds at the beginning of the file,
> then after one hour I get 25 seconds for 1000 inserts ... and it's going
> slower and slower ...

You probably should *not* have vacuum analyzed while the tables were
empty. The planner generated plans for the foreign-key checks based on
the assumption that the tables are tiny --- so it's probably using
seqscans to make the checks. The actual inserts won't slow down
materially as the tables fill, but seqscan foreign-key checks will.

You could vacuum now to update the stats, but that won't affect
the foreign-key plans that the inserting process has already cached.
You'll have to stop the inserting process and start a new one in any
case.

Another possibility is to start again with freshly created tables and no
initial vacuum. If the planner has no stats it should default to
indexscans.

regards, tom lane