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 67DB2DA423 for ; Fri, 11 Nov 2005 10:18:26 -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 58096-09 for ; Fri, 11 Nov 2005 14:18:22 +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 245DED9577 for ; Fri, 11 Nov 2005 10:18:23 -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 jABEH4G8018121; Fri, 11 Nov 2005 09:17:04 -0500 (EST) To: Richard Huxton cc: Mitch Skinner , pgsql-performance@postgresql.org Subject: Re: same plan, add 1 condition, 1900x slower In-reply-to: <437485DB.50005@archonet.com> References: <1131630176.29496.91.camel@enzian> <10164.1131643400@sss.pgh.pa.us> <1131700635.29496.136.camel@enzian> <437485DB.50005@archonet.com> Comments: In-reply-to Richard Huxton message dated "Fri, 11 Nov 2005 11:51:55 +0000" Date: Fri, 11 Nov 2005 09:17:04 -0500 Message-ID: <18120.1131718624@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/206 X-Sequence-Number: 15463 Richard Huxton writes: > Mitch Skinner wrote: >> The version with the condition is definitely doing more I/O. The >> version without the condition doesn't read at all. > Does external_id_map really have 15 million rows? If not, try a VACUUM > FULL on it. Be prepared to give it some time to complete. Please don't, actually, until we understand what's going on. The thing is that the given plan will fetch every row indicated by the index in both cases, in order to check the row's visibility. I don't see how an additional test on a non-indexed column would cause any additional I/O. If the value were large enough to be toasted out-of-line then it could cause toast table accesses ... but we're speaking of a char(3). regards, tom lane