Re: Newbie question about degraded performance on delete statement. - Mailing list pgsql-performance

Hello Gregory,

On 10/3/07, Greg Williamson <Gregory.Williamson@digitalglobe.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