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 12F5C56965 for ; Wed, 16 Mar 2005 14:35:13 +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 92732-07 for ; Wed, 16 Mar 2005 14:35:12 +0000 (GMT) Received: from sunsite.dcc.uchile.cl (sunsite.dcc.uchile.cl [192.80.24.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9399656962 for ; Wed, 16 Mar 2005 14:35:11 +0000 (GMT) Received: from anakena.dcc.uchile.cl (anakena [192.80.24.6]) by sunsite.dcc.uchile.cl (8.12.11/8.12.11) with ESMTP id j2GEZB7n016370; Wed, 16 Mar 2005 10:35:11 -0400 (CLT) Received: by anakena.dcc.uchile.cl (Postfix, from userid 4151) id A307D5236B; Wed, 16 Mar 2005 10:35:11 -0400 (CLT) Date: Wed, 16 Mar 2005 10:35:11 -0400 From: Alvaro Herrera To: David Gagnon Cc: Michael Fuhr , pgsql-performance@postgresql.org Subject: Re: Performance problem on delete from for 10k rows. May Message-ID: <20050316143511.GC21935@dcc.uchile.cl> References: <42375281.9090102@siunik.com> <20050315233834.GA68209@winnie.fuhr.org> <4238322F.10302@siunik.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4238322F.10302@siunik.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.35 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200503/322 X-Sequence-Number: 11027 On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote: David, > I rerun the example with the debug info turned on in postgresl. As you > can see all dependent tables (that as foreign key on table IC) are > emptied before the DELETE FROM IC statement is issued. For what I > understand the performance problem seem to came from those selects that > point back to IC ( LOG: statement: SELECT 1 FROM ONLY "public"."ic" x > WHERE "icnum" = $1 FOR UPDATE OF x). There are 6 of them. I don't know > where they are comming from. I think they come from the FK checking code. Try to run a VACUUM on the IC table just before you delete from the other tables; that should make the checking almost instantaneous (assuming the vacuuming actually empties the table, which would depend on other transactions). It would be better to be able to use TRUNCATE to do this, but in 8.0 you can't if the tables have FKs. 8.1 is better on that regard ... -- Alvaro Herrera () "Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)