pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNewbie question about degraded performance on delete statement.
5+ messages / 3 participants
[nested] [flat]
* Newbie question about degraded performance on delete statement.
@ 2007-10-02 21:55 Giulio Cesare Solaroli <giulio.cesare@gmail.com>
2007-10-02 22:02 ` Re: Newbie question about degraded performance on delete statement. Dan Langille <dan@langille.org>
2007-10-02 22:39 ` Re: Newbie question about degraded performance on delete statement. Greg Williamson <Gregory.Williamson@digitalglobe.com>
0 siblings, 2 replies; 5+ messages in thread
From: Giulio Cesare Solaroli @ 2007-10-02 21:55 UTC (permalink / raw)
To: pgsql-performance
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.
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.
Given this evidence, what are the best practices to fix/avoid this
kind of problems?
I am using PostgreSQL 8.1.4 both on Linux (on a Parallels virtual
machine with a Linux OS) and on Solaris, on a hosted zone; the Solaris
version is running the live DB, while the Linux instance is on my
development machine using a snapshot of the live data.
Thanks for your attention.
Best regards,
Giulio Cesare Solaroli
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Newbie question about degraded performance on delete statement.
2007-10-02 21:55 Newbie question about degraded performance on delete statement. Giulio Cesare Solaroli <giulio.cesare@gmail.com>
@ 2007-10-02 22:02 ` Dan Langille <dan@langille.org>
1 sibling, 0 replies; 5+ messages in thread
From: Dan Langille @ 2007-10-02 22:02 UTC (permalink / raw)
To: Giulio Cesare Solaroli <giulio.cesare@gmail.com>; +Cc: pgsql-performance
On 2 Oct 2007 at 23:55, Giulio Cesare Solaroli wrote:
> 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.
>
> 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.
There may be more to that original table. What about triggers?
rules? Perhaps there other things going on in the background.
--
Dan Langille - http://www.langille.org/
Available for hire: http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Newbie question about degraded performance on delete statement.
2007-10-02 21:55 Newbie question about degraded performance on delete statement. Giulio Cesare Solaroli <giulio.cesare@gmail.com>
@ 2007-10-02 22:39 ` Greg Williamson <Gregory.Williamson@digitalglobe.com>
2007-10-03 06:56 ` Re: Newbie question about degraded performance on delete statement. Giulio Cesare Solaroli <giulio.cesare@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Greg Williamson @ 2007-10-02 22:39 UTC (permalink / raw)
To: Giulio Cesare Solaroli <giulio.cesare@gmail.com>; +Cc: pgsql-performance
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.)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Newbie question about degraded performance on delete statement.
2007-10-02 21:55 Newbie question about degraded performance on delete statement. Giulio Cesare Solaroli <giulio.cesare@gmail.com>
2007-10-02 22:39 ` Re: Newbie question about degraded performance on delete statement. Greg Williamson <Gregory.Williamson@digitalglobe.com>
@ 2007-10-03 06:56 ` Giulio Cesare Solaroli <giulio.cesare@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Giulio Cesare Solaroli @ 2007-10-03 06:56 UTC (permalink / raw)
To: Greg Williamson <Gregory.Williamson@digitalglobe.com>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Newbie question about degraded performance on delete statement. (SOLVED)
@ 2007-10-03 07:00 Giulio Cesare Solaroli <giulio.cesare@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Giulio Cesare Solaroli @ 2007-10-03 07:00 UTC (permalink / raw)
To: pgsql-performance; +Cc: Greg Williamson <Gregory.Williamson@digitalglobe.com>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-03 07:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-02 21:55 Newbie question about degraded performance on delete statement. Giulio Cesare Solaroli <giulio.cesare@gmail.com>
2007-10-02 22:02 ` Dan Langille <dan@langille.org>
2007-10-02 22:39 ` Greg Williamson <Gregory.Williamson@digitalglobe.com>
2007-10-03 06:56 ` Giulio Cesare Solaroli <giulio.cesare@gmail.com>
2007-10-03 07:00 Re: Newbie question about degraded performance on delete statement. (SOLVED) Giulio Cesare Solaroli <giulio.cesare@gmail.com>
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