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

On Fri, Aug 1, 2025 at 10:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Also, the calendar is a factor here: we are only a week away from
> release freeze for the August releases.  Even if you're feeling
> optimistic about what we could risk in v17 with some development
> effort, I'd counsel first working on a minimal patch that would
> be safe to apply on short notice, so we can get the easy win
> in this release cycle.

Attached is what I came up with. It makes the problematic test case
query execute a little under 10x faster, compared to unpatched
master -- see my numbers from Friday (the numbers for the less
ambitious approach/the approach taken by this patch). This is still a
WIP.

I'm unsure of how to assess my approach from a modularity perspective.
The patch simply assumes that any amsearcharray index AM is either
nbtree, or some other amsearcharray index AM that is okay with having
its array deduplicated based on nbtree rules/a B-Tree ORDER proc. So
there had better be a B-Tree opclass ORDER proc for us to work off of
(otherwise planning will throw an error). This is a little bit like
how we deal with RowCompareExpr within nodeIndexScan.c -- there are
similar hard-coded BTORDER_PROC lookups there. I don't *think* that I
need to use the new IndexAmTranslateCompareType facility (added by
commit c09e5a6a) for this, since most existing code doesn't bother
with any of that, either.

I haven't given much thought to the increase in planner cycles. I'll
stress-test that aspect of the patch soon.

Todd, Sajith: it would be helpful if you could test this patch.
Possibly by using the original problematic query, rather than the
minimized version that you posted to the list. The patch won't bring
performance up to parity with Postgres 15, but it should be a great
deal faster. Note that the patch that I've posted will only apply
against the current master branch (I'll prepare patches for earlier
branches once I have some buy-in). See
https://wiki.postgresql.org/wiki/Compile_and_Install_from_source_code
and https://www.postgresql.org/docs/current/installation.html for
instructions.


--
Peter Geoghegan