Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vyQTu-0006VE-2l for pgsql-performance@arkaria.postgresql.org; Fri, 06 Mar 2026 08:24:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vyQTt-003iyQ-0S for pgsql-performance@arkaria.postgresql.org; Fri, 06 Mar 2026 08:24:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vyQTs-003iyH-1V for pgsql-performance@lists.postgresql.org; Fri, 06 Mar 2026 08:24:21 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vyQTq-00000000k7M-2ZuZ for pgsql-performance@lists.postgresql.org; Fri, 06 Mar 2026 08:24:19 +0000 Received: by mail-ed1-x52f.google.com with SMTP id 4fb4d7f45d1cf-660be63279bso5013388a12.1 for ; Fri, 06 Mar 2026 00:24:18 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772785456; cv=none; d=google.com; s=arc-20240605; b=P/n+tIqgud/JV4sPv+cOK5wmuXzxA1wsR1DuAXfTh4hxP18yknflHATDOwpMZl6ye7 hoVk02p+cuYORE+q4uYIyVpPt2HUETDq6+zvTMf8KAJOp3wk2Tkkwr6RsgLmyvk9XohY +b6UWvwfkaJnbHsRV4LKefqvbVT0cxAQnmH9k+B6IHOoXV+mePWIDEn2f2pEEbGBj06o HP04mwoskXt4A30lhlUuyacAyUadUILwziGfdClW8lWD0aNQvrvLqrQJPmd2umjHt9wn cjVie7vt7v+6nmaFMSg6+DLN/LilJeclcNxkYlaA4RuRrZmJafAioPW9G+XOe4gRw09B OwQg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=Zmw8hGNP/KIbgFrpVg33/rU5qdGITyZvPMDJ3vN+1/Q=; fh=fg18SaSUGwy49A9+94fmZIwBrK9/BBpioYfU8TzxvBk=; b=fxngLdFYbrUVqok2IyEdqlUSiJi3C5C3ai676Qix15+3O52o1TpsyjPGhOjVBeQTPj qXMK1fwIWNyBBdhjFox+2nZ9y4+18mMov5Ht4yAfanRpyqORZ4PIKweaJIrDUC4yVoVy mCWrrNZ8MOKRHnZ+CuwI8ofDfKKNY6pIFoV1PKsrPF71aSoDIKNZfZEkaSYB99EcmMsl VRf/Q/Kr4p/pvV03grbCs+G7e3g3o4Vb1MJX2GlLIPwepsox6F+hTWJ1rFcLJitA6orR hFYDLY8mfelqnFpFcpf2ItLkjgQW2EZGq51PWKrvM1Ty4jMGyMYGjapDPaeUcYUMpsTx cEnA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772785456; x=1773390256; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Zmw8hGNP/KIbgFrpVg33/rU5qdGITyZvPMDJ3vN+1/Q=; b=drLSrSjKTnWB5eA0rF/fTwDShZnFQ0taCWqBT53ZmtzwaqMJr9yvVeyV10Q8Q4wnaY /vMTTVvkmGKcRM43y5RpPcX9HdgLRIhN2eVUZIV+1Jph+WGqZR0UgdEuCFlwQRqZ3jtt 0izcPhxv6sLBK1fDX7vcBcrYEefiLVnQVb7DKoNX75zZYCt7V9s9rJS3c36xpQPs3+Kq 0WDr0CPJDOoDD6tEnhO+OiBtvqLXfoQJUHgZt7kjLTohj3im13D6+cgxuOVLl4e0CvbB 5a1n4SywSlnWL1jcjiJnImW2PdUCxPUOzrJTCwfwINT7wYbw/+i3vT/XaG7hXJt1NqXO uMtg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772785456; x=1773390256; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=Zmw8hGNP/KIbgFrpVg33/rU5qdGITyZvPMDJ3vN+1/Q=; b=r/7ubB6y/nT9TQtN9xVihkT1B8ZkYDzCFgXq2iAyfJlLOWmvJVEyPaAnw3+zzhWkhn FJYgD/Ug2aX4HUFUHjcK8BBikZmSnaFVedk8zg/Lv0pQOJaFdyd2IzST5ENIrcr6dcex 2YQTWWRuPyThg/NV71nLrzt5shjUFYJCOiVb3KRVh0K2vt4dpanIv79yuo18EnvS2Soq +8ClnqEreh4Rdwsc2vdH/H26pTtNGSLTs57Vn3KMswIdx6z3Y7SueVm6wpGDZbTffIr+ aNPwohuw9Woi9d5zHpZx9tdTDUr1IfrqU+i33pPmtOyZ8B6ydHDRhrZadIn7WBRrjk3d V85A== X-Forwarded-Encrypted: i=1; AJvYcCUQQi/pPV9ovRX9uPPfNKYdrTnHfHrMAsKbKubP1qB3Skf+GZf/JdwF+iLoZNpHyxElOfhSlkjm0Vb8s/mB1XD2bA==@lists.postgresql.org X-Gm-Message-State: AOJu0YwgsFiCWupPUP9rhVG9kLosoMgACgNuTJSstMKR9LIz66Epydeg ed1D+CKuy82IiX8EHilF6h/YvbrLRhPaw2AnK+oQ9Ca0nLAN9taFKe8W3C6iXNIxBFeOEXzUrA2 zPTIGdmQGGFrfjumJkKybJvAmp3qmJB8= X-Gm-Gg: ATEYQzyHoUV2cjpn5tjhubV6xYMWCcOXxvFLlfik2SFHK/R/hW2DLmlvXezA3oEPa94 vdiIlHkQE5eTuE+p8F4p2TXHsoItaf3OmD8qfwTBFnd3kh0kk0qIAiUFU32fexHtanC/Wopa4Ec di11Z/YtbkbVelf4Vg3pcaHvzh+B0D1LtDOSK082W1r8Afi6Umnzuce4hpsgm97pocMOvPrNOO+ EHtCwWeQlrof0zVqhPIhfn1j4uLiNFZVce+e9HGeiEpu6vEsYfZetxRegtMOxLPNx+Sf3TURIh3 U8JAmWRC X-Received: by 2002:a17:907:3e90:b0:b8e:a126:66be with SMTP id a640c23a62f3a-b942d15186amr87756266b.18.1772785455574; Fri, 06 Mar 2026 00:24:15 -0800 (PST) MIME-Version: 1.0 References: <12c0b535-42f5-47c5-a8e7-45b4209e8ac9@gmail.com> <14531.1772747078@sss.pgh.pa.us> In-Reply-To: <14531.1772747078@sss.pgh.pa.us> From: Mauro Gatti Date: Fri, 6 Mar 2026 09:24:03 +0100 X-Gm-Features: AaiRm50gL6Tah0mU-_ErO2ka0ru4sa3MOdAZK1vWoP80cRUBuTx5vM4yYtfjb_E Message-ID: Subject: Re: Planner join order regression from PG 15 to PG 16+: 70ms -> 1440ms (self-contained reproducer included) To: Tom Lane Cc: Andrei Lepikhov , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000057b2b2064c56c7ab" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000057b2b2064c56c7ab Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Andrei, Tom, thank you for the explanation. I had already tried using extended statistics but without success; I probably did something wrong. After creating statistics on all the tables, I=E2=80=99m now getting optima= l timings in production. This is not the ideal solution because this issue will delay the PostgreSQL version upgrade: our application will also need to be tested with respect to response times, and we will need to create dedicated statistics depending on the specific cases. In addition, there may be cases that slip through testing and then surface in production due to a different data distribution. Based on your explanation, my take is that the planner is not able to produce adequate estimates when there are filters on many columns; when there are millions of rows involved this become noticeable and extended statistics may be needed. One or more commits in PG16 changed the context sligthly and caused the unfortunate path to be chosen. In any case, I don=E2=80=99t think there are other workable solutions witho= ut changing the code. I get good results using CTEs or LATERAL with OFFSET 0, but the point was to find a solution without having to change the code. Thanks again, regards. Mauro Gatti Il giorno gio 5 mar 2026 alle ore 22:44 Tom Lane ha scritto: > Andrei Lepikhov 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_= id > =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 10= 0)); > > QUERY PLAN > > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------- > Index Scan using ix_pricelist_options_1 on pricelist_options pl > (cost=3D0.43..117.12 rows=3D28 width=3D106) (actual time=3D0.015..0.037 r= ows=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'::te= xt) > 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 > --00000000000057b2b2064c56c7ab Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Andrei, Tom, thank you for the explanation.

