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
