Skip site navigation (1) Skip section navigation (2)

Re: slow deletes on pgsql 7.4

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 (view raw or flat)
Thread:
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
>

In response to

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2006-04-25 22:03:35
Subject: Re: Large (8M) cache vs. dual-core CPUs
Previous:From: Ron PeacetreeDate: 2006-04-25 21:43:14
Subject: Re: Large (8M) cache vs. dual-core CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group