Hi!!!
We are running PostgreSQL server version 7.4.6 on RedHat 9 (Shrike) on
single Pentium 4 (2.66 GHz) box with SCSI disc and 512 MB RAM.
Our database contains several tables (small size) and one special table
with ~1000000 records (it contains log entries from system activity).We
decided that its time to do a little clean-up and it's still running
(for about 12 hours) and it seems that it won't stop :((
Here schema of largest table:
Table "public.activities"
Column | Type | Modifiers
-------------------+-----------------------------+-----------
act_id | bigint | not null
act_type | character varying(32) | not null
act_activity_date | timestamp without time zone | not null
act_synch_date | timestamp without time zone |
act_state | character varying(32) |
act_mcn_id | bigint |
act_mcn_alarm | character varying(16) |
act_cmd_id | bigint |
act_ctr_id | bigint |
act_emp_id | bigint |
act_parent_id | bigint |
act_rpt_id | bigint |
Indexes:
"activities_pkey" primary key, btree (act_id)
"activities_act_cmd_id" btree (act_cmd_id)
"activities_act_ctr_id" btree (act_ctr_id)
"activities_act_state_idx" btree (act_state)
"activities_act_type_idx" btree (act_type)
Foreign-key constraints:
"fk7a1b3bed494acc46" FOREIGN KEY (act_ctr_id) REFERENCES
controllers(ctr_id)
"fk7a1b3bed4c50f03f" FOREIGN KEY (act_emp_id) REFERENCES
employees(emp_id)
"fk7a1b3bed48e1ca8d" FOREIGN KEY (act_cmd_id) REFERENCES
commands(cmd_id)
"fk7a1b3bed5969e16f" FOREIGN KEY (act_mcn_id) REFERENCES
machines(mcn_id)
"fk7a1b3bedf3fd6e40" FOREIGN KEY (act_parent_id) REFERENCES
activities(act_id)
"fk7a1b3bed62ac0851" FOREIGN KEY (act_rpt_id) REFERENCES
and our killer delete:
mrt-vend2-jpalka=# explain delete from activities where
act_type='controller-activity' and act_ctr_id in (select ctr_id from
controllers where ctr_opr_id in (1,2));
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Merge IN Join (cost=9.87..17834.97 rows=84933 width=6)
Merge Cond: ("outer".act_ctr_id = "inner".ctr_id)
-> Index Scan using activities_act_ctr_id on activities
(cost=0.00..34087.59 rows=402627 width=14)
Filter: ((act_type)::text = 'controller-activity'::text)
-> Sort (cost=9.87..10.09 rows=89 width=8)
Sort Key: controllers.ctr_id
-> Seq Scan on controllers (cost=0.00..6.99 rows=89 width=8)
Filter: ((ctr_opr_id = 1) OR (ctr_opr_id = 2))
(8 rows)
reports(rpt_id)
Table controllers contains about 200 records.Is it problem with large
number of foreign keys in activities table?
Can you help me?
Thanks,
Jaroslaw Palka
