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(at)debis(dot)com (Jan Wieck) #