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 7F1BA555BA for ; Thu, 10 Mar 2005 00:24:52 +0000 (GMT) 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 99965-06 for ; Thu, 10 Mar 2005 00:24:44 +0000 (GMT) Received: from gizmo07bw.bigpond.com (gizmo07bw.bigpond.com [144.140.70.42]) by svr1.postgresql.org (Postfix) with SMTP id 97EC556448 for ; Thu, 10 Mar 2005 00:24:41 +0000 (GMT) Received: (qmail 26809 invoked from network); 10 Mar 2005 00:24:41 -0000 Received: from unknown (HELO bwmam05.bigpond.com) (144.135.24.81) by gizmo07bw.bigpond.com with SMTP; 10 Mar 2005 00:24:41 -0000 Received: from cpe-203-45-203-73.qld.bigpond.net.au ([203.45.203.73]) by bwmam05.bigpond.com(MAM REL_3_4_2a 44/6531782) with SMTP id 6531782; Thu, 10 Mar 2005 10:24:41 +1000 Message-ID: <422F93CE.10400@bigpond.net.au> Date: Thu, 10 Mar 2005 10:24:46 +1000 From: David Brown User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Jim C. Nasby" Cc: pgsql-performance@postgresql.org, Tom Lane Subject: Re: index scan on =, but not < ? References: <001f01c5240d$a920c8c0$0200a8c0@dell8200> <20050309043821.GA1569@wolff.to> <20050309045519.GA509@decibel.org> <20050309052020.GB1569@wolff.to> <20050309172238.GD528@decibel.org> In-Reply-To: <20050309172238.GD528@decibel.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.752 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200503/175 X-Sequence-Number: 10880 Jim C. Nasby wrote: >Ahh, I was thinking of a high correlation factor on the index. I still >question 5% though... that seems awefully low. > > Not really. It all depends on how many records you're packing into each page. 1% may well be the threshold for small records. Tom mentioned this in the last couple of months. He was citing a uniform distribution as an example and I thought that sounded a little pessimistic, but when I did the (possibly faulty) math with a random distribution, I discovered he wasn't far off. It's not this simple, but if you can fit 50 randomly organized records into each page and you want to retrieve 2% of the rows, it's likely you'll have to fetch every page - believe it or not. What concerns me is that this all depends on the correlation factor, and I suspect that the planner is not giving enough weight to this. Actually, I'm wondering if it's even looking at the statistic, but I haven't created a test to check. It might explain quite a few complaints about the planner not utilizing indexes.