X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 43C7C9FA5C3 for ; Fri, 16 Jun 2006 10:30:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 36762-01 for ; Fri, 16 Jun 2006 10:30:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8043B9FA42B for ; Fri, 16 Jun 2006 10:30:38 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5GDUWZR016187; Fri, 16 Jun 2006 09:30:32 -0400 (EDT) To: postgres@leangen.net cc: pgsql-performance@postgresql.org Subject: Re: Delete operation VERY slow... In-reply-to: <1150445542.4812.34.camel@sonoda.bioscene.co.jp> References: <1150441126.4812.23.camel@sonoda.bioscene.co.jp> <674d1f8a0606160022p1b042676gba28553bac5ab5ea@mail.gmail.com> <1150445542.4812.34.camel@sonoda.bioscene.co.jp> Comments: In-reply-to David Leangen message dated "Fri, 16 Jun 2006 17:12:22 +0900" Date: Fri, 16 Jun 2006 09:30:32 -0400 Message-ID: <16186.1150464632@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/351 X-Sequence-Number: 19708 David Leangen writes: > The only inconvenience is that I need to remove all the foreign key > constraints before truncating, then put them back after. I was about to ask if you had any. Usually the reason for DELETE being slow is that you have foreign key references to (not from) the table and the referencing columns aren't indexed. This forces a seqscan search of the referencing table for each row deleted :-( regards, tom lane