Re: Foreign key slows down copy/insert

>> Deferring makes no difference to FK checking speed...
>
>
> 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.

>> Well, that's what people do - even pg_dump will restore data and add
>> the foreign key afterward...
>
> If I have to go this route, is there a way of automatically dropping and
> re-adding FKs? I can probably query pg_constraints and drop the
> appropriate ones, but how do I re-add them after the copy/insert?

Actually, you can just "disable" them if you want to be really dirty :)
You have to be confident that the data you're inserting does satisfy
the FK, however otherwise you can end up with invalid data.

To see how to do that, try pg_dump with --disable-triggers mode enabled.
Just do a data-only dump.

Chris