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:

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

---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]