Re: Foreign key slows down copy/insert

Christopher Kings-Lynne wrote:
>> But why then is the speed acceptable if I copy and then manually add
>> the FK? Is the check done by the FK so much different from when it is
>> done automatically using an active deffered FK?
>
> Yeah I think it uses a different query formulation... Actually I only
> assume that deferred fk's don't use that - I guess your experiment
> proves that.

In my tests deferred or not deferred makes no difference in speed. I am
still quite surprised by how huge the difference is.. this makes FKs
quite unusable when added a lot of data to a table.

> Actually, you can just "disable" them if you want to be really dirty :)

Thanks for the pointer. I got this from the archives:

------------------------
update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME';

to enable them after you are done, do

update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME';
------------------------

I assume the re-enabling will cause an error when the copy/insert added
data that does not satisfy the FK. In that case I'll indeed end up with
invalid data, but at least I will know about it.

Thanks,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------