Re: Newbie question about degraded performance on delete statement.

Hello Gregory,

On 10/3/07, Greg Williamson <Gregory(dot)Williamson(at)digitalglobe(dot)com> wrote:
> 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 run Analyze (always through the pgAdmin interface), and it did
not provide any benefits.

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

clipperz_connection=> \d clipperz.rcrvrs
Table "clipperz.rcrvrs"
Column | Type | Modifiers
----------------------+--------------------------+-----------
id_rcrvrs | integer | not null
id_rcr | integer | not null
id_prvrcrvrs | integer |
reference | character varying(1000) | not null
header | text | not null
data | text | not null
version | character varying(100) | not null
creation_date | timestamp with time zone | not null
access_date | timestamp with time zone | not null
update_date | timestamp with time zone | not null
previous_version_key | text | not null
Indexes:
"rcrvrs_pkey" PRIMARY KEY, btree (id_rcrvrs)
"unique_rcrvrs_referecnce" UNIQUE, btree (id_rcr, reference)
Foreign-key constraints:
"rcrvrs_id_prvrcrvrs_fkey" FOREIGN KEY (id_prvrcrvrs) REFERENCES
rcrvrs(id_rcrvrs)
"rcrvrs_id_rcr_fkey" FOREIGN KEY (id_rcr) REFERENCES rcr(id_rcr)
DEFERRABLE INITIALLY DEFERRED

Is this a complete listing of all the DDL involved in defining the
table, or is there something possibly missing here?

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

I have already tried the explain plan, but only using the pgAdmin
interface; running it from psql shows some more data that looks very
promising:

--------------------------------------------------------------------------------------------------------------------
Index Scan using rcrvrs_pkey on rcrvrs (cost=0.00..3.68 rows=1
width=6) (actual time=2.643..2.643 rows=1 loops=1)
Index Cond: (id_rcrvrs = 15434)
Trigger for constraint rcrvrs_id_prvrcrvrs_fkey: time=875.992 calls=1
Total runtime: 878.641 ms
(4 rows)

The trigger stuff was not shown on the pgAdmin interface.

I will try to add an index on the foreign key field (id_prvrcrvrs) to
see if this improves performances of the incriminated query.

Thanks for the kind attention.

Best regards,

Giulio Cesare