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

On Thu, Sep 25, 2025 at 2:07 PM Todd Cook <cookt@blackduck.com> wrote:
> We've finished the tests comparing PG18rc1 with and without the proposed patch,
> including several runs each to verify reproducibility.  In short, the variance between the
> two is well within what we normally see, so statistically speaking, the results are
> indistinguishable.

That's interesting. It clearly wasn't like that with your minimal test
case, but the issue with the actual query is perhaps more
complicated/has more than one issue in play.

> However, the results are roughly on par with what we had been
> seeing with PG 15 and 16, so maybe some other change from 17 to 18 eliminated the
> regression we were seeing?

There's no specific reason to expect this: there's simply no way that
Postgres 18 (or Postgres 17) will get the same plan that you had on 15
and 16. That is, it is simply impossible for 17 or 18 to use a plan
where the problematic index scan node shows its ANY condition as a
filter qual ("Filter: ...") -- it *must* be a true index qual ("Cond:
..."). I always understood that your observed slowdown was a perverse
consequence of this relatively minor plan difference. Since of course
that was precisely what your original minimal test case actually
demonstrated ("perf top" showed we were bottlenecked on sorting the
SAOP array during index scan preprocessing).

What does EXPLAIN ANALYZE actually show you on 18, compared to 16,
with the same real world (non-minimal) test case? Are the joins and
scan nodes all the same as before (i.e. is the Postgres 18 plan
*identical to the "bad" Postgres 17 plan)?

Do you see any "Heap Fetches:", particularly with the problematic
index-only scan? Could it just be that you made sure to run VACUUM
ahead of the test this time, allowing the index-only scan seen on
Postgres 17 and 18 to avoid heap accesses?

Recall that the 15 and 16 plan had a plain index scan on another
index, and that the 17 plan you showed a few weeks back had  "Heap
Fetches: 598,916" (meaning that the index-only scan was completely
ineffective at avoiding heap accesses). If you're now able to get the
most out of index-only access, it could be enough to flip things in
favor of the new plan -- in spite of the fact that there is definitely
still a regression tied to needlessly sorting the scan's SAOP array
many times.

--
Peter Geoghegan