X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EBC26DAE49 for ; Thu, 10 Nov 2005 13:24:37 -0400 (AST) 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 12429-10 for ; Thu, 10 Nov 2005 17:24:36 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 664CCDA5B3 for ; Thu, 10 Nov 2005 13:24:36 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAAHNKhH010165; Thu, 10 Nov 2005 12:23:20 -0500 (EST) To: Mitch Skinner Cc: pgsql-performance@postgresql.org Subject: Re: same plan, add 1 condition, 1900x slower In-reply-to: <1131630176.29496.91.camel@enzian> References: <1131630176.29496.91.camel@enzian> Comments: In-reply-to Mitch Skinner message dated "Thu, 10 Nov 2005 05:42:56 -0800" Date: Thu, 10 Nov 2005 12:23:20 -0500 Message-ID: <10164.1131643400@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200511/190 X-Sequence-Number: 15447 Mitch Skinner writes: > This is with Postgres 8.0.3. Any advice is appreciated. These are exactly the same plan, except for the addition of the extra filter condition ... > -> Index Scan using external_id_map_primary_key on external_id_map > eim (cost=0.00..2345747.01 rows=15560708 width=26) (actual > time=0.061..2.944 rows=2175 loops=1) > -> Index Scan using external_id_map_primary_key on external_id_map > eim (cost=0.00..2384648.78 rows=4150 width=26) (actual > time=0.020..21068.508 rows=1186 loops=1) > Filter: (source = 'SCH'::bpchar) Apparently, you are using a platform and/or locale in which strcoll() is spectacularly, god-awfully slow --- on the order of 10 msec per comparison. This is a bit hard to believe but I can't make sense of those numbers any other way. What is the platform exactly, and what database locale and encoding are you using? regards, tom lane