Re: [PERFORM] slow deletes on pgsql 7.4
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 <[EMAIL PROTECTED]> wrote: "Junaili Lie" <[EMAIL PROTECTED]> 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
Re: [PERFORM] slow deletes on pgsql 7.4
"Junaili Lie" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] slow deletes on pgsql 7.4
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 <[EMAIL PROTECTED]> wrote: "Junaili Lie" <[EMAIL PROTECTED]> 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
Re: [PERFORM] slow deletes on pgsql 7.4
"Junaili Lie" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] slow deletes on pgsql 7.4
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 <[EMAIL PROTECTED]> 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