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.
