Search Postgresql Archives

Re: Query planner refuses to use index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux