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 02C9D565DD for ; Wed, 16 Mar 2005 14:56:53 +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 98153-09 for ; Wed, 16 Mar 2005 14:56:45 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id E7A0C5646C for ; Wed, 16 Mar 2005 14:56:44 +0000 (GMT) Received: (qmail 2478 invoked from network); 16 Mar 2005 14:55:45 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 16 Mar 2005 14:55:45 -0000 Message-ID: <4238492B.2000801@fastcrypt.com> Date: Wed, 16 Mar 2005 09:56:43 -0500 From: Dave Cramer Reply-To: pg@fastcrypt.com Organization: Postgres International User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alvaro Herrera Cc: David Gagnon , Michael Fuhr , pgsql-performance@postgresql.org Subject: Re: Performance problem on delete from for 10k rows. May References: <42375281.9090102@siunik.com> <20050315233834.GA68209@winnie.fuhr.org> <4238322F.10302@siunik.com> <20050316143511.GC21935@dcc.uchile.cl> In-Reply-To: <20050316143511.GC21935@dcc.uchile.cl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.038 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/323 X-Sequence-Number: 11028 Really? Postgres is generating these queries ??? Dave Alvaro Herrera wrote: >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 ... > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561