Re: Drop in performance for each INSERT/DELETE combo

----- Original Message -----
From: "Turbo Fredriksson" <turbo(at)bayour(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, March 06, 2002 10:19 AM
Subject: [HACKERS] Drop in performance for each INSERT/DELETE combo

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

What version of PG are you running ?

On PG 7.2 vacuum itself does not incur very big performance hit. And you
don't need to run VACUUM ANALYZE that often, just plain VACUUM will do
nicely.

You can also restrict VACUUMING to your table only by doing VACUUM TABLENAME

If the total size of your table is small I'd recommend running VACUUM
TABLENAME
even more often, up to every few seconds.

> Investigation have shown that it's the actual DELETE that's slow,

Do you have any foreign keys on that table ?

Or even an ON DELETE trigger.

> any idea how to find WHERE (and hopefully WHY :) this is so?

Nope :)

-------------
Hannu