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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to