Content-Type: multipart/mixed; boundary="------------5dqdi8p14ttt7c0amcVxikPs"
Message-ID: <12c0b535-42f5-47c5-a8e7-45b4209e8ac9@gmail.com>
Date: Thu, 5 Mar 2026 20:09:06 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Planner join order regression from PG 15 to PG 16+: 70ms ->
 1440ms (self-contained reproducer included)
To: Mauro Gatti <mauro.gt70@gmail.com>, pgsql-performance@lists.postgresql.org
References: 
 <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/12c0b535-42f5-47c5-a8e7-45b4209e8ac9%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------5dqdi8p14ttt7c0amcVxikPs
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 5/3/26 17:25, Mauro Gatti wrote:
> ## Questions for the community

Thanks for stable reproduction!

> 
> 1. Was there a specific commit in the PG 16 cycle that changed how
>     the planner evaluates join orderings for LEFT JOINs, possibly
>     related to the Right Anti Join work or outer join commutation?

Your case is typical for 'never executed' nodes. As you can see, the 
costs of your query plans are very close, and the estimation error is 
large due to multiple clauses in your filter. As I see, for the planner, 
there is no difference in which version of the plan to choose - it is 
just a game of chance.
There were lots of commits - each of them might trigger this slight change.

On PG18, I see an even more optimal query plan than on 16 (see explain.txt).
The main problem with your query is the use of multi-clause expressions. 
And fix is typical - create extension statistics like the following:

CREATE STATISTICS option_rules_ext
ON brand_id,line_code,model_year,model_code,version_code
FROM option_rules;

CREATE STATISTICS product_options_ext
ON brand_id,line_code,model_year,model_code,version_code
FROM product_options;

CREATE STATISTICS pricelist_options_ext
ON brand_id,line_code,model_year,model_code,version_code,pricelist_id
FROM pricelist_options;

This solution is not ideal, but no one database system is fully ready 
for multi-clause expressions yet.

-- 
regards, Andrei Lepikhov,
pgEdge
--------------5dqdi8p14ttt7c0amcVxikPs
Content-Type: text/plain; charset=UTF-8; name="explain.txt"
Content-Disposition: attachment; filename="explain.txt"
Content-Transfer-Encoding: base64

