From: | "Giulio Cesare Solaroli" <giulio(dot)cesare(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Cc: | "Greg Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com> |
Subject: | Re: Newbie question about degraded performance on delete statement. (SOLVED) |
Date: | 2007-10-03 07:00:52 |
Message-ID: | ff737ac30710030000t719e03e6j1b118f49339d67ee@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)cesare(at)gmail(dot)com> wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Henrik | 2007-10-03 08:03:53 | Query taking too long. Problem reading explain output. |
Previous Message | Giulio Cesare Solaroli | 2007-10-03 06:56:57 | Re: Newbie question about degraded performance on delete statement. |