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@bitmead.com