From: Greg Williamson <Gregory.Williamson@digitalglobe.com>
To: Giulio Cesare Solaroli <giulio.cesare@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Newbie question about degraded performance on delete statement.
Date: Tue, 02 Oct 2007 15:39:51 -0700
Message-ID: <4702C8B7.5020701@digitalglobe.com> (raw)
In-Reply-To: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
References: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
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.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Gregory.Williamson@digitalglobe.com, giulio.cesare@gmail.com
Subject: Re: Newbie question about degraded performance on delete statement.
In-Reply-To: <4702C8B7.5020701@digitalglobe.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox