Re: index speed and failed expectations?

"Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> writes:
>> This query from the console:
>> select * from stats order by start_time;
>> takes 8 seconds before starting its output. Am I wrong in assuming that
>> the index on start_time should make ORDER BY orders of magnitude
>> faster?

> Postgresql won't use the index for queries like this.

"won't" -> "might not". It all depends on the relative cost estimates
for indexscan vs seqscan + sort. For a large table it's quite likely
that the latter will be cheaper, because it has a better-localized
access pattern.

> (What postgresql lacks is a first_row/all_rows hint like oracle)

That's spelled "LIMIT" ;-). Also, you can bias the choice in favor
of a fast-start plan if you use a cursor rather than a plain SELECT.
In that case the planner makes some allowance for the idea that
you might not want all the rows, or might be more interested in
getting the first ones quickly than minimizing the total time to
fetch all the rows.

regards, tom lane