"Adam Rich" <adam.r@xxxxxxxxxxxxx> 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
