Re: Why does it sort rows after a nested loop that uses already-sorted indexes?

> That's a level of analysis that it doesn't do...

Great. I suspected that, but I needed a confirmation from a reliable
source. Thank you!

> ...and TBH I'm not even
> entirely sure it's correct to assume that the output is sorted like
> that. At minimum you'd need an additional assumption that the
> outer side's join key is unique, which is a factor that we don't
> currently track when reasoning about ordering.

Ouch! I hadn't thought about that possibility! When I tried to mentally
reproduce the nested loop, I always considered the values of the outer
loop to be unique. I guess that was because, very often, I used unique
indexes for my tests... But it doesn't have to be so, of course.

Best regards.

On 18/04/2024 16:53, Tom Lane wrote:
> negora <public(at)negora(dot)com> writes:
>> As you can see, the planner does detect that the outer loop returns the
>> rows presorted by [sales_order.id]. However, it's unable to detect that
>> the rows returned by the inner loop are also sorted by [sales_order.id]
>> first, and then by [order_line.id].
>
> That's a level of analysis that it doesn't do, and TBH I'm not even
> entirely sure it's correct to assume that the output is sorted like
> that. At minimum you'd need an additional assumption that the
> outer side's join key is unique, which is a factor that we don't
> currently track when reasoning about ordering.
>
> regards, tom lane