> I'm wondering,
> is there any sense to cluster table using two-column index ?
>
>
We've had this discussion a few weeks ago. Look at the archives for my
post "One Big Trend ...."
The problem is that while the statistics can resonably deal with the
primary column it completely misses the trends produced in the secondary
column. This situation can be seen quite clearly using the US Census TIGER
database.
I imagine the primary and secondary columns both have a discrete index and
the combined index is for the cluser or more complex queries.
If you execute a query based on the secondary column's index that should
return about 100 rows. The "smaller" trends in the column produced by the
cluster are not detected. So, rather then seeing that its probably a few
index seeks and a few table seeks because the data is fairly well grouped,
it opts, instead, to do a table scan because it doesn't see any
correlation.
Increasing the number of samples in ANALIZE helps a bit, but the solution
is better statistics or maybe hints that can be embedded into the query.