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

On 2/3/26 08:21, Radim Marek wrote:
> On Tue, 3 Feb 2026 at 00:37, David Rowley <dgrowleyml(at)gmail(dot)com
> <mailto:dgrowleyml(at)gmail(dot)com>> wrote:
>
> On Tue, 3 Feb 2026 at 11:54, Radim Marek <radim(at)boringsql(dot)com
> <mailto: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
> <mailto: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
> <mailto: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.
>

I wouldn't call this a "bug", but the behavior may be a bit surprising
and working against what regresql aims to do.

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

I don't think it re-resolves the metadata. AFAIK it does fetch
additional information about the opclass, which was not needed for planning.

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

No, not really. That would increase the cost of planning - we do need to
do the lookup later, but we only do that for the one plan that "won". If
we did that during planning, it'd have to happen for all plans.

Perhaps we could identify buffer accesses from syscache lookups, and
track those separately? Then we'd know which of the hits/reads are from
"actual" execution.

regards

--
Tomas Vondra