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

On Tue, 3 Feb 2026 at 00:37, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 3 Feb 2026 at 11:54, Radim Marek <radim(at)boringsql(dot)com> wrote:
> > Buffers: shared hit=4
> > -> Seq Scan on sort_buffer_test (cost=0.00..2.50 rows=1
> width=32) (actual time=0.042..0.126 rows=1.00 loops=1)
> > Filter: (lower(email) = 'email_1(at)example(dot)com'::text)
> > Rows Removed by Filter: 99
> > Buffers: shared hit=1
> >
> > vs
> >
> > Buffers: shared hit=1
> > -> Seq Scan on sort_buffer_test (cost=0.00..2.50 rows=1
> width=32) (actual time=0.021..0.057 rows=1.00 loops=1)
> > Filter: (lower(email) = 'email_1(at)example(dot)com'::text)
> > Rows Removed by Filter: 99
> > Buffers: shared hit=1
>
> > I traced the potential issue down in case of ORDER BY to
> pg_amop/pg_amproc lookups. The specific lookups vary by operation but the
> pattern is the same: first execution incurs catalog reads that get cached
> for subsequent runs. This applies to DISTINCT (sort, not hashed), GROUP BY,
> window functions, etc - which seems to isolate it to sort node.
>
> I'm finding it hard to fathom why you think this is a bug. We have
> various caches that will require accessing various catalogue tables
> and probably indexes too, which will be accessed unless the cache has
> already been populated. These accessing the buffers for these are
> included in the buffers count in EXPLAIN.
>
> What is it you expect to happen here? If we access buffers and don't
> report them, then IMO, that's a bug.
>
> David
>

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.

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.

Radim