pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: David Gagnon <dgagnon@siunik.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem on delete from for 10k rows.  May
Date: Wed, 16 Mar 2005 09:06:24 -0500
Message-ID: <42383D60.7080900@fastcrypt.com> (raw)
In-Reply-To: <4238322F.10302@siunik.com>
References: <42375281.9090102@siunik.com>
	<20050315233834.GA68209@winnie.fuhr.org>
	<4238322F.10302@siunik.com>



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




view thread (12+ messages)  latest in thread

Message-ID: <42383D60.7080900@fastcrypt.com>
Permalink:  ../42383D60.7080900@fastcrypt.com/
Also on:    postgresql.org/message-id/42383D60.7080900@fastcrypt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pg@fastcrypt.com, dgagnon@siunik.com, mike@fuhr.org
  Subject: Re: Performance problem on delete from for 10k rows.  May
  In-Reply-To: <42383D60.7080900@fastcrypt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox