Re: Planner issue

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Alex Turner <armtuk(at)gmail(dot)com>
Subject: Re: Planner issue
Date: 2005-03-22 23:08:39
Message-ID: 4240A577.6070806@cheapcomplexdevices.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Ron Mayer 2005-03-22 23:09:35 Re: Planner issue
Previous Message Ron Mayer 2005-03-22 22:59:44 Re: What about utility to calculate planner cost constants?