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 CE44E53215 for ; Fri, 15 Apr 2005 07:22:57 -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 48626-04 for ; Fri, 15 Apr 2005 10:22:53 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id 93E4353FB3 for ; Fri, 15 Apr 2005 07:22:51 -0300 (ADT) Received: (qmail 24854 invoked from network); 15 Apr 2005 12:22:58 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 15 Apr 2005 12:22:58 +0200 Date: Fri, 15 Apr 2005 12:22:52 +0200 To: "Richard van den Berg" Subject: Re: Foreign key slows down copy/insert Cc: pgsql-performance@postgresql.org References: <425E6045.4060201@trust-factory.com> <425F77E4.7030504@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: 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/333 X-Sequence-Number: 11730 > 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 94 ms for an index scan ? this look really slow... was the index in the RAM cache ? does it fit ? is it faster the second time ? If it's still that slow, something somewhere is severely screwed. B has 150K rows you say, so everything about B should fit in RAM, and you should get 0.2 ms for an index scan, not 90 ms ! Try this : Locate the files on disk which are involved in table B (table + indexes) looking at the system catalogs Look at the size of the files. Is the index severely bloated ? REINDEX ? DROP/Recreate the index ? Load them into the ram cache (just cat files | wc -b several times until it's almost instantaneous) Retry your query and your COPY I know it's stupid... but it's a lot faster to load an index in the cache by plainly reading the file rather than accessing it randomly. (even though, with this number of rows, it should not be THAT slow !)