Chris <chris(at)bitmead(dot)com> writes:
> Could it _ever_ be faster to sort the tuples when there is already an
> index that can provide them in sorted order?
Yes --- in fact usually, for large tables. Once your table gets too
big for the disk cache to be effective, indexscan performance approaches
one random-access page fetch per tuple. Sort performance behaves more
or less as p*log(p) accesses for p pages; and a far larger proportion
of those accesses are sequential than in the indexscan case. So the
sort will win if you have more than log(p) tuples per page. Do the
arithmetic...
The optimizer's job would be far simpler if no-brainer rules like
"indexscan is always better" worked.
regards, tom lane