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