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

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Mauro Gatti <[email protected]>
To: [email protected]
Subject: Re: Planner join order regression from PG 15 to PG 16+: 70ms -> 1440ms (self-contained reproducer included)
Date: Thu, 5 Mar 2026 20:09:06 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>
References: <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>

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

Attachments:

  [text/plain] explain.txt (3.2K, ../[email protected]/2-explain.txt)
  download | inline:
 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

view thread (5+ messages)  latest in thread

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: Planner join order regression from PG 15 to PG 16+: 70ms -> 1440ms (self-contained reproducer included)
  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