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 -------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
