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 sacnning a 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