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 B94D6565FF for ; Tue, 22 Mar 2005 23:08:22 +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 15996-07 for ; Tue, 22 Mar 2005 23:08:14 +0000 (GMT) Received: from floppy.pyrenet.fr (unknown [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5CCAC52A4F for ; Tue, 22 Mar 2005 23:08:12 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 8B38D30EA1; Wed, 23 Mar 2005 00:08:12 +0100 (MET) From: Ron Mayer X-Newsgroups: pgsql.performance Subject: Re: Planner issue Date: Tue, 22 Mar 2005 15:09:35 -0800 Organization: Hub.Org Networking Services Lines: 25 Message-ID: <4240A5AF.4010901@cheapcomplexdevices.com> References: <33c6269f05032206565ca4f9f1@mail.gmail.com> <42404663.2010504@commandprompt.com> <33c6269f05032211366ce4563b@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Alex Turner User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en In-Reply-To: <33c6269f05032211366ce4563b@mail.gmail.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200503/480 X-Sequence-Number: 11185 I'm guessing your data is actually more "clustered" than the "correlation" statistic thinks it is. Alex Turner wrote: > trendmls=# explain analyze select listnum from propmain where > listprice<=300000 and listprice>=200000; Is that a database of properties like land/houses? If your table is clustered geographically (by zip code, etc), the index scan might do quite well because all houses in a neighborhood may have similar prices (and therefore live on just a few disk pages). However since high-priced neighborhoods are scattered across the country, the optimizer would see a very low "correlation" and not notice this clustering. If this is the cause, one thing you could do is CLUSTER your table on propmain_listprice_i. I'm quite confident it'll fix this particular query - but might slow down other queries.