Horton

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

David Rowley <dgrowleyml@gmail.com>

View thread
From: David Rowley <dgrowleyml@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
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-31T02:39:31Z
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.

Attachments

On Thu, 31 Jul 2025 at 08:14, Peter Geoghegan <pg@bowt.ie> wrote:
> I suggest that you rewrite affected queries to make them join against
> a VALUES() with the same constants as those currently used in the
> larger IN() list. If you're not sure whether the set of constants from
> the application will be reliably unique, you can use DISTINCT to make
> sure.

Even just presorting the IN list constants would make it better. If I
manually adjust the recreator query to sort the items in both IN
lists, I get:

Execution Time: 263.365 ms

vs:

Execution Time: 804.377 ms

Of course, the qsort_arg() call still happens, it'll hit qsort_arg's
presorted short-circuit case and will do very little.

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.

David