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
Nested Loop Left Join (cost=6.13..550.68 rows=12 width=621) (actual rows=0.00
loops=1)
-> Hash Left Join (cost=5.69..448.92 rows=12 width=515) (actual rows=0.00
loops=1)
Hash Cond: (pl.tax_id = tc.tax_id)
-> Nested Loop Left Join (cost=1.72..444.91 rows=12 width=407)
(actual rows=0.00 loops=1)
-> Nested Loop (cost=0.86..241.66 rows=12 width=209) (actual
rows=0.00 loops=1)
-> Index Scan using ix_pricelist_options_1 on
pricelist_options pl (cost=0.43..80.90 rows=19 width=106) (actual rows=165.00
loops=1)
Index Cond: ((brand_id = 10) AND ((line_code)::text
= 'ABC'::text) AND ((model_year)::text = 'YR'::text) AND ((model_code)::text =
'ABC'::text) AND ((version_code)::text = 'VER-001'::text) AND (pricelist_id =
100))
Index Searches: 1
-> Index Scan using pk_product_options on product_options
po (cost=0.43..8.46 rows=1 width=103) (actual rows=0.00 loops=165)
Index Cond: ((brand_id = 10) AND ((line_code)::text
= 'ABC'::text) AND ((model_year)::text = 'YR'::text) AND ((model_code)::text =
'ABC'::text) AND ((version_code)::text = 'VER-001'::text) AND
((option_type)::text = (pl.option_type)::text) AND ((option_code)::text =
(pl.option_code)::text))
Index Searches: 165
-> Nested Loop Left Join (cost=0.85..16.93 rows=1 width=198)
(never executed)
-> Index Scan using pk_option_rules on option_rules rules
(cost=0.42..8.46 rows=1 width=95) (never executed)
Index Cond: ((brand_id = 10) AND ((line_code)::text
= 'ABC'::text) AND ((model_year)::text = 'YR'::text) AND ((model_code)::text =
'ABC'::text) AND ((version_code)::text = 'VER-001'::text) AND
((option_type)::text = (pl.option_type)::text) AND ((option_code)::text =
(pl.option_code)::text))
Index Searches: 0
-> Index Scan using pk_product_options on product_options
po2 (cost=0.43..8.46 rows=1 width=103) (never executed)
Index Cond: ((brand_id = 10) AND ((line_code)::text
= 'ABC'::text) AND ((model_year)::text = 'YR'::text) AND ((model_code)::text =
'ABC'::text) AND ((version_code)::text = 'VER-001'::text) AND
((option_type)::text = (rules.sec_option_type)::text) AND ((option_code)::text
= (rules.sec_option_code)::text))
Index Searches: 0
-> Hash (cost=2.88..2.88 rows=88 width=108) (never executed)
-> Seq Scan on tax_codes tc (cost=0.00..2.88 rows=88
width=108) (never executed)
-> Index Scan using ix_pricelist_options_1 on pricelist_options pl_check
(cost=0.43..8.47 rows=1 width=106) (never executed)
Index Cond: ((brand_id = rules.brand_id) AND ((line_code)::text =
(rules.line_code)::text) AND ((model_year)::text = (rules.model_year)::text)
AND ((model_code)::text = (rules.model_code)::text) AND ((version_code)::text =
(rules.version_code)::text) AND (pricelist_id = 100))
Filter: (((option_type)::text = (rules.sec_option_type)::text) AND
((option_code)::text = (rules.sec_option_code)::text))
Index Searches: 0
Planning Time: 5.456 ms
Execution Time: 2.272 ms
