Re: index speed and failed expectations?

Adam Rich wrote:
>> 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?
>> Or is this already fast enough? Or should I max up some memory (buffer)
>> setting to achieve greater speeds? Not that the speed is crucial, just
>> curious.
>>
>
> Postgresql won't use the index for queries like this. Due to the
> MVCC implementation, the index does not contain all necessary information
> and would therefore be slower than using the table data alone.
>
> (What postgresql lacks is a first_row/all_rows hint like oracle)
>
> However, if you limit the number of rows enough, you might force it
> to use an index:
>
> select * from stats order by start_time limit 1000;
>

Thanks! Since LIMIT/OFFSET is the typical usage pattern for a paginated
data set accessed from the Web (which is my case), it immediately
becomes a non-issue.