Detoast a column once per row instead of once per reference

Hi,

Every operator or function applied to a TOASTed column fetches and
decompresses the value again. A WHERE clause with two jsonb predicates on
the same document reads its TOAST chunks twice; eight predicates read them
eight times. Nothing about it is visible in the plan, and no query rewrite
avoids it.

SELECT id FROM events
WHERE doc @> '{"kind":"deploy"}'
AND doc -> 'target' ->> 'env' = 'prod';

Over 2000 rows whose jsonb column is stored out of line, that query reads
12013 buffers on master and 6013 with the patches below.

Tom Lane raised this in 2008 as "Reducing overhead for repeat de-TOASTing",
committed a narrow fix for index runtime keys in 2009, and sketched a
planner-driven pre-detoast in 2016 [1]. Andy Fan's "Shared detoast Datum"
carried a query-lifespan cache through ten revisions before stalling [2];
Robert Haas objected that a cache of that shape is "likely never going to
be committed", and that a planner-level decision would be preferable.

This series is the planner-level version. There is no cache, so no
eviction policy, no memory accounting and no invalidation question. The
value is kept beside the slot holding the row, for as long as the slot
holds it, and which columns are worth keeping is decided at plan time.

The rule the mechanism rests on is that tts_values is never modified: it
keeps the stored datum, so everything that copies, stores, projects or
inspects the column is unaffected. The detoasted value reaches only
argument positions -- places where a construct consumes a value and cannot
return it -- so it can never become the result of an expression or reach
code that must see the stored form. src/backend/executor/README has the
full statement. Holding it beside the slot needs one promise from a slot
implementation, TupleTableSlotOps.resets_detoasted. The four in-tree ones
make that promise; an implementation that leaves the flag false is never
given copies, and behaves exactly as today.

[1] https://www.postgresql.org/message-id/11665.1471360761%40sss.pgh.pa.us
[2] https://commitfest.postgresql.org/patch/4759/

Measurements
------------

Buffer accesses and user-space instructions, feature on against off in the
same binary. Q1-Q5 are JSONBench (PostgreSQL variant, 10m rows). Q6-Q10
are Oleg Bartunov's "The curse of TOAST" table, 10000 rows all stored out
of line: Q6 reads the column once and is the control, Q7 twice, Q8 four
times. Q9 and Q10 name it three times in a conjunction, so not every row
evaluates all three and their off counts stay below three times the
control.

query buffers off buffers on buffers instructions
----- ------------ ------------ --------- ------------
Q1 569,634 569,634 +0.0% -0.000%
Q2 574,539 570,673 -0.7% +0.120%
Q3 574,538 569,705 -0.8% +0.221%
Q4 343,222 342,249 -0.3% -0.481%
Q5 344,195 342,249 -0.6% -1.468%
Q6 30,104 30,104 +0.0% -0.032%
Q7 60,104 30,104 -49.9% -32.512%
Q8 120,104 30,104 -74.9% -55.227%
Q9 75,104 30,104 -59.9% -47.857%
Q10 75,104 30,104 -59.9% -46.286%

Buffer counts are exact and reproduce across runs. Instruction counts are
deterministic to within 0.05%, measured in single-user mode, so Q2 and Q3
are the serial rather than the parallel plan.

The worst case in the table is +0.22%. The cost is a test at each argument
position of every expression compiled, and compilation happens per
execution; a node that marks nothing settles each position in three null
tests. That shows up where execution is short: a plan-cached statement in
a loop with nothing to share costs +0.5%.

An application workload, reported second-hand because the data cannot be
shared: 70 filters over a jsonb attribute column, each testing it a median
of eight times, ran 3.31x faster with 7.48x fewer buffer accesses and
identical results.

The patches
-----------

0001 Executor support: the argument positions, the slot-side storage, the
steps in interpreter and JIT, the developer option detoast_reuse, and
the README section stating the rule. Nothing marks a column yet, so
it changes no behaviour; it is the half that needs careful reading.
0002 The planner decision, counting a node's own references and those it
shares with the scan below it. Nothing here can affect results.
0003 Subplan and nestloop parameters, which get their outer row's columns
by value and need a back-reference to find the copy. Separable.
0004 Tests. Injection points in detoast_attr() pin the exact number of
detoasts for around a hundred query shapes.

Open questions
--------------

1. Should function metadata of this kind live in pg_proc rather than in a
hardcoded list?

ExecFuncReadsStoredForm() names the functions that must receive the
stored datum -- pg_column_size, pg_column_compression,
pg_column_toast_chunk_id. A function missing from that list silently
returns a wrong answer, and an extension has no way to declare one of
its own. This belongs next to provolatile and proleakproof, as a
planner support request alongside those in supportnodes.h; I have not
written it. The other two hardcoded lists,
IsScanPlan()/ScanUsesIndexVar() and ExecFuncReadsSliceOrSize(), follow
existing practice (is_projection_capable_plan(), the get_oprrest()
switch in clausesel.c), and a miss there costs only an optimization.

2. Is it acceptable that the list of argument positions exists twice?

Once 0002 lands, that list exists in the executor as the safety rule
and in the planner to decide where a copy pays, held in step by a
comment; drift costs a missed optimization, not a wrong answer. I
built the alternative where the executor counts references itself,
needing no second list and no promise from slot implementations, and
withdrew it: about 2% of executor startup on every query.

3. Is sharing across one node boundary enough to start with?

If a scan reads a column once in its filter and the node directly above
it reads that column once as well, the column is marked on both and
detoasted once. A reference further up, above an intervening join, is
neither counted nor shared. Measured on 2000 rows of 98 kB text: an
aggregate directly above the scan detoasts the column once instead of
twice, a join filter above it once instead of six times, and that same
aggregate moved above the join gains nothing at all. Reaching further
means marking the column on every node in between and carrying the copy
up through each projection; left as a follow-up.

4. Is it right that no CATALOG_VERSION_NO bump is needed?

The new fields are on Plan and PlannedStmt, which are not stored in
catalogs and are serialized only for parallel workers within one server
version. Please say if that is wrong.

Status
------

Against master, and intended for the PG20-3 CommitFest. Posted for
discussion rather than for application: I would like opinions on the
questions above before it is worth anyone reviewing it line by line. No
platform-specific code. Regression suite, the new module and check-world
with assertions pass; CI is green on Linux, macOS and Windows.

The series was developed with Claude Code. I have read, built and tested
everything posted here, and the design choices and the measurements are
mine to answer for.

Regards,
Johannes Edmeier