Re: Shouldn't the planner have a higher cost for reverse index scans?

On Fri, 10 Apr 2009, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Not as far as I can tell. It looks to me like the planner is assuming
>> that a forwards index scan and a reverse index scan will have the same
>> cost.
>
> Right, because they do. If you think otherwise, demonstrate it.

They do when the correlation of indexed value versus position in the table
is low, resulting in random access. However, when the correlation is near
1, then the index scan approximates to sequential access to disc. In that
case, scan direction would be important.

Of course, there's the separate issue that correlation isn't actually that
good a measure of the cost of an index scan, but I'm not sure what is
better, and feasible.

Matthew

--
Our riverbanks and seashores have a beauty all can share, provided
there's at least one boot, three treadless tyres, a half-eaten pork
pie, some oil drums, an old felt hat, a lorry-load of tar blocks,
and a broken bedstead there. -- Flanders and Swann