Re: Non-deterministic buffer counts reported in execution with EXPLAIN ANALYZE BUFFERS

On Tue, 3 Feb 2026 at 20:21, Radim Marek <radim(at)boringsql(dot)com> wrote:
> Apologies, "bug" might be a bit harsh and didn't want to imply those buffers won't be reported. My assumption is that catalogue lookups for resolving sort operator metadata would be reported as planning buffers rather than execution buffers. This is already what's reported for other lookups for very same query above.
>
> The executor re-resolves sort operator metadata that the planner already looked up. The first lookup counts as planning buffers, the second as execution buffers.

If it was looking up the same thing, then there shouldn't be a cache
miss, and the buffers won't be accessed.

> Would passing that already resolved opfamily from the planner be a reasonable direction? My reasoning in this case is that I'm building on the hypothesis that buffers (whether shared hit or read) should be deterministic for the same query on the same data, and the same plan. This re-resolution on first execution breaks it.

I think generally, offloading more work into the planner for looking
up things that are always the same for every invocation of a plan is
generally good. For anything that moves things more in that direction,
you'd need to ensure that the plan is correctly invalidated when
something changes about the extra thing you looked up in planning.
I've not looked into the specifics of this instance, other than the
function being called which causes the buffer usage is
get_opfamily_proc(). I don't see that called during planning for this
query, so your statement about "already resolved opfamily from the
planner" doesn't seem true.

However, I imagine this is just one of many things and if you're
aiming to stabilise your tests doing this, then you'll likely be
fixing things for a long time to come. I imagine a better way if
you're just doing this for running test suites would be to invent some
way to prepopulate the sys and cat caches. Maybe that could be a
contrib module. I don't think there's a way to get a relcache miss
during execution, but I guess it could change with prepared statements
if we ever decided to start evicting long unused relcache entries one
day.

David