Re: [HACKERS] 6.5 beta and ORDER BY patch

Added to TODO:

* Have optimizer take LIMIT into account when considering index scans

> Hannu Krosing wrote:
>
> >
> > Jan Wieck wrote:
> > >
> > > >
> > > > Hi PostgreSQL hackers
> > > >
> > > > As we are again approaching the beta (feature freeze),
> > > > I will ask my ordinary question ;)
> > > >
> > > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > > was redundant included in 6.5 ?
> > >
> > > Sorry,
> > >
> > > I missed to put it into after v6.4 release. And since it
> > > wasn't there during v6.5 development, I would not put it in
> > > now.
> > >
> > > Note that it wasn't in the v6.4 feature patches either, so it
> > > isn't tested enough to get released.
> >
> > But if it is not relesed it will _never_ be tested enough ...
> >
> > As we are just going into beta, not relese, I would suggest to put
> > it in now, and back out if it relly breaks anything.
> >
> > I have been using it with 6.4 almost since the relese an have
> > seen no problems - in fact it solved a big problem and provided about
> > 1000X speedup for certain queries (a fraction of second instead of
> > 6 minutes) , not to mention avoiding backend crashes due to disk space
> > exhaustion.
> >
> > And it did not break anything in regression tests either, the only
> > argument then was that there is nothing in regression tests that
> > could possibly be broken by it ;)
> >
> > I greatly prefer it over my previous method of doing the same on the
> > client side (issuing an EXPLAIN, parsing it to see if it is SORT on
> > INDEX SCAN, and omitting the ORDER BY if it is)
> >
> > Also, not having it greatly diminishes the value of LIMIT.
>
> Ok ok ok - OK. You got me, I'll go ahead and put it in.
>
> >
> > I agree that it is a hack and only a partial solution and that in
> > ideal world the optimiser would also know about sort nodes.
>
> First the executor must know better how to handle LIMIT's
> OFFSET. For now it processes the query until OFFSET is
> reached, simply suppressing the in fact produced result
> tuples in the output. The it stops sending if the LIMIT count
> is reached. For joins or other complex things, it has no
> chance to do something different. But for an indexed single
> table scan, where ALL the qualifications are done on the
> index, it should handle the OFFSET by skipping index tuples
> only.
>
> Second the optimizer must take LIMIT into account and
> depending on the known number of tuples, LIMIT and OFFSET
> produce an index scan even if the query isn't qualified at
> all but has an ORDER BY clause matched by the index.
>
> These two features would finally solve your huge table
> problems.
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck(at)debis(dot)com (Jan Wieck) #
>
>
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(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