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 :


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)
-> 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. |
Bazarstraat 44a    |
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

Reply via email to