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 83E735521B for ; Wed, 16 Mar 2005 16:28:17 +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 38767-06 for ; Wed, 16 Mar 2005 16:28:08 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 86BA85514D for ; Wed, 16 Mar 2005 16:28:07 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A1681359EA; Wed, 16 Mar 2005 08:28:07 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9FAAE359E9; Wed, 16 Mar 2005 08:28:07 -0800 (PST) Date: Wed, 16 Mar 2005 08:28:07 -0800 (PST) From: Stephan Szabo To: David Gagnon Cc: Alvaro Herrera , Michael Fuhr , pgsql-performance@postgresql.org Subject: Re: Performance problem on delete from for 10k rows. May In-Reply-To: <42384D26.4020903@siunik.com> Message-ID: <20050316082636.J53581@megazone.bigpanda.com> References: <42375281.9090102@siunik.com> <20050315233834.GA68209@winnie.fuhr.org> <4238322F.10302@siunik.com> <20050316143511.GC21935@dcc.uchile.cl> <42384D26.4020903@siunik.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/327 X-Sequence-Number: 11032 On Wed, 16 Mar 2005, David Gagnon wrote: > Hi > > >>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). > > > > > I'll try to vaccum first before I start the delete to see if it change > something. > > There is probably a good reason why but I don't understant why in a > foreign key check it need to check the date it points to. > > You delete a row from table IC and do a check for integrity on tables > that have foreign keys on IC (make sense). But why checking back IC? Because in the general case there might be another row which satisfies the constraint added between the delete and the check.