Re: COPY with fk's slow

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Smith <john_smith_45678(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: COPY with fk's slow
Date: 2003-02-06 14:31:02
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

John Smith <john_smith_45678(at)yahoo(dot)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

In response to


Browse pgsql-general by date

  From Date Subject
Next Message Serkan Bektaþ 2003-02-06 14:32:50 FW: Webmin version 1.060 released
Previous Message Tom Lane 2003-02-06 14:26:36 Re: How do I create an array?