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

On Wed April 9 2003 19:59, Tom Lane wrote:
> 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

Yes. But that still doesn't explain the speed. I am not joking when I said I
was getting only ONE INSERT every 30 seconds - 1 minute!!!
I tried runnng VACUUM ANALYZE many times. about 10 -15 as I thought somethign
like this might have happened, but even a terrible QUERY plan doesnt explain
20+ SELECTS per seccond but only one INSERT every 30sec on a table with only
a few hundred records!
Note: When the database is working as expected I get the following speed
(across a network)
"9065 records from detail-20020919.bz2 were processed in 24 seconds (377.7
records/sec)"

processed means SELECT to see if the record is in the database, then INSERT if
it is not (It should not be)..

> 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.

OK Thanks

Thanks for the great work guys. Postgres is a brilliant database!

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc