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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
