Re: [HACKERS] Solution for LIMIT cost estimation

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?

>
> to get the tuple with lowest x > 100. Assuming that there is an index
> on x, the right way to implement this is with an indexscan, because a
> single probe into the index will pull out the tuple you want. But right
> now the optimizer will choose a plan as if the LIMIT weren't there,
> ie on the basis of estimated total cost to retrieve the whole ordered
> result set. On that basis it might well choose sequential scan + sort,
> so you'd have to wait around for a sort to complete before you get your
> answer.
>
> regards, tom lane

--
Chris Bitmead
mailto:chris(at)bitmead(dot)com