From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrei Lepikhov <lepihov@gmail.com>
cc: Mauro Gatti <mauro.gt70@gmail.com>, pgsql-performance@lists.postgresql.org
Subject: Re: Planner join order regression from PG 15 to PG 16+: 70ms ->
 1440ms (self-contained reproducer included)
In-reply-to: <12c0b535-42f5-47c5-a8e7-45b4209e8ac9@gmail.com>
References: 
 <CAGWVhepg33bkfuDcPMy7GU5RQJss7GUd4jTNoB25wNT4X86zeA@mail.gmail.com>
 <12c0b535-42f5-47c5-a8e7-45b4209e8ac9@gmail.com>
Comments: In-reply-to Andrei Lepikhov <lepihov@gmail.com>
	message dated "Thu, 05 Mar 2026 20:09:06 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <14530.1772747078.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 05 Mar 2026 16:44:38 -0500
Message-ID: <14531.1772747078@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/14531.1772747078%40sss.pgh.pa.us>
Precedence: bulk

Andrei Lepikhov <lepihov@gmail.com> 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

=3D# create statistics on brand_id,line_code,model_year,model_code,version=
_code,pricelist_id from pricelist_options;
CREATE STATISTICS
=3D# analyze pricelist_options;
ANALYZE

That doesn't result in fully accurate estimates:

=3D# explain analyze select * from pricelist_options pl where ((pl.brand_i=
d =3D 10) AND ((pl.line_code)::text =3D 'ABC'::text) AND ((pl.model_year):=
:text =3D 'YR'::text) AND ((pl.model_code)::text =3D 'ABC'::text) AND ((pl=
.version_code)::text =3D 'VER-001'::text) AND (pl.pricelist_id =3D 100));
                                                                          =
                              QUERY PLAN                                  =
                                                                      =

--------------------------------------------------------------------------=
--------------------------------------------------------------------------=
----------------------------------------------------------------------
 Index Scan using ix_pricelist_options_1 on pricelist_options pl  (cost=3D=
0.43..117.12 rows=3D28 width=3D106) (actual time=3D0.015..0.037 rows=3D165=
.00 loops=3D1)
   Index Cond: ((brand_id =3D 10) AND ((line_code)::text =3D 'ABC'::text) =
AND ((model_year)::text =3D 'YR'::text) AND ((model_code)::text =3D 'ABC':=
:text) AND ((version_code)::text =3D 'VER-001'::text) AND (pricelist_id =3D=
 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