Re: Foreign key slows down copy/insert

PFC wrote:
> You're using 7.4.5. It's possible that you have a type mismatch in
> your foreign keys which prevents use of the index on B.

I read about this pothole and made damn sure the types match. (Actually,
I kinda hoped that was the problem, it would have been an easy fix.)

> First of all, be really sure it's THAT foreign key, ie. do your COPY
> with only ONE foreign key at a time if you have several, and see which
> one is the killer.

I took exactly this route, and the first FK I tried already hit the
jackpot. The real table had 4 FKs.

> EXPLAIN ANALYZE the following :
>
> SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1);
>
> It should use the index. Does it ?

It sure looks like it:

Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual
time=93.824..93.826 rows=1 loops=1)
Index Cond: (id = $0)
InitPlan
-> Limit (cost=0.00..0.04 rows=1 width=4) (actual
time=15.128..15.129 rows=1 loops=1)
-> Seq Scan on A (cost=0.00..47569.70 rows=1135570
width=4) (actual time=15.121..15.121 rows=1 loops=1)
Total runtime: 94.109 ms

The real problem seems to be what Chris and Stephen pointed out: even
though the FK check is deferred, it is done on a per-row bases. With 1M
rows, this just takes forever.

Thanks for the help.

--
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
-------------------------------------------