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 2CACA5672D for ; Wed, 16 Mar 2005 14:06:46 +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 82329-04 for ; Wed, 16 Mar 2005 14:06:34 +0000 (GMT) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id F183F565F9 for ; Wed, 16 Mar 2005 14:06:32 +0000 (GMT) Received: (qmail 1936 invoked from network); 16 Mar 2005 14:05:33 -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:05:33 -0000 Message-ID: <42383D60.7080900@fastcrypt.com> Date: Wed, 16 Mar 2005 09:06:24 -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: David Gagnon Cc: 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> In-Reply-To: <4238322F.10302@siunik.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.141 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200503/320 X-Sequence-Number: 11025 David Gagnon wrote: > Hi All, > > 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. But if I want to > delete the content of the table (~10k) it may be long to those 6 > selects for each deleted rows. Why are those selects are there ? Are > those select really run on each row deleted? You are using hibernate. Hibernate is generating them to lock the tables. > > > I'm running version 7.4.5 on cygwin. I ran the same delete from > pgAdminIII and I got 945562ms for all the deletes within the same > transaction .. (so I was wrong saying it took less time in > PgAdminIII... sorry about this). > > Do you have any idea why those 6 selects are there? Hibernate > > Maybe I can drop indexes before deleting the content of the table. I > didn't planned to because tables are quite small and it's more > complicated in my environment. And tell me if I'm wrong but if I drop > indexed do I have to reload all my stored procedure (to reset the > planner related info)??? Remember having read that somewhere.. (was it > in the Postgresql General Bit newletter ...anyway) > > Thanks for your help I really appréciate it :-) > > /David > > LOG: duration: 144.000 ms > LOG: statement: DELETE FROM YN > LOG: duration: 30.000 ms > LOG: statement: DELETE FROM YO > LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = > $1 AND "yonum" = $2 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" = > $1 AND "ynyonum" = $2 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = > $1 AND "yonum" = $2 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" = > $1 AND "yryonum" = $2 FOR UPDATE OF x > LOG: duration: 83.000 ms > LOG: connection received: host=127.0.0.1 port=2196 > LOG: connection authorized: user=admin database=webCatalog > LOG: statement: set datestyle to 'ISO'; select version(), case when > pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else > getdatabaseencoding() end; > LOG: duration: 2.000 ms > LOG: statement: set client_encoding = 'UNICODE' > LOG: duration: 0.000 ms > LOG: statement: DELETE FROM IY > LOG: duration: 71.000 ms > LOG: statement: DELETE FROM IA > LOG: duration: 17.000 ms > LOG: statement: DELETE FROM IQ > LOG: duration: 384.000 ms > LOG: statement: DELETE FROM IC > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = > $1 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = > $1 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" = > $1 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" = > $1 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = > $1 FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 > FOR UPDATE OF x > LOG: statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = > $1 FOR UPDATE OF x > LOG: duration: 656807.000 msMichael Fuhr wrote: > > > > > > ----------------------- > DELETE FROM BM; > DELETE FROM BD; > DELETE FROM BO; > DELETE FROM IL; > DELETE FROM YR; > DELETE FROM YN; > DELETE FROM YO; > DELETE FROM IY; > DELETE FROM IA; > DELETE FROM IQ; > DELETE FROM IC; > > Michael Fuhr wrote: > >> On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote: >> >> >> >>> Il get this strange problem when deleting rows from a Java program. >>> Sometime (For what I noticed it's not all the time) the server take >>> almost forever to delete rows from table. >>> >> >> >> Do other tables have foreign key references to the table you're >> deleting from? If so, are there indexes on the foreign key columns? >> >> Do you have triggers or rules on the table? >> >> Have you queried pg_locks during the long-lasting deletes to see >> if the deleting transaction is waiting for a lock on something? >> >> >> >>> I rememeber having tried to delete the content of my table (IC) from >>> PgAdminIII and I took couples of seconds!!! Not minutes. >>> >> >> >> How many records did you delete in this case? If there are foreign >> key references, how many records were in the referencing tables? >> How repeatable is the disparity in delete time? A single test case >> might have been done under different conditions, so it might not >> mean much. No offense intended, but "I remember" doesn't carry as >> much weight as a documented example. >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561