Horton

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

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Todd Cook <cookt@blackduck.com>, Sajith Prabhakar Shetty <ssajith@blackduck.com>, Andrei Lepikhov <lepihov@gmail.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2025-07-31T03:00:46Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Convert strategies to and from compare types

  2. Enhance nbtree ScalarArrayOp execution.

  3. Improve planning of btree index scans using ScalarArrayOpExpr quals.

  4. Fix planning of btree index scans using ScalarArrayOpExpr quals.

On Wed, Jul 30, 2025 at 10:39 PM David Rowley <dgrowleyml@gmail.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