Re: Planner join order regression from PG 15 to PG 16+: 70ms -> 1440ms (self-contained reproducer included)

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