Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

Peter Nixon <listuser(at)peternixon(dot)net> writes:
> Now, I had a approx 5million records in this table and I usually get a
> combination of 250-300 SELECTS + 250-300 INSERTS per second on this table
> at this size (or around 500 SELECTS per second)

> Now I decided to remove all the data from the table and reimport due to a
> minor parsing error in my import script (No changes to the table schema
> were made) so I did DELETE FROM StopTelephony; then VACUUM ANALYZE;

> I then reran my import script and found that I was getting approximately 1
> INSERT every 30 secconds!!! although SELECTS were working relatively
> quickly.

> I then tried a VACUUM FULL; a restart of postgres, a server reboot etc etc
> all to no avail. INSERTS stayed stubbonly at 1 every 30+ secconds.

> It wasn't until this morning that I decided to drop and recreate the index
> at which point everything went back to normal.

I think your mistake was to do VACUUM ANALYZE while the table was empty.
That led the planner to generate plans suitable for a very small table
--- seqscans instead of indexscans, for example. A byproduct of
rebuilding the index was to update the planner's idea of how big the
table is, at which point the plans went back to normal. It's hard to
prove anything now, but it would have been a good idea to take note of
EXPLAIN ANALYZE output for the slow queries.

BTW: next time you want to do something like this, consider using
TRUNCATE TABLE instead of DELETE/VACUUM.

regards, tom lane