Re: Foreign key slows down copy/insert

About the foreign key performance:

Maybe foreign key checks could be delayed into the COMMIT phase.
In that position, you could check, that there are lots of foreign key
checks
for each foreign key pending, and do the foreign key check for an area
or for the whole table, if it is faster.

I have heard, that the database must be in consistent state after COMMIT,
but it does not have necessarily to be okay inside a transaction.

1. COMMIT wanted
2. If there are lots of foreign key checks pending, do either an area
foreign key check
(join result must be 0 rows), or a full table join.
3. If all foreign key checks are okay, complete the COMMIT operation.
4. If a foreign key check fails, go into the ROLLBACK NEEDED state.

Maybe Tom Lane meant the same.

set option delayed_foreign_keys=true;
BEGIN;
insert 1000 rows.
COMMIT;

Regards,
Marko Ristola

Christopher Kings-Lynne wrote:

>> My problem with this really is that in my database it is hard to
>> predict which inserts will be huge (and thus need FKs dissabled), so
>> I would have to code it around all inserts. Instead I can code my own
>> integirty logic and avoid using FKs all together.
>
>
> Just drop the fk and re-add it, until postgres gets more smarts.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org