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 AE157536CE for ; Fri, 25 Mar 2005 00:52:09 +0000 (GMT) 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 27309-01 for ; Fri, 25 Mar 2005 00:52:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E3CCA536C8 for ; Fri, 25 Mar 2005 00:52:06 +0000 (GMT) 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 j2P0q7IP002631; Thu, 24 Mar 2005 19:52:07 -0500 (EST) To: Karim Nassar Cc: pgsql-performance@postgresql.org Subject: Re: Delete query takes exorbitant amount of time In-reply-to: <1111709457.9085.127.camel@k2.cet.nau.edu> References: <1111709457.9085.127.camel@k2.cet.nau.edu> Comments: In-reply-to Karim Nassar message dated "Thu, 24 Mar 2005 17:10:57 -0700" Date: Thu, 24 Mar 2005 19:52:07 -0500 Message-ID: <2630.1111711927@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/527 X-Sequence-Number: 11232 Karim Nassar writes: > Here is the statement: > orfs=# explain analyze DELETE FROM int_sensor_meas_type WHERE > id_meas_type IN (SELECT * FROM meas_type_ids); > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > Hash Join (cost=11.53..42.06 rows=200 width=6) (actual > time=1.564..2.840 rows=552 loops=1) > ... > Total runtime: 2499616.216 ms > (7 rows) Notice that the actual join is taking 2.8 ms. The other ~40 minutes is in operations that we cannot see in this plan, but we can surmise are ON DELETE triggers. > Where do I go from here? Look at what your triggers are doing. My private bet is that you have unindexed foreign keys referencing this table, and so each deletion forces a seqscan of some other, evidently very large, table(s). regards, tom lane