Re: Newbie question about degraded performance on delete statement.

Giulio Cesare Solaroli wrote:
> Hello everybody,
>
> I have just joined the list, as I am experiencing a degradation on
> performances on my PostgreSQL instance, and I was looking for some
> insights on how to fix/avoid it.
>
> What I have observed are impossibly high time on delete statements on
> some tables.
>
> The delete statement is very simple:
> delete from table where pk = ?
>
> The explain query report a single index scan on the primary key index,
> as expected.
>
> I have run vacuum using the pgAdmin tool, but to no avail.
>
> I have also dropped and recreated the indexes, again without any benefit.
>
Make sure you run ANALYZE on the table in question after changes to make
sure the stats are up to date.
> I have later created a copy of the table using the "create table
> table_copy as select * from table" syntax.
>
> Matching the configuration of the original table also on the copy
> (indexes and constraints), I was able to delete the raws from the new
> table with regular performances, from 20 to 100 times faster than
> deleting from the original table.
>
>
As another poster indicated, this sounds like foreign constraints where
the postmaster process has to make sure there are no child references in
dependent tables; if you are lacking proper indexing on those tables a
sequential scan would be involved.

Posting the DDL for the table in question and anything that might refer
to it with an FK relationship would help the list help you.

Try running the query with EXPLAIN ANALYZE ... to see what the planner
says. Put this in a transaction and roll it back if you want to leave
the data unchanged, e.g.
BEGIN;
EXPLAIN ANALYZE DELETE FROM foo WHERE pk = 1234; -- or whatever values
you'd be using
ROLLBACK;

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information and must be protected in
accordance with those provisions. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.

(My corporate masters made me say this.)