public inbox for [email protected]  

help / color / mirror / Atom feed

From: negora <[email protected]>
To: Tom Lane <[email protected]>
Cc: PostgreSQL - General <[email protected]>
Subject: Re: Why does it sort rows after a nested loop that uses already-sorted indexes?
Date: Fri, 19 Apr 2024 10:11:17 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

 > 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 <[email protected]> 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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Why does it sort rows after a nested loop that uses already-sorted indexes?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox