Re: [HACKERS] Solution for LIMIT cost estimation

Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> writes:
> For my own curiousity, how does the presence of limit affect a plan
> anyway?

At the moment, it doesn't. But it should. To take an extreme example:

SELECT * FROM table WHERE x > 100 ORDER BY x LIMIT 1;

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