X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0140C11F6097 for ; Tue, 25 Apr 2006 18:46:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43212-03 for ; Tue, 25 Apr 2006 18:46:32 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.176]) by postgresql.org (Postfix) with ESMTP id 39FC411F6083 for ; Tue, 25 Apr 2006 18:46:30 -0300 (ADT) Received: by pproxy.gmail.com with SMTP id x66so1485515pye for ; Tue, 25 Apr 2006 14:46:31 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=IDLqzv6zdukADO9QpqDSopz236W9+grLbRhFvj00bEmFw1JKaP1cKU9FTcXwNX7fohqgUYUFb5Oh2aQX2S2jMHtPDytOWOarDiZ+m0rvyokpOgytNtu8h+AJ5uNdO4BgqrqEfot2bOF4+Az2UbgxkyM9tAJygYnftg6wW+xji8A= Received: by 10.35.50.9 with SMTP id c9mr49812pyk; Tue, 25 Apr 2006 14:46:31 -0700 (PDT) Received: by 10.35.82.19 with HTTP; Tue, 25 Apr 2006 14:46:30 -0700 (PDT) Message-ID: <8d04ce990604251446l967aaa5j5909e7f19ddecd6d@mail.gmail.com> Date: Tue, 25 Apr 2006 14:46:30 -0700 From: "Junaili Lie" To: pgsql-performance@postgresql.org Subject: Re: slow deletes on pgsql 7.4 In-Reply-To: <8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6949_31381341.1146001590961" References: <8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/580 X-Sequence-Number: 18664 ------=_Part_6949_31381341.1146001590961 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I should also mention that select ... for update is fast: MONSOON=3D# begin;explain analyze select * from SCENARIO WHERE id =3D '1099= ' FOR UPDATE; BEGIN QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------- Index Scan using scenario_pkey on scenario (cost=3D0.00..3.17 rows=3D1 width=3D64) (actual time=3D0.016..0.017 rows=3D1 loops=3D1) Index Cond: (id =3D 1099::bigint) Total runtime: 0.072 ms (3 rows) On 4/25/06, Junaili Lie 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 >=3D 0) > "$2" CHECK (organization_id >=3D 0) > "$1" CHECK (subscriber_id >=3D 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=3D'1023', it takes less than 200 > ms. > But when i do "delete from scenario where id=3D'1099', it took forever (m= ore > than 10 minutes that i decided to cancel it. > I can't do explain analyze, but here is the explain: > MONSOON=3D# begin; > BEGIN > MONSOON=3D# explain delete from scenario where id=3D'1099'; > QUERY PLAN > > -------------------------------------------------------------------------= ----- > Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 > width=3D6) > Index Cond: (id =3D 1099::bigint) > (2 rows) > > MONSOON=3D# explain delete from scenario where id=3D'1023'; > QUERY PLAN > > -------------------------------------------------------------------------= ----- > Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 > width=3D6) > Index Cond: (id =3D 1023::bigint) > (2 rows) > > MONSOON=3D# explain analyze delete from scenario where id=3D'1023'; > QUERY PLAN > > -------------------------------------------------------------------------= ----------------------------------------------- > Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 > width=3D6) (actual time=3D0.028..0.030 rows=3D1 loops=3D1) > Index Cond: (id =3D 1023::bigint) > Total runtime: 0.174 ms > (3 rows) > > I have also tried increasing statistics on both parent and child tables t= o > 100, vacuum analyze parent and all child tables. But still the same > slowness. > The o/s is Solaris 10, with fsync =3D true. > Any ideas what's going on? > Thanks in advance, > > J > ------=_Part_6949_31381341.1146001590961 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I should also mention that select ... for update is fast:
MONSOON=3D# begin;explain analyze select * from SCENARIO WHERE id =3D '1099= ' FOR UPDATE;
BEGIN
            &nb= sp;            =             &nb= sp;            =      QUERY PLAN
---------------------------------------------------------------------------= ----------------------------------------------
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.17 rows=3D1 width=3D64) (actual time=3D0.016..0.017 rows=3D1 loops=3D1)
   Index Cond: (id =3D 1099::bigint)
 Total runtime: 0.072 ms
(3 rows)




On 4/25/06, Junaili Lie <junaili@gm= ail.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.<= br>            =             &nb= sp;        Table "public.scenario"
     Column      |         Type          |            &n= bsp;      Modifiers
-----------------+-----------------------+---------------------------------= ---------------
 id           &= nbsp;  | bigint           &nb= sp;    | not null default nextval('scenario_seq'::text)
 name           = ; | character varying(50) |
 description     | text            = ;      |
 subscriber_id   | bigint           &nb= sp;    |
 organization_id | bigint       &nb= sp;        |
 schedule_id     | bigint           &nb= sp;    |
Indexes:
    "scenario_pkey" primary key, btree (id)
    "org_ind_scenario_index" btree (organization_i= d)
    "sch_ind_scenario_index" btree (schedule_id)     "sub_ind_scenario_index" btree (subscriber_id)=
Check constraints:
    "$3" CHECK (schedule_id >=3D 0)
    "$2" CHECK (organization_id >=3D 0)
    "$1" CHECK (subscriber_id >=3D 0)
Foreign-key constraints:
    "0_4774" FOREIGN KEY (schedule_id) REFERENCES = schedule(id) ON DELETE CASCADE
    "0_4773" FOREIGN KEY (organization_id) REFEREN= CES organization(id) ON DELETE CASCADE
    "0_4772" FOREIGN KEY (subscriber_id) REFERENCE= S subscriber(id) ON DELETE CASCADE

In all the child tables, the foreign key has the same data type and are ind= exed.
When I do "delete from scenario where id=3D'1023', it takes less than = 200 ms.
But when i do "delete from scenario where id=3D'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=3D# begin;
BEGIN
MONSOON=3D# explain delete from scenario where id=3D'1099';
            &nb= sp;            =          QUERY PLAN
---------------------------------------------------------------------------= ---
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 r= ows=3D1 width=3D6)
   Index Cond: (id =3D 1099::bigint)
(2 rows)

MONSOON=3D# explain delete from scenario where id=3D'1023';
            &nb= sp;            =          QUERY PLAN
---------------------------------------------------------------------------= ---
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 r= ows=3D1 width=3D6)
   Index Cond: (id =3D 1023::bigint)
(2 rows)

MONSOON=3D# explain analyze delete from scenario where id=3D'1023';
            &nb= sp;            =             &nb= sp;            =      QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 rows=3D1 width=3D6) (actual time=3D0.028..0.030 rows=3D1 loops=3D1)
   Index Cond: (id =3D 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 =3D true.
Any ideas what's going on?
Thanks in advance,

J

------=_Part_6949_31381341.1146001590961--