Re: about 7.0 LIMIT optimization

> Did you read by chance my previous message intitled 
> "Generalized Top Queries on PostgreSQL"?

I vaguely recall it, but forget the details...

> - You cannot select the top N rows according to criterion A ordering
>   the results with a different criterion B.

True, but I don't see how to do that with one indexscan (for that
matter, I don't even see how to express it in the SQL subset that
we support...)

> - If you ask for the best 10 rows, from a relation including 
>   100000 rows, you have to do a traditional sort on 100000 rows and
>   then retain only the first 10, doing more comparisons than requested.

Not if there's an index that implements the ordering --- and if there
is not, I don't see how to avoid the sort anyway.

> - You can choose a "fast-start" plan (i.e., basically, 
>   a pipelined plan), but you cannot performe an "early-stop" of 
>   the stream when you have a "slow-start" plan  (e.g. involving sorts 
>   or hash tables).

Why not?  The executor *will* stop when it has as many output rows as
the LIMIT demands.

> We noticed that this kind of plan often outperforms the first one.

I'd be the first to admit that the cost model needs some fine-tuning
still.  It's just a conceptual structure at this point.

> Actually, we should say we can't figure out the reason for
> managing the LIMIT clause in a so complicated way, not providing 
> a node in the plan as any other operator. 

We will probably end up doing it like that sooner or later, in order to
allow attaching LIMIT to sub-selects.  I don't take any credit or blame
for the execution-time implementation of LIMIT; I just worked with what
I found...
        regards, tom lane