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 063E3D7057 for ; Tue, 11 Oct 2005 11:17:30 -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 48567-10 for ; Tue, 11 Oct 2005 14:17:21 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 54BC0D6E41 for ; Tue, 11 Oct 2005 11:17:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9BEHCNv013906; Tue, 11 Oct 2005 10:17:12 -0400 (EDT) To: "Andy" Cc: "Steinar H. Gunderson" , pgsql-performance@postgresql.org Subject: Re: Massive delete performance In-reply-to: <01e401c5ce67$93d65de0$0b00a8c0@forge> References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge> <20051011121942.GA25543@uio.no> <01e401c5ce67$93d65de0$0b00a8c0@forge> Comments: In-reply-to "Andy" message dated "Tue, 11 Oct 2005 16:27:48 +0300" Date: Tue, 11 Oct 2005 10:17:12 -0400 Message-ID: <13905.1129040232@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/193 X-Sequence-Number: 14961 "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