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 2178C565D3 for ; Wed, 16 Mar 2005 17:00:28 +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 51838-01 for ; Wed, 16 Mar 2005 17:00:20 +0000 (GMT) Received: from relais.videotron.ca (relais.videotron.ca [24.201.245.36]) by svr1.postgresql.org (Postfix) with ESMTP id 47815565C9 for ; Wed, 16 Mar 2005 17:00:18 +0000 (GMT) Received: from [192.168.1.100] ([24.202.23.128]) by VL-MO-MR007.ip.videotron.ca (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003)) with ESMTP id <0IDG00B55F69WO@VL-MO-MR007.ip.videotron.ca> for pgsql-performance@postgresql.org; Wed, 16 Mar 2005 11:58:58 -0500 (EST) Date: Wed, 16 Mar 2005 12:02:04 -0500 From: David Gagnon Subject: Re: Performance problem on delete from for 10k rows. May In-reply-to: <20050316082636.J53581@megazone.bigpanda.com> To: Stephan Szabo Cc: Alvaro Herrera , Michael Fuhr , pgsql-performance@postgresql.org Message-id: <4238668C.5080002@siunik.com> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) References: <42375281.9090102@siunik.com> <20050315233834.GA68209@winnie.fuhr.org> <4238322F.10302@siunik.com> <20050316143511.GC21935@dcc.uchile.cl> <42384D26.4020903@siunik.com> <20050316082636.J53581@megazone.bigpanda.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/328 X-Sequence-Number: 11033 Stephan Szabo wrote: >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. > > > So it's means if I want to reset the shema with DELETE FROM Table statemnets I must first drop indexes, delete the data and then recreate indexes and reload stored procedure. Or I can suspend the foreign key check in the db right. I saw something on this. Is that possible to do this from the JDBC interface? Is there any other options I can consider ? Thanks for your help! /David