From: Clive Boughton <clive.boughton@softimp.com.au>
Message-Id: <FCCB5F82-0C39-47DB-8C76-2474EDDE4CF3@softimp.com.au>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_8C1C6DB9-12E6-49B7-93E7-8DB570AAC369"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.4\))
Subject: Re: A serious change in performance between PG 15 and PG 16, 17, 18.
Date: Wed, 18 Feb 2026 12:05:26 +1100
In-Reply-To: 
 <CAApHDvouSSC+koUjuMHx7gCTNFWrJOGtrVR+kpSn5=KLDtRX9A@mail.gmail.com>
Cc: Joe Conway <mail@joeconway.com>,
 pgsql-performance@postgresql.org
To: David Rowley <dgrowleyml@gmail.com>
References: <5A68A5FD-3CD2-4863-B274-51804235BE3C@softimp.com.au>
 <b0752470-6536-4a00-a00a-b2111986b2fd@joeconway.com>
 <CAApHDvouSSC+koUjuMHx7gCTNFWrJOGtrVR+kpSn5=KLDtRX9A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/FCCB5F82-0C39-47DB-8C76-2474EDDE4CF3%40softimp.com.au>
Precedence: bulk


--Apple-Mail=_8C1C6DB9-12E6-49B7-93E7-8DB570AAC369
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

David,

I've handed over further communication with you (and others) to Ji =
Zhang, my (better) technical colleague.
He is able to determine any issues surrounding open/closed publication.

Clive.

Dr Clive Boughton

Software Improvements

Street address:
97 Bankers Road, NSW 2621
Australia

Mobile Phone: +61 (0)410 632 055
Telephone: +61 (0)2 6230 3195


> On 18 Feb 2026, at 11:01=E2=80=AFam, David Rowley =
<dgrowleyml@gmail.com> wrote:
>=20
> On Tue, 17 Feb 2026 at 04:42, Joe Conway <mail@joeconway.com> wrote:
>> Are some of your indexes on collatable columns? If so, what version =
of
>> glibc is on each of your systems (i.e. did you change OS major =
versions)?
>=20
> For the record, Clive did send me the query and EXPLAIN output
> offline. I asked him if he'd anonymise it and post it here, but no
> response yet.
>=20
> I'll just describe what I saw so as not to share details that Clive
> isn't happy making public. The query is to a single table, no joins.
> The filter is equality on an INT column. The v15 plan uses a Seq Scan
> and runs in 175ms. v18 is using a Bitmap Heap Scan using the table's
> primary key index. The PK must be a composite key and the WHERE clause
> must be on the first column of that key. That query runs in 305ms, so
> more like x2 rather than the reported 300-600x. v18 estimates slightly
> fewer rows than v15, so that might be why it switched plans to the
> bitmap heap scan. That could be down to random sampling from ANALYZE
> finding fewer rows that match the query's WHERE clause in v18.
>=20
> I'd recommend Clive to try running ANALYZE on the table and seeing if
> the plan changes. Repeat that a few times to see if the Seq Scan plan
> is ever picked. I'd also check if the random_page_cost and
> seq_page_cost settings are the same on both instances and ensure
> enable_seqscan is on.
>=20
> David


--Apple-Mail=_8C1C6DB9-12E6-49B7-93E7-8DB570AAC369
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;">David,<div><br></div><div>I've handed over further =
communication with you (and others) to Ji Zhang, my (better) technical =
colleague.</div><div>He is able to determine any issues surrounding =
open/closed publication.</div><div><br></div><div>Clive.</div><div><br =
id=3D"lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div dir=3D"auto" style=3D"caret-color: rgb(0, 0, =
0); color: rgb(0, 0, 0); letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none; overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>Dr Clive Boughton<br><br>Software =
Improvements<br><br>Street address:<br>97 Bankers Road, NSW =
2621<br>Australia<br><br>Mobile Phone: +61 (0)410 632 055<br>Telephone: =
+61 (0)2 6230 3195<br></div></div><br =
class=3D"Apple-interchange-newline"></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">
</div>
<div><br><blockquote type=3D"cite"><div>On 18 Feb 2026, at 11:01=E2=80=AFa=
m, David Rowley &lt;dgrowleyml@gmail.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>On Tue, 17 Feb 2026 at =
04:42, Joe Conway &lt;mail@joeconway.com&gt; wrote:<br><blockquote =
type=3D"cite">Are some of your indexes on collatable columns? If so, =
what version of<br>glibc is on each of your systems (i.e. did you change =
OS major versions)?<br></blockquote><br>For the record, Clive did send =
me the query and EXPLAIN output<br>offline. I asked him if he'd =
anonymise it and post it here, but no<br>response yet.<br><br>I'll just =
describe what I saw so as not to share details that Clive<br>isn't happy =
making public. The query is to a single table, no joins.<br>The filter =
is equality on an INT column. The v15 plan uses a Seq Scan<br>and runs =
in 175ms. v18 is using a Bitmap Heap Scan using the table's<br>primary =
key index. The PK must be a composite key and the WHERE clause<br>must =
be on the first column of that key. That query runs in 305ms, so<br>more =
like x2 rather than the reported 300-600x. v18 estimates =
slightly<br>fewer rows than v15, so that might be why it switched plans =
to the<br>bitmap heap scan. That could be down to random sampling from =
ANALYZE<br>finding fewer rows that match the query's WHERE clause in =
v18.<br><br>I'd recommend Clive to try running ANALYZE on the table and =
seeing if<br>the plan changes. Repeat that a few times to see if the Seq =
Scan plan<br>is ever picked. I'd also check if the random_page_cost =
and<br>seq_page_cost settings are the same on both instances and =
ensure<br>enable_seqscan is =
on.<br><br>David<br></div></div></blockquote></div><br></div></body></html=
>=

--Apple-Mail=_8C1C6DB9-12E6-49B7-93E7-8DB570AAC369--