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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
