From: Richard van den Berg <richard.vandenberg@trust-factory.com>
To: PFC <lists@boutiquenumerique.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Foreign key slows down copy/insert
Date: Fri, 15 Apr 2005 10:14:28 +0200
Message-ID: <425F77E4.7030504@trust-factory.com> (raw)
In-Reply-To: <op.so8fvhlwth1vuj@localhost>
References: <425E6045.4060201@trust-factory.com>
<op.so8fvhlwth1vuj@localhost>
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
-------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: richard.vandenberg@trust-factory.com, lists@boutiquenumerique.com
Subject: Re: Foreign key slows down copy/insert
In-Reply-To: <425F77E4.7030504@trust-factory.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox