Re: Postgres: Queries are too slow after upgrading to PG17 from PG15

From: Peter Geoghegan <pg(at)bowt(dot)ie>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: Todd Cook <cookt(at)blackduck(dot)com>, Sajith Prabhakar Shetty <ssajith(at)blackduck(dot)com>, Andrei Lepikhov <lepihov(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Date: 2025-07-31 03:00:46
Message-ID: CAH2-WzmtHvFCWjKtxTHdJhxVzA-pecFoGscZ8Yw1vNgR7NCBFQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, Jul 30, 2025 at 10:39 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> I've not looked in great detail, but I did wonder if it's worth
> adjusting ExecIndexBuildScanKeys() to sort the array in a
> ScalarArrayOpExpr when it's Const beforehand. That might be a bit of
> wasted effort if there's just one scan, however.

If we were to do that, it wouldn't necessarily waste any effort. We're
sorting the array either way; sorting it a bit earlier seems unlikely
to hurt performance. The main difficulty with such a scheme is finding
a natural way to structure everything. We'd have to look up the
relevant ORDER proc earlier, which might be awkward and non-modular
(it has to use the correct ORDER proc in cross-type scenarios, for
example).

We'd also have to mark the relevant input scan key with a new flag
indicating that it is already sorted (and already has any NULL array
elements removed). That way, when nbtree preprocessing saw the flag,
it'd know that there was no need to sort the array. The sort would be
skipped, regardless of whether preprocessing took place during the
first btrescan/amrescan, or during some subsequent rescan.

--
Peter Geoghegan

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2025-07-31 03:06:10 BUG #19004: Incorrect lowercasing of word-final Greek capital Sigma (Σ)
Previous Message David Rowley 2025-07-31 02:49:16 Re: Postgres: Queries are too slow after upgrading to PG17 from PG15