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

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