X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DA06F53B8A for ; Fri, 15 Apr 2005 05:14:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12096-01 for ; Fri, 15 Apr 2005 08:14:40 +0000 (GMT) Received: from localhost.net (cust.89.121.adsl.cistron.nl [195.64.89.121]) by svr1.postgresql.org (Postfix) with ESMTP id 2D81E539B5 for ; Fri, 15 Apr 2005 05:14:39 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by localhost.net (Postfix) with ESMTP id 3248619B3BF; Fri, 15 Apr 2005 10:14:34 +0200 (CEST) Message-ID: <425F77E4.7030504@trust-factory.com> Date: Fri, 15 Apr 2005 10:14:28 +0200 From: Richard van den Berg Organization: Trust Factory User-Agent: Debian Thunderbird 1.0 (X11/20050116) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PFC Cc: pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert References: <425E6045.4060201@trust-factory.com> In-Reply-To: X-Enigmail-Version: 0.90.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/330 X-Sequence-Number: 11727 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 -------------------------------------------