public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: Mauro Gatti <[email protected]>
Cc: [email protected]
Subject: Re: Planner join order regression from PG 15 to PG 16+: 70ms -> 1440ms (self-contained reproducer included)
Date: Thu, 05 Mar 2026 16:44:38 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>
<[email protected]>
Andrei Lepikhov <[email protected]> writes:
> On 5/3/26 17:25, Mauro Gatti wrote:
>> ## Questions for the community
> Thanks for stable reproduction!
Yes, we appreciate that much effort being put into trouble reports.
Makes it a lot easier to see what's going wrong.
> 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.
Right. Given the very-far-off rowcount estimates for some of the
index scans, it'd be surprising if the planner arrived at a good
join order. It's a "garbage in, garbage out" situation. As Andrei
suggested, you can often improve bad rowcount estimates by creating
custom statistics.
I found it was sufficient to do
=# create statistics on brand_id,line_code,model_year,model_code,version_code,pricelist_id from pricelist_options;
CREATE STATISTICS
=# analyze pricelist_options;
ANALYZE
That doesn't result in fully accurate estimates:
=# explain analyze select * from pricelist_options pl where ((pl.brand_id = 10) AND ((pl.line_code)::text = 'ABC'::text) AND ((pl.model_year)::text = 'YR'::text) AND ((pl.model_code)::text = 'ABC'::text) AND ((pl.version_code)::text = 'VER-001'::text) AND (pl.pricelist_id = 100));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_pricelist_options_1 on pricelist_options pl (cost=0.43..117.12 rows=28 width=106) (actual time=0.015..0.037 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))
but "28 rows" is a lot closer to 165 than "1 row", and it's enough
to push the planner to choose the plan you want.
I do concur with Andrei's recommendation to create stats matching
your other multicolumn indexes, though.
regards, tom lane
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], [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