pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Gagnon <dgagnon@siunik.com>
To: Alvaro Herrera <alvherre@dcc.uchile.cl>
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 10:13:42 -0500
Message-ID: <42384D26.4020903@siunik.com> (raw)
In-Reply-To: <20050316143511.GC21935@dcc.uchile.cl>
References: <42375281.9090102@siunik.com>
	<20050315233834.GA68209@winnie.fuhr.org>
	<4238322F.10302@siunik.com>
	<20050316143511.GC21935@dcc.uchile.cl>

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?  
I'm pretty sure there is a good reason but it seems to have a big 
performance impact... In this case.  It means it's not really feasable 
to empty the content of a schema.  The table has only 10k .. with a huge 
table it's not feasible just because the checks on itselft!

Is someone can explain why there is this extra check?  Is that can be 
fixed or improved?

Thanks for your help

/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:




>It would be better to be able to use TRUNCATE to do this, but in 8.0 you
>can't if the tables have FKs.  8.1 is better on that regard ...
>
>  
>




view thread (12+ messages)  latest in thread

Message-ID: <42384D26.4020903@siunik.com>
Permalink:  ../42384D26.4020903@siunik.com/
Also on:    postgresql.org/message-id/42384D26.4020903@siunik.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: dgagnon@siunik.com, alvherre@dcc.uchile.cl, mike@fuhr.org
  Subject: Re: Performance problem on delete from for 10k rows.  May
  In-Reply-To: <42384D26.4020903@siunik.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