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

Peter Geoghegan <pg@bowt.ie> writes:
> 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 took a very quick look at this, and there are a couple of things
I don't like:

* I don't love the fact that _bt_presort_const_array shares no code
with _bt_sort_array_elements.  Maybe there's nothing to be done
about it given that they're working on different representations.
But the comments could at least point out that the two functions
had better give equivalent results.

* I really don't like the fact that SK_PRESORTED gets set so far
away from the code that's actually doing the work to make that
valid (not to mention far from the code that consults that flag).
Even if it's bug-free today, that seems like an invitation to
maintenance problems.  Can we factor that differently?

> 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.

Well, it's worse than that: it's assuming that support procedure 1
is in fact a btree ordering proc in any opclass of an amsearcharray
AM.  I don't think that's too safe.  We have our hands on enough
info about the index that we could skip trying to do the sort if
it's not a btree, and I think we should do so, at least for today.

> This is a little bit like
> how we deal with RowCompareExpr within nodeIndexScan.c -- there are
> similar hard-coded BTORDER_PROC lookups there.

One big difference is that we *know* that the comparisons in a
RowCompareExpr are btree operators, because the parser constructed it
that way.  We cannot make such assumptions about arbitrary
ScalarArrayOps, even if they are indexquals.

            regards, tom lane