Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> but (at least on my machine) the explicit sort is marginally faster.
>> Evidently, the cost estimate for an explicit sort is *way* too high.
> But it shouldn't be using the ORDER BY,
Right, if the cost estimates were in line with reality it would be
choosing the explicit sort.
> ... except when the number of rows
> processed is less than the full table, right?
Now if there were *also* a LIMIT clause then the tradeoffs change again
--- the index scan wins for a small LIMIT because of its much lower
startup cost. But the optimizer knows nothing of this and will still
estimate on the basis that all of the tuples are going to be processed.
As Hiroshi just remarked, we really need to teach the optimizer about
LIMIT. Another thing I'm hoping to get done before 7.0.
regards, tom lane