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