X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2E1E9D7129 for ; Tue, 11 Oct 2005 11:27:44 -0300 (ADT) 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 76328-10 for ; Tue, 11 Oct 2005 14:27:39 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 1E2E6D710C for ; Tue, 11 Oct 2005 11:27:42 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 110E62164D; Tue, 11 Oct 2005 17:11:37 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11529-08; Tue, 11 Oct 2005 17:11:33 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 56C5121649; Tue, 11 Oct 2005 17:11:33 +0300 (EEST) Message-ID: <001101c5ce6f$f0f4e480$0b00a8c0@forge> From: "Andy" To: "Tom Lane" Cc: "Steinar H. Gunderson" , References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge> <20051011121942.GA25543@uio.no> <01e401c5ce67$93d65de0$0b00a8c0@forge> <13905.1129040232@sss.pgh.pa.us> Subject: Re: Massive delete performance Date: Tue, 11 Oct 2005 17:27:40 +0300 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.203 required=5 tests=[AWL=0.153, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/195 X-Sequence-Number: 14963 Ups folks, Indeed there were 2 important indexes missing. Now it runs about 10 times faster. Sorry for the caused trouble :) and thanx for help. Hash IN Join (cost=3307.49..7689.47 rows=30250 width=6) (actual time=227.666..813.786 rows=56374 loops=1) Hash Cond: ("outer".id_order = "inner".id) -> Seq Scan on report (cost=0.00..2458.99 rows=60499 width=10) (actual time=0.035..269.422 rows=60499 loops=1) -> Hash (cost=3109.24..3109.24 rows=30901 width=4) (actual time=227.459..227.459 rows=0 loops=1) -> Seq Scan on orders o (cost=9.73..3109.24 rows=30901 width=4) (actual time=0.429..154.219 rows=57543 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Sort (cost=9.71..9.72 rows=3 width=4) (actual time=0.329..0.330 rows=1 loops=1) Sort Key: cp.id_ag -> Nested Loop (cost=0.00..9.69 rows=3 width=4) (actual time=0.218..0.224 rows=1 loops=1) -> Index Scan using users_name_idx on users u (cost=0.00..5.61 rows=1 width=4) (actual time=0.082..0.084 rows=1 loops=1) Index Cond: ((name)::text = 'dc'::text) -> Index Scan using contactpartner_id_user_idx on contactpartner cp (cost=0.00..4.03 rows=3 width=8) (actual time=0.125..0.127 rows=1 loops=1) Index Cond: (cp.id_user = "outer".id) Total runtime: 31952.811 ms ----- Original Message ----- From: "Tom Lane" To: "Andy" Cc: "Steinar H. Gunderson" ; Sent: Tuesday, October 11, 2005 5:17 PM Subject: Re: [PERFORM] Massive delete performance > "Andy" writes: >> EXPLAIN ANALYZE >> DELETE FROM report WHERE id_order IN >> ... > >> Hash IN Join (cost=3532.83..8182.33 rows=32042 width=6) (actual >> time=923.456..2457.323 rows=59557 loops=1) >> ... >> Total runtime: 456718.658 ms > > So the runtime is all in the delete triggers. The usual conclusion from > this is that there is a foreign key column pointing at this table that > does not have an index, or is not the same datatype as the column it > references. Either condition will force a fairly inefficient way of > handling the FK deletion check. > > regards, tom lane > >