IE5lc3RlZCBMb29wIExlZnQgSm9pbiAgKGNvc3Q9Ni4xMy4uNTUwLjY4IHJvd3M9MTIgd2lk
dGg9NjIxKSAoYWN0dWFsIHJvd3M9MC4wMCBsb29wcz0xKQogICAtPiAgSGFzaCBMZWZ0IEpv
aW4gIChjb3N0PTUuNjkuLjQ0OC45MiByb3dzPTEyIHdpZHRoPTUxNSkgKGFjdHVhbCByb3dz
PTAuMDAgbG9vcHM9MSkKICAgICAgICAgSGFzaCBDb25kOiAocGwudGF4X2lkID0gdGMudGF4
X2lkKQogICAgICAgICAtPiAgTmVzdGVkIExvb3AgTGVmdCBKb2luICAoY29zdD0xLjcyLi40
NDQuOTEgcm93cz0xMiB3aWR0aD00MDcpIChhY3R1YWwgcm93cz0wLjAwIGxvb3BzPTEpCiAg
ICAgICAgICAgICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC44Ni4uMjQxLjY2IHJvd3M9
MTIgd2lkdGg9MjA5KSAoYWN0dWFsIHJvd3M9MC4wMCBsb29wcz0xKQogICAgICAgICAgICAg
ICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyBpeF9wcmljZWxpc3Rfb3B0aW9uc18xIG9u
IHByaWNlbGlzdF9vcHRpb25zIHBsICAoY29zdD0wLjQzLi44MC45MCByb3dzPTE5IHdpZHRo
PTEwNikgKGFjdHVhbCByb3dzPTE2NS4wMCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAg
ICAgICAgICBJbmRleCBDb25kOiAoKGJyYW5kX2lkID0gMTApIEFORCAoKGxpbmVfY29kZSk6
OnRleHQgPSAnQUJDJzo6dGV4dCkgQU5EICgobW9kZWxfeWVhcik6OnRleHQgPSAnWVInOjp0
ZXh0KSBBTkQgKChtb2RlbF9jb2RlKTo6dGV4dCA9ICdBQkMnOjp0ZXh0KSBBTkQgKCh2ZXJz
aW9uX2NvZGUpOjp0ZXh0ID0gJ1ZFUi0wMDEnOjp0ZXh0KSBBTkQgKHByaWNlbGlzdF9pZCA9
IDEwMCkpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IFNlYXJjaGVzOiAxCiAg
ICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBrX3Byb2R1Y3Rfb3B0
aW9ucyBvbiBwcm9kdWN0X29wdGlvbnMgcG8gIChjb3N0PTAuNDMuLjguNDYgcm93cz0xIHdp
ZHRoPTEwMykgKGFjdHVhbCByb3dzPTAuMDAgbG9vcHM9MTY1KQogICAgICAgICAgICAgICAg
ICAgICAgICAgICBJbmRleCBDb25kOiAoKGJyYW5kX2lkID0gMTApIEFORCAoKGxpbmVfY29k
ZSk6OnRleHQgPSAnQUJDJzo6dGV4dCkgQU5EICgobW9kZWxfeWVhcik6OnRleHQgPSAnWVIn
Ojp0ZXh0KSBBTkQgKChtb2RlbF9jb2RlKTo6dGV4dCA9ICdBQkMnOjp0ZXh0KSBBTkQgKCh2
ZXJzaW9uX2NvZGUpOjp0ZXh0ID0gJ1ZFUi0wMDEnOjp0ZXh0KSBBTkQgKChvcHRpb25fdHlw
ZSk6OnRleHQgPSAocGwub3B0aW9uX3R5cGUpOjp0ZXh0KSBBTkQgKChvcHRpb25fY29kZSk6
OnRleHQgPSAocGwub3B0aW9uX2NvZGUpOjp0ZXh0KSkKICAgICAgICAgICAgICAgICAgICAg
ICAgICAgSW5kZXggU2VhcmNoZXM6IDE2NQogICAgICAgICAgICAgICAtPiAgTmVzdGVkIExv
b3AgTGVmdCBKb2luICAoY29zdD0wLjg1Li4xNi45MyByb3dzPTEgd2lkdGg9MTk4KSAobmV2
ZXIgZXhlY3V0ZWQpCiAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5n
IHBrX29wdGlvbl9ydWxlcyBvbiBvcHRpb25fcnVsZXMgcnVsZXMgIChjb3N0PTAuNDIuLjgu
NDYgcm93cz0xIHdpZHRoPTk1KSAobmV2ZXIgZXhlY3V0ZWQpCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgIEluZGV4IENvbmQ6ICgoYnJhbmRfaWQgPSAxMCkgQU5EICgobGluZV9jb2Rl
KTo6dGV4dCA9ICdBQkMnOjp0ZXh0KSBBTkQgKChtb2RlbF95ZWFyKTo6dGV4dCA9ICdZUic6
OnRleHQpIEFORCAoKG1vZGVsX2NvZGUpOjp0ZXh0ID0gJ0FCQyc6OnRleHQpIEFORCAoKHZl
cnNpb25fY29kZSk6OnRleHQgPSAnVkVSLTAwMSc6OnRleHQpIEFORCAoKG9wdGlvbl90eXBl
KTo6dGV4dCA9IChwbC5vcHRpb25fdHlwZSk6OnRleHQpIEFORCAoKG9wdGlvbl9jb2RlKTo6
dGV4dCA9IChwbC5vcHRpb25fY29kZSk6OnRleHQpKQogICAgICAgICAgICAgICAgICAgICAg
ICAgICBJbmRleCBTZWFyY2hlczogMAogICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXgg
U2NhbiB1c2luZyBwa19wcm9kdWN0X29wdGlvbnMgb24gcHJvZHVjdF9vcHRpb25zIHBvMiAg
KGNvc3Q9MC40My4uOC40NiByb3dzPTEgd2lkdGg9MTAzKSAobmV2ZXIgZXhlY3V0ZWQpCiAg
ICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgoYnJhbmRfaWQgPSAxMCkg
QU5EICgobGluZV9jb2RlKTo6dGV4dCA9ICdBQkMnOjp0ZXh0KSBBTkQgKChtb2RlbF95ZWFy
KTo6dGV4dCA9ICdZUic6OnRleHQpIEFORCAoKG1vZGVsX2NvZGUpOjp0ZXh0ID0gJ0FCQyc6
OnRleHQpIEFORCAoKHZlcnNpb25fY29kZSk6OnRleHQgPSAnVkVSLTAwMSc6OnRleHQpIEFO
RCAoKG9wdGlvbl90eXBlKTo6dGV4dCA9IChydWxlcy5zZWNfb3B0aW9uX3R5cGUpOjp0ZXh0
KSBBTkQgKChvcHRpb25fY29kZSk6OnRleHQgPSAocnVsZXMuc2VjX29wdGlvbl9jb2RlKTo6
dGV4dCkpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IFNlYXJjaGVzOiAwCiAg
ICAgICAgIC0+ICBIYXNoICAoY29zdD0yLjg4Li4yLjg4IHJvd3M9ODggd2lkdGg9MTA4KSAo
bmV2ZXIgZXhlY3V0ZWQpCiAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiB0YXhfY29k
ZXMgdGMgIChjb3N0PTAuMDAuLjIuODggcm93cz04OCB3aWR0aD0xMDgpIChuZXZlciBleGVj
dXRlZCkKICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgaXhfcHJpY2VsaXN0X29wdGlvbnNfMSBv
biBwcmljZWxpc3Rfb3B0aW9ucyBwbF9jaGVjayAgKGNvc3Q9MC40My4uOC40NyByb3dzPTEg
d2lkdGg9MTA2KSAobmV2ZXIgZXhlY3V0ZWQpCiAgICAgICAgIEluZGV4IENvbmQ6ICgoYnJh
bmRfaWQgPSBydWxlcy5icmFuZF9pZCkgQU5EICgobGluZV9jb2RlKTo6dGV4dCA9IChydWxl
cy5saW5lX2NvZGUpOjp0ZXh0KSBBTkQgKChtb2RlbF95ZWFyKTo6dGV4dCA9IChydWxlcy5t
b2RlbF95ZWFyKTo6dGV4dCkgQU5EICgobW9kZWxfY29kZSk6OnRleHQgPSAocnVsZXMubW9k
ZWxfY29kZSk6OnRleHQpIEFORCAoKHZlcnNpb25fY29kZSk6OnRleHQgPSAocnVsZXMudmVy
c2lvbl9jb2RlKTo6dGV4dCkgQU5EIChwcmljZWxpc3RfaWQgPSAxMDApKQogICAgICAgICBG
aWx0ZXI6ICgoKG9wdGlvbl90eXBlKTo6dGV4dCA9IChydWxlcy5zZWNfb3B0aW9uX3R5cGUp
Ojp0ZXh0KSBBTkQgKChvcHRpb25fY29kZSk6OnRleHQgPSAocnVsZXMuc2VjX29wdGlvbl9j
b2RlKTo6dGV4dCkpCiAgICAgICAgIEluZGV4IFNlYXJjaGVzOiAwCiBQbGFubmluZyBUaW1l
OiA1LjQ1NiBtcwogRXhlY3V0aW9uIFRpbWU6IDIuMjcyIG1z

--------------5dqdi8p14ttt7c0amcVxikPs--