COPY with fk's slow

John Smith <john_smith_45678@yahoo.com> writes:

I'm playing around with importing 10,000 records into a table with a single index and a single foreign key. COPY takes roughly 10 minutes(!). However, if I drop the foreign key, COPY takes a couple seconds, THEN adding the foreign key back takes another few seconds. Any idea why COPY with a f/k takes so much longer? Any workarounds? Such as lock the table, drop fk, copy, add fk, unlock?

Sounds like you are getting a bad plan for the fk-checking query. Are
the referencing and referenced columns exactly the same datatype? Do
they both have indexes?

BTW, when experimenting with this, keep in mind that the backend caches
query plans for fk queries. You'll typically need to start a fresh
connection after doing something that you hope will improve the plan.

regards, tom lane

Turns out there was a duplicate index (on the id column) in the parent table. Dropped it, readded the f/k and now copy takes 5-10 secs :).
John
Tom Lane <tgl@sss.pgh.pa.us> wrote:John Smith writes:

I'm playing around with importing 10,000 records into a table with a single index and a single foreign key. COPY takes roughly 10 minutes(!). However, if I drop the foreign key, COPY takes a couple seconds, THEN adding the foreign key back takes another few seconds. Any idea why COPY with a f/k takes so much longer? Any workarounds? Such as lock the table, drop fk, copy, add fk, unlock?

Sounds like you are getting a bad plan for the fk-checking query. Are
the referencing and referenced columns exactly the same datatype? Do
they both have indexes?

BTW, when experimenting with this, keep in mind that the backend caches
query plans for fk queries. You'll typically need to start a fresh
connection after doing something that you hope will improve the plan.

regards, tom lane

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now