Re: [HACKERS] What about LIMIT in SELECT ?

Hiroshi Inoue wrote:

> >   * Prevent psort() usage when query already using index matching ORDER BY
> >
> >
>
> I can't find the reference to descending order cases except my posting.
> If  we use an index scan to remove sorts in those cases,backward positioning
> and scanning are necessary.

    I  think  it's  only thought as a reminder that the optimizer
    needs some optimization.

    That topic, and the LIMIT stuff too I think, is past 6.4 work
    and  may  go into a 6.4.1 performance release. So when we are
    after 6.4, we have enough time to work out a  real  solution,
    instead of just throwing in a patch as a quick shot.

    What  we  two did where steps in the same direction. Your one
    covers more situations, but after all if multiple people have
    the  same  idea  there  is a good chance that it is the right
    thing to do.

>
> Let t be a table with 2 indices, index1(key1,key2), index2(key1,key3).
> i.e. key1 is common to index1 and index2.
>
> And for the query
>   select * from t where key1>....;
>
> If   PosgreSQL optimizer choose [ index scan on index1 ] we can't remove
> sorts from the following query.
>    select * from t where key1>... order by key1,key3;
>
> Similarly if  [ index scan on index2 ] are chosen  we can't remove sorts
> from the following query.
>    select * from t where key1>... order by key1,key2;
>
> But in both cases (clever) optimizer can choose another index for scan.

    Right. As I remember, your solution does basically  the  same
    as  my  one. It does not change the optimizers decision about
    the index or if an index at all is used.  So  I  assume  they
    hook  into  the same position where depending on the order by
    clause the sort node is added. And that is at the very end of
    the optimizer.

    What  you  describe above requires changes in upper levels of
    optimization.  Doing that is far away from my knowledge about
    the  optimizer.   And  some of your earlier statements let me
    think you aren't familiar enough with  it  too.  We  need  at
    least help from others to do it well.

    I  don't want to dive that deep into the optimizer. There was
    a far too long time where the rule system was broken and  got
    out  of  sync with the parser/optimizer capabilities. I fixed
    many things in it for 6.4. My first priority now is,  not  to
    let such a situation come up again.


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