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@negora.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