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 90D68566E3 for ; Tue, 22 Mar 2005 23:07:37 +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 16363-01 for ; Tue, 22 Mar 2005 23:07:15 +0000 (GMT) Received: from floppy.pyrenet.fr (unknown [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 99D415664E for ; Tue, 22 Mar 2005 23:07:14 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 0B8E430EA1; Wed, 23 Mar 2005 00:07:17 +0100 (MET) From: Ron Mayer X-Newsgroups: pgsql.performance Subject: Re: Planner issue Date: Tue, 22 Mar 2005 15:08:39 -0800 Organization: Hub.Org Networking Services Lines: 25 Message-ID: <4240A577.6070806@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/479 X-Sequence-Number: 11184 I'm guessing your data is actually more "clustered" than the "correlation" stastic 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.