Re: Query planner refuses to use index

On Friday 22 July 2005 16:17, Tom Lane pondered:
> Pre-8.0 tends to underestimate the correlation of a multicolumn index.
> (8.0 may too, but not as much.)

I actually upgraded to 8.0.3 now and 2 things have changed. Firstly, I don't
need to do the annoying casts anymore as the query planner now recognises
which literals are compatible with which indexes. Secondly, and you're right
here, 8.0 has decreased the gap between index and sequential scan cost
estimate significantly, but not nearly sufficiently to detect that the index
scan is indeed superior.

> > Also, note that set_id is strictly increasing (hence correlation of 1)
> > and rec_time is strictly increasing within records with same set_id.
>
> So the reason the indexscan is so good is that the ordering correlation
> is perfect. This isn't the planner's default assumption, and
> unfortunately we haven't got statistics available that would allow
> correlation of a multicolumn index to be estimated well.

Hmm, what's wrong with using the 'correlation' column of pg_stats? It told us
straight away that the correlation on set_id was perfect. Even when leaving
out the condition on the second index column (rec_time) the query planner
thinks a sequential scan is more appropriate (please refer to the text file
in my other most recent post for more details).

May I file a bug report for this? I really think that this points to a
deficiency in the query planner.

--
Kilian Hagemann

Climate Systems Analysis Group
University of Cape Town
Republic of South Africa
Tel(w): ++27 21 650 2748