Lists: | pgsql-performance |
---|
From: | "Junaili Lie" <junaili(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | slow deletes on pgsql 7.4 |
Date: | 2006-04-25 21:41:03 |
Message-ID: | 8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Hi all,
we encounter issues when deleting from a table based on id (primary key). On
certain 'id', it took forever to delete and the i/o is 100% busy.
Table scenario has around 1400 entries. It is the parent of 3 other table.
Table "public.scenario"
Column | Type | Modifiers
-----------------+-----------------------+------------------------------------------------
id | bigint | not null default
nextval('scenario_seq'::text)
name | character varying(50) |
description | text |
subscriber_id | bigint |
organization_id | bigint |
schedule_id | bigint |
Indexes:
"scenario_pkey" primary key, btree (id)
"org_ind_scenario_index" btree (organization_id)
"sch_ind_scenario_index" btree (schedule_id)
"sub_ind_scenario_index" btree (subscriber_id)
Check constraints:
"$3" CHECK (schedule_id >= 0)
"$2" CHECK (organization_id >= 0)
"$1" CHECK (subscriber_id >= 0)
Foreign-key constraints:
"0_4774" FOREIGN KEY (schedule_id) REFERENCES schedule(id) ON DELETE
CASCADE
"0_4773" FOREIGN KEY (organization_id) REFERENCES organization(id) ON
DELETE CASCADE
"0_4772" FOREIGN KEY (subscriber_id) REFERENCES subscriber(id) ON DELETE
CASCADE
In all the child tables, the foreign key has the same data type and are
indexed.
When I do "delete from scenario where id='1023', it takes less than 200 ms.
But when i do "delete from scenario where id='1099', it took forever (more
than 10 minutes that i decided to cancel it.
I can't do explain analyze, but here is the explain:
MONSOON=# begin;
BEGIN
MONSOON=# explain delete from scenario where id='1099';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1099::bigint)
(2 rows)
MONSOON=# explain delete from scenario where id='1023';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1023::bigint)
(2 rows)
MONSOON=# explain analyze delete from scenario where id='1023';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6) (actual time=0.028..0.030 rows=1 loops=1)
Index Cond: (id = 1023::bigint)
Total runtime: 0.174 ms
(3 rows)
I have also tried increasing statistics on both parent and child tables to
100, vacuum analyze parent and all child tables. But still the same
slowness.
The o/s is Solaris 10, with fsync = true.
Any ideas what's going on?
Thanks in advance,
J
From: | "Junaili Lie" <junaili(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow deletes on pgsql 7.4 |
Date: | 2006-04-25 21:46:30 |
Message-ID: | 8d04ce990604251446l967aaa5j5909e7f19ddecd6d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
I should also mention that select ... for update is fast:
MONSOON=# begin;explain analyze select * from SCENARIO WHERE id = '1099' FOR
UPDATE;
BEGIN
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.17 rows=1
width=64) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (id = 1099::bigint)
Total runtime: 0.072 ms
(3 rows)
On 4/25/06, Junaili Lie <junaili(at)gmail(dot)com> wrote:
>
> Hi all,
> we encounter issues when deleting from a table based on id (primary key).
> On certain 'id', it took forever to delete and the i/o is 100% busy.
> Table scenario has around 1400 entries. It is the parent of 3 other table.
> Table "public.scenario"
> Column | Type | Modifiers
>
> -----------------+-----------------------+------------------------------------------------
> id | bigint | not null default
> nextval('scenario_seq'::text)
> name | character varying(50) |
> description | text |
> subscriber_id | bigint |
> organization_id | bigint |
> schedule_id | bigint |
> Indexes:
> "scenario_pkey" primary key, btree (id)
> "org_ind_scenario_index" btree (organization_id)
> "sch_ind_scenario_index" btree (schedule_id)
> "sub_ind_scenario_index" btree (subscriber_id)
> Check constraints:
> "$3" CHECK (schedule_id >= 0)
> "$2" CHECK (organization_id >= 0)
> "$1" CHECK (subscriber_id >= 0)
> Foreign-key constraints:
> "0_4774" FOREIGN KEY (schedule_id) REFERENCES schedule(id) ON DELETE
> CASCADE
> "0_4773" FOREIGN KEY (organization_id) REFERENCES organization(id) ON
> DELETE CASCADE
> "0_4772" FOREIGN KEY (subscriber_id) REFERENCES subscriber(id) ON
> DELETE CASCADE
>
> In all the child tables, the foreign key has the same data type and are
> indexed.
> When I do "delete from scenario where id='1023', it takes less than 200
> ms.
> But when i do "delete from scenario where id='1099', it took forever (more
> than 10 minutes that i decided to cancel it.
> I can't do explain analyze, but here is the explain:
> MONSOON=# begin;
> BEGIN
> MONSOON=# explain delete from scenario where id='1099';
> QUERY PLAN
>
> ------------------------------------------------------------------------------
> Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
> width=6)
> Index Cond: (id = 1099::bigint)
> (2 rows)
>
> MONSOON=# explain delete from scenario where id='1023';
> QUERY PLAN
>
> ------------------------------------------------------------------------------
> Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
> width=6)
> Index Cond: (id = 1023::bigint)
> (2 rows)
>
> MONSOON=# explain analyze delete from scenario where id='1023';
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------
> Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
> width=6) (actual time=0.028..0.030 rows=1 loops=1)
> Index Cond: (id = 1023::bigint)
> Total runtime: 0.174 ms
> (3 rows)
>
> I have also tried increasing statistics on both parent and child tables to
> 100, vacuum analyze parent and all child tables. But still the same
> slowness.
> The o/s is Solaris 10, with fsync = true.
> Any ideas what's going on?
> Thanks in advance,
>
> J
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Junaili Lie" <junaili(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow deletes on pgsql 7.4 |
Date: | 2006-04-25 23:09:18 |
Message-ID: | 9147.1146006558@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
"Junaili Lie" <junaili(at)gmail(dot)com> writes:
> we encounter issues when deleting from a table based on id (primary key). O=
> n
> certain 'id', it took forever to delete and the i/o is 100% busy.
Almost always, if delete is slow when selecting the same rows is fast,
it's because you've got a trigger performance problem --- most commonly,
there are foreign keys referencing this table from other tables and you
don't have the referencing columns indexed.
regards, tom lane
From: | "Junaili Lie" <junaili(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow deletes on pgsql 7.4 |
Date: | 2006-04-26 00:14:56 |
Message-ID: | 8d04ce990604251714g1f5d99bbn172ec554a49c8374@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
hi,
Thanks for the answer.
I have double checked that all the foreign key that are referencing "id" on
scenario are indexed.
I have even vacuum analyze scenario table and all the tables that referenced
this table.
Something that is interesting is that: it only happens for a certain values.
ie. delete from scenario where id='1023' is very fast, but delete from
scenario where id='1099' is running forever.
Any ideas?
J
On 4/25/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Junaili Lie" <junaili(at)gmail(dot)com> writes:
> > we encounter issues when deleting from a table based on id (primary
> key). O=
> > n
> > certain 'id', it took forever to delete and the i/o is 100% busy.
>
> Almost always, if delete is slow when selecting the same rows is fast,
> it's because you've got a trigger performance problem --- most commonly,
> there are foreign keys referencing this table from other tables and you
> don't have the referencing columns indexed.
>
> regards, tom lane
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Junaili Lie" <junaili(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow deletes on pgsql 7.4 |
Date: | 2006-04-26 01:04:34 |
Message-ID: | 10985.1146013474@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
"Junaili Lie" <junaili(at)gmail(dot)com> writes:
> ie. delete from scenario where id=3D'1023' is very fast, but delete from
> scenario where id=3D'1099' is running forever.
What does EXPLAIN show for each of those cases?
regards, tom lane
From: | "Junaili Lie" <junaili(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: slow deletes on pgsql 7.4 |
Date: | 2006-04-26 14:27:13 |
Message-ID: | 8d04ce990604260727r65d009d6pef436465701c4203@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
It was on my first email.
Here it is again:
MONSOON=# explain delete from scenario where id='1099';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1099::bigint)
(2 rows)
MONSOON=# explain delete from scenario where id='1023';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1023::bigint)
(2 rows)
Thanks,
J
On 4/25/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Junaili Lie" <junaili(at)gmail(dot)com> writes:
> > ie. delete from scenario where id=3D'1023' is very fast, but delete from
> > scenario where id=3D'1099' is running forever.
>
> What does EXPLAIN show for each of those cases?
>
> regards, tom lane
>