Re: [HACKERS] 6.5 beta and ORDER BY patch - Mailing list pgsql-hackers

>
>
> Jan, is this implemented in 6.5 beta?

    It is still the simple suppressing of the sort if the choosen
    index scan has already the requested sort order. The possible
    enhancements  of  the  optimizer  (mainly  taking  LIMIT into
    account and use index scan if sort order can be obtained from
    that) aren't implemented AFAIK.

    I  have too less knowledge in the planner/optimizer corner to
    get my hands on it at this stage! And there are  things  left
    in  the  rewrite system. It might be better to leave this all
    for v6.6.


Jan

>
> > > > > 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.
> >
>
>
> --
>   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, Pennsylvania 19026
>


--

#======================================================================#
# 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) #