On Thu, Sep 13, 2001 at 01:03:54PM +0100, Thurstan R. McDougle wrote:
> It would depend on how efficient the ORDER BY and LIMIT work together.
> (The ORDER BY could build a list of LIMIT n items and just replace items
> in that list...a lot more efficient both of memory and comparisons than
> building the full list and then keeping the top n)
There is some of this already. In the output of EXPLAIN you see two numbers.
The first is the estimated time toget the first tuple, the second is to get
all the tuples.
When LIMIT is applied, the estimated total cost is adjusted based on the
number of rows. So with a small number of tuples the planner will favour
plans that get tuples early even if the total cost would be larger.
> HACKERS: If it does not do this it might be a usefull optimisation.
> There would probably need to be a cutoff limit on whether to apply this
> method or sort and keep n. Also for LIMIT plus OFFSET it would need to
> build a list of the the total of the LIMIT and OFFSET figures.
The problem is that it sometimes doesn't help as much as you'd expect. If
you see a Sort stage in the plan, that means that everything below that has
to be completly calculated.
The only solution is to use a sorted index to avoid the sort step, if
possible.
HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.