I had already tried u= sing extended statistics but without success; I probably did something wron= g.

After creating statis= tics on all the tables, I=E2=80=99m now getting optimal timings in producti= on.

This is not the ideal= solution because this issue will delay the PostgreSQL version upgrade:=C2= =A0
our applicat= ion will also need to be tested with respect to response times, and we will= need to=C2=A0
c= reate dedicated statistics depending on the specific cases. In addition, th= ere may be cases=C2=A0
that slip through testing and then surface in production due to a di= fferent data distribution.

Based on your explana= tion, my take is that the planner is not able to produce adequate estimates= =C2=A0
when ther= e=C2=A0are filters on many columns; when there are millions of rows involve= d this become=C2=A0
noticeable and extended statistics may be needed. One or more commits i= n PG16 changed the context=C2=A0
sligthly and caused the=C2=A0unfortunate path to be chosen= .

In any case, I don=E2= =80=99t think there are other workable solutions without changing the code.= =C2=A0
I get goo= d results using CTEs or LATERAL with OFFSET 0, but the point was to find=C2= =A0
a solution w= ithout having to change the code.

Thanks again, regards= .

Mauro Gatti


=

Il giorno gio 5 mar 2026 alle ore 22:44 Tom L= ane <tgl@sss.pgh.pa.us> ha s= critto:
Andrei L= epikhov <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 se= e, 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 planne= r,
> there is no difference in which version of the plan to choose - it is =
> just a game of chance.

Right.=C2=A0 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.=C2=A0 It's a "garbage in, garbage out" situation.= =C2=A0 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_id= =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));
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY= PLAN=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <= br> ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------------
=C2=A0Index Scan using ix_pricelist_options_1 on pricelist_options pl=C2=A0= (cost=3D0.43..117.12 rows=3D28 width=3D106) (actual time=3D0.015..0.037 ro= ws=3D165.00 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((brand_id =3D 10) AND ((line_code)::text =3D '= ;ABC'::text) AND ((model_year)::text =3D 'YR'::text) AND ((mode= l_code)::text =3D 'ABC'::text) AND ((version_code)::text =3D 'V= ER-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.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--00000000000057b2b2064c56c7ab--