Re: Performance problem on delete from for 10k rows. May - Mailing list pgsql-performance

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