Message-ID: <4c474300-2662-4b4e-bad1-ca9d36324a2c@negora.com>
Date: Fri, 19 Apr 2024 10:11:17 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Why does it sort rows after a nested loop that uses
 already-sorted indexes?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL - General <pgsql-general@lists.postgresql.org>
References: <07b89cad-b2a1-48c2-8a14-67d1b93098c9@negora.com>
 <1825378.1713452017@sss.pgh.pa.us>
Content-Language: en-US, es-ES, en-GB
From: negora <public@negora.com>
In-Reply-To: <1825378.1713452017@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/4c474300-2662-4b4e-bad1-ca9d36324a2c%40negora.com>
Precedence: bulk

 > 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