Hi there,

I've just had a look at the 7.0beta and I've seen your enhancements 
about LIMIT optimization.
Did you read by chance my previous message intitled 
"Generalized Top Queries on PostgreSQL"?
When I wrote it I hadn't read the thread 
intitled "Solution for LIMIT cost estimation" yet.

What you did looks pretty similar to part of our extension
(cost model and pruning rules). The main differences are:

- the FOR EACH generalization.

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

- 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.

- 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). We noticed that this kind of plan often 
  outperforms the first one.

So, we are looking forward to see how the new LIMIT optimization works
(we will do some tests as soon as possible). Have you noticed
relevant improvements? 

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. 
In our opinion, the choice to provide a separated process of the 
LIMIT clause has two problems:
1. We find it more complicated and not so natural.
2. It is an obstacle to some optimizations and to some functionalities
   (how to use it in subselects or views?)

Best regards

R. Cornacchia (cornacch@cs.unibo.it) Computer Science, University of
Bologna

A. Ghidini (ghidini@cs.unibo.it) Computer Science, University of Bologna 

Dr. Paolo Ciaccia (ciaccia@cs.unibo.it) DEIS CSITE-CNR, University of
Bologna

===========================================================

VIRGILIO MAIL - Il tuo indirizzo E-mail gratis e per sempre
http://mail.virgilio.it/


VIRGILIO - La guida italiana a Internet
http://www.virgilio.it/

Wed, 23 Feb 2000 00:40:43 -0500
"Roberto Cornacchia" <rob.c@virgilio.it>
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

Wed, 23 Feb 2000 01:03:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: about 7.0 LIMIT optimization 