public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Joe Conway <[email protected]>
Cc: Clive Boughton <[email protected]>
Cc: [email protected]
Subject: Re: A serious change in performance between PG 15 and PG 16, 17, 18.
Date: Wed, 18 Feb 2026 13:01:22 +1300
Message-ID: <CAApHDvouSSC+koUjuMHx7gCTNFWrJOGtrVR+kpSn5=KLDtRX9A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Tue, 17 Feb 2026 at 04:42, Joe Conway <[email protected]> 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

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: A serious change in performance between PG 15 and PG 16, 17, 18.
  In-Reply-To: <CAApHDvouSSC+koUjuMHx7gCTNFWrJOGtrVR+kpSn5=KLDtRX9A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox