pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Gagnon <dgagnon@siunik.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: 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 12:02:04 -0500
Message-ID: <4238668C.5080002@siunik.com> (raw)
In-Reply-To: <20050316082636.J53581@megazone.bigpanda.com>
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>



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



view thread (12+ messages)  latest in thread

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