X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BBD4152A39 for ; Wed, 6 Apr 2005 22:05:19 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05267-05 for ; Wed, 6 Apr 2005 21:05:11 +0000 (GMT) Received: from server01.mrt-system.pl (server01.mrt-system.pl [80.53.234.114]) by svr1.postgresql.org (Postfix) with ESMTP id 612A85295D for ; Wed, 6 Apr 2005 22:05:09 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by server01.mrt-system.pl (Mail Server) with ESMTP id 855F03F73D for ; Wed, 6 Apr 2005 23:05:07 +0200 (CEST) Received: from [213.134.169.237] (anpa003.net.autocom.pl [213.134.169.237]) by server01.mrt-system.pl (Mail Server) with ESMTP id 2775C3F70F for ; Wed, 6 Apr 2005 23:05:06 +0200 (CEST) Message-ID: <42544F01.7090208@mrt-system.pl> Date: Wed, 06 Apr 2005 23:05:05 +0200 From: =?UTF-8?B?SmFyb3PFgmF3IFBhxYJrYQ==?= User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Never ending delete story Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS at server01.mrt-system.pl X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/199 X-Sequence-Number: 11596 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