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