Re: Reducing opr_sanity test's runtime under CLOBBER_CACHE_ALWAYS

Hi,

On 2021-05-08 15:44:57 -0400, Tom Lane wrote:
> In a nearby thread I bemoaned the fact that the core regression tests
> seem to have gotten significantly slower in the last couple of months,
> at least with CCA enabled: hyrax reports completing them in 12:52:44
> on 18 March, while its most recent run on 1 May took 14:08:18.
>
> Trying to diagnose the cause overall seemed a bit daunting, but
> I thought I'd dig into the opr_sanity test in particular, as it
> is one of the slower tests under CCA to start with and had also
> slowed down noticeably (from 3701581 ms to 4761183 ms, or 28%).
> I was able to complete a bisection using just that test, and
> got an unexpected result: most of the slowdown appeared at
> ab596105b (BRIN minmax-multi indexes). Apparently the additional
> time is simply from having to check the additional pg_amop and
> pg_amproc entries, which that patch added quite a few of.

I suspect that it might be not just that. From a quick profile it looks
like debug_invalidate_system_caches_always spends a good chunk of its
time in ResetCatalogCache() and hash_seq_search(). Those cost linearly
with the size of the underlying hash tables.

Wo what what might be happening is that the additional catalog entries
pushed some of the catcache hash tables into growing
(RehashCatCache()). Which then makes all subsequent ResetCatalogCache()
scans slower.

Not that that changes much - your proposed fix still seems reasonable.

> I noticed that all of the slowest queries in that test were dependent
> on the binary_coercible() plpgsql function that it uses. Now, that
> function has always been a rather lame attempt to approximate the
> behavior of the parser's IsBinaryCoercible() function, so I've been
> thinking for some time that we ought to get rid of it in favor of
> actually using IsBinaryCoercible(). I tried that, by adding a
> shim function to regress.c, and got a most gratifying result:
> on my machine opr_sanity's runtime with
> debug_invalidate_system_caches_always = 1 drops from
> 29m9s to 3m19s. Without CCA the speedup is far less impressive,
> 360ms to 305ms, but that's still useful. Especially since this
> makes the test strictly more accurate.

Cool!

> Anyway, I propose that we ought to sneak this into HEAD, since
> it's only touching test code and not anything production-critical.

+1

Greetings,

Andres Freund