IANAD (I am not a developer) but deleted rows are not removed till
vacuuming occurs. They are just marked so.
Are you deleting specific rows? If you are then you have to keep vacuuming
to keep it going at about 30/sec. This can be more viable with 7.2.
Postgresql often has to go through relevant deleted rows in order to find
the valid rows.
If you want to delete everything, truncating might be faster. Unfortunately
truncating can't work in a transaction block.
Link.
At 09:19 AM 06-03-2002 +0100, Turbo Fredriksson wrote:
>We're using a function to insert some information into the database.
>This information is later (within seconds) retrieved from a program,
>that does the actual processing of the information. It is then
>deleted from the database when we're done with it.
>
>
>We see a MAJOR performance loss the longer the time. It starts out
>from around 28 'data chunks' per second (inserts in a couple tables),
>and drops down to below 10/s...
>
>If doing 'VACUUM ANALYZE' every 20 minutes improves the performance,
>with the expected drop when the VACUUM is done, but in general the
>performance is steady...
>
>Investigation have shown that it's the actual DELETE that's slow,
>any idea how to find WHERE (and hopefully WHY :) this is so?
