Re: LIMIT Optimization

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I am confused. I thought we already did optimization for LIMIT that
> > assumed you only wanted a few values. Is there something we are missing
> > there?
>
> Yeah, he was proposing an alternative implementation of sorting that
> would win in a scenario like
>
> SELECT ... ORDER BY foo LIMIT <something small>
>
> If you have an index on foo then there's no problem, but if you're
> forced to do an explicit sort then the system does a complete sort
> before you can get any data out. If the limit is small enough you
> can instead do a one-pass "select top N" scan.
>
> Note that this is only workable in the non-cursor case, where you
> know the limit for sure.

Oh, boy, so we would scan through and grab the top X value from the
table without a sort. Interesting. Add to TODO:

Allow ORDER BY ... LIMIT to select top values without sort or index

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026