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@debis.com (Jan Wieck) #
> 
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026