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 0A99053860 for ; Thu, 14 Apr 2005 14:22:29 -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 23752-04 for ; Thu, 14 Apr 2005 17:22:21 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id BCD98535EF for ; Thu, 14 Apr 2005 14:22:20 -0300 (ADT) Received: (qmail 31847 invoked from network); 14 Apr 2005 19:22:24 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 14 Apr 2005 19:22:24 +0200 Date: Thu, 14 Apr 2005 19:22:19 +0200 To: "Richard van den Berg" , pgsql-performance@postgresql.org Subject: Re: Foreign key slows down copy/insert References: <425E6045.4060201@trust-factory.com> From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <425E6045.4060201@trust-factory.com> User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/302 X-Sequence-Number: 11699 > I have a table A with an int column ID that references table B column > ID. Table B has about 150k rows, and has an index on B.ID. When trying > to copy 1 million rows into A, I get the following \timings: 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. 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. Then, supposing it's the column in A which REFERENCE's B(id) : SELECT id FROM A LIMIT 1; (check type) SELECT id FROM B LIMIT 1; (check type) EXPLAIN ANALYZE the following : SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1); It should use the index. Does it ?