Thread: slow deletes on pgsql 7.4

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

"Junaili Lie" <junaili@gmail.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

"Junaili Lie" <junaili@gmail.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