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

Hello,

thanks to the added info available running the explain plan through
pgsl (instead of using pgAdmin) I was able to realize that an
(implicitly created) trigger was the culprit of the slowdown I was
suffering.

Adding an index on the foreign key the trigger was monitoring solved the issue.

THANKS EVERYBODY for your kind attention.

Best regards,

Giulio Cesare



On 10/3/07, Giulio Cesare Solaroli <giulio.cesare@gmail.com> wrote:
> 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
>