From: Clive Boughton <clive.boughton@softimp.com.au>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_F540079D-74B1-4839-A2B6-9A8D2CF7C639"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.4\))
Subject: A serious change in performance between PG 15 and PG 16, 17, 18.
Message-Id: <5A68A5FD-3CD2-4863-B274-51804235BE3C@softimp.com.au>
Date: Mon, 16 Feb 2026 14:49:02 +1100
To: pgsql-performance@postgresql.org
Archived-At: 
 <https://www.postgresql.org/message-id/5A68A5FD-3CD2-4863-B274-51804235BE3C%40softimp.com.au>
Precedence: bulk


--Apple-Mail=_F540079D-74B1-4839-A2B6-9A8D2CF7C639
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

To whom it may concern,

The system I'm referring to has been built by my company beginning in =
2019/2020 with PG 10.2.

The SQL functionality and related tables contained within the PG DB has =
changed little since initial delivery.
There a very few complex forms of SQL used and the tables are quite =
simple - the largest being ~400,000 rows based directly on ~50,000 =
instances of the primary key.
The functions do not update anything in this 'large' table (from which =
data is only read) but do produce several significantly smaller tables - =
the largest being ~ 40 columns x 50-80 rows about 80% of which are fixed =
point numbers (6 decimal places).
Initially (in 2020 before an indexing was added) the time to produce and =
output the overall result was 30-60 minutes.  After adding quite basic =
indexing, that time was reduced to 1 or 2 minutes.
=20
PG 10.2 remained in place until 2023 when the DB was upgraded to PG 15.2 =
with no problems. In fact performance increased.

When I recently upgraded to PG 18.1 (without any changes to the DB =
functions or tables, or indexing) it took 7-10 hours to produce the same =
result - a 300-600 times decrease in performance.

My first step was to reduce the jump in versioning of PG from 15 to 18 =
down to 15 - 16.  This action produced the same very poor performance =
result as when using PG 18.1

To try and save time I employed the help of Claude Pro (an excellent AI =
tool for software development).  After implementing all sorts of =
suggestions (indexing, configuring etc.) and undertaking all sorts of =
checks (using available PG functions), the conclusion is that some =
significant regression was introduced in PG 16, and hasn't been removed =
in (at least) PG 18 (I ddn't try PG 17).  Of course, I do understand =
that bringing Claude Pro into the fray may have its own issues, but =
Claude's suggestions and explanations do make sense and at the very =
least may help a PG expert to more easily hone in on the path to take to =
fix the issue.

Sticking with PG 15 in the short term is fine for us.  However, the =
current performance of PG 16 - PG 18 is totally unacceptable in terms of =
expectations.

As an aside we did increase the number of elements by 30% within the =
largest table (mentioned above).  So that instead of ~400,000 rows there =
were ~550,000 rows to read from.  The number of instances of the primary =
key was not changed.
This action (for PG 15) only added an extra 20-30 seconds to the =
production of the result - more or less as expected.  Although the same =
actions were performed on PG 18, after several hours we simply killed =
the process.

BTW, the 'tests' I did were carried out in both a MacOS (Sequoia) and a =
Linux (Rocky Linux 9.6) environment.

For reasons of security I can't provide all the SQL code, but I can (if =
it is necessary) provide an outline of what the code does.

Regards.

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


--Apple-Mail=_F540079D-74B1-4839-A2B6-9A8D2CF7C639
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dus-ascii"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">To whom it may =
concern,<div><br></div><div>The system I'm referring to has been built =
by my company beginning in 2019/2020 with PG =
10.2.</div><div><br></div><div>The SQL functionality and related tables =
contained within the PG DB has changed little since initial =
delivery.</div><div>There a very few complex forms of SQL used and the =
tables are quite simple - the largest being ~400,000 rows based directly =
on ~50,000 instances of the primary key.</div><div>The functions do not =
update anything in this 'large' table (<i>from which data is only =
read</i>) but do produce several significantly smaller tables - the =
largest being ~ 40 columns x 50-80 rows about 80% of which are fixed =
point numbers (6 decimal places).</div><div>Initially (in 2020 before an =
indexing was added) the time to produce and output the overall result =
was 30-60 minutes. &nbsp;After adding quite basic indexing, that time =
was reduced to 1 or 2 minutes.</div><div>&nbsp;</div><div>PG 10.2 =
remained in place until 2023 when the DB was upgraded to PG 15.2 with no =
problems. In fact performance increased.</div><div><br></div><div>When I =
recently upgraded to PG 18.1 (without any changes to the DB functions or =
tables, or indexing) it took 7-10 hours to produce the same result - a =
<b>300-600 times decrease in =
performance</b>.</div><div><br></div><div>My first step was to reduce =
the jump in versioning of PG from 15 to 18 down to 15 - 16. &nbsp;This =
action produced the same very poor performance result as when using PG =
18.1</div><div><br></div><div>To try and save time I employed the help =
of Claude Pro (<i>an excellent AI tool for software development</i>). =
&nbsp;After implementing all sorts of suggestions (indexing, configuring =
etc.) and undertaking all sorts of checks (using available PG =
functions), the conclusion is that some significant regression was =
introduced in PG 16, and hasn't been removed in (at least) PG 18 (I =
ddn't try PG 17). &nbsp;Of course, I do understand that bringing Claude =
Pro into the fray may have its own issues, but Claude's suggestions and =
explanations do make sense and at the very least may help a PG expert to =
more easily hone in on the path to take to fix the =
issue.</div><div><br></div><div>Sticking with PG 15 in the short term is =
fine for us. &nbsp;However, the current performance of PG 16 - PG 18 is =
totally unacceptable in terms of =
expectations.</div><div><br></div><div>As an aside we did increase the =
number of elements by 30% within the largest table (mentioned above). =
&nbsp;So that instead of ~400,000 rows there were ~550,000 rows to read =
from. &nbsp;The number of instances of the primary key was not =
changed.</div><div>This action (for PG 15) only added an extra 20-30 =
seconds to the production of the result - more or less as expected. =
&nbsp;Although the same actions were performed on PG 18, after several =
hours we simply killed the process.</div><div><br></div><div>BTW, the =
'tests' I did were carried out in both a MacOS (Sequoia) and a Linux =
(Rocky Linux 9.6) environment.</div><div><br></div><div>For reasons of =
security I can't provide all the SQL code, but I can (if it is =
necessary) provide an outline of what the code =
does.</div><div><br></div><div>Regards.</div><div><br></div><div>Clive.</d=
iv><div><br><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>
<br></div></body></html>=

--Apple-Mail=_F540079D-74B1-4839-A2B6-9A8D2CF7C639--