> 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@xxxxxxxxxx> 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
