Re: [HACKERS] Solution for LIMIT cost estimation

Chris wrote:
> 
> Tom Lane wrote:
> >
> >         SELECT * FROM table WHERE x > 100 ORDER BY x LIMIT 1;
> 
> Could it _ever_ be faster to sort the tuples when there is already an
> index that can provide them in sorted order?

This has been discussed on this list several times, and it appears that
select+sort is quite often faster than index scan, mainly due to the fact 
that tables live on disk and disk accesses are expensive, and when doing 
index scans:

1- you have to scan two files (index and data), when they are on the same   disk it is much more 2 times slower than
sacnninga single file even  when doing it sequentially
 

2- scans on the both files are random access, so seek and latency times   come into play and readahead is useless

3- you often read the same data page many times

-------------
Hannu