MIME-Version: 1.0
References: <5A68A5FD-3CD2-4863-B274-51804235BE3C@softimp.com.au>
 <b0752470-6536-4a00-a00a-b2111986b2fd@joeconway.com>
In-Reply-To: <b0752470-6536-4a00-a00a-b2111986b2fd@joeconway.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 18 Feb 2026 13:01:22 +1300
Message-ID: 
 <CAApHDvouSSC+koUjuMHx7gCTNFWrJOGtrVR+kpSn5=KLDtRX9A@mail.gmail.com>
Subject: Re: A serious change in performance between PG 15 and PG 16, 17, 18.
To: Joe Conway <mail@joeconway.com>
Cc: Clive Boughton <clive.boughton@softimp.com.au>,
 pgsql-performance@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvouSSC%2BkoUjuMHx7gCTNFWrJOGtrVR%2BkpSn5%3DKLDtRX9A%40mail.gmail.com>
Precedence: bulk

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)?

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.

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.

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.

David