Re: Performance problem on delete from for 10k rows. May

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