Re: Detoast a column once per row instead of once per reference

Rebased over 36a5622976 ("Don't write to postgres's stdin in shmem
startup failure test"). The only conflict was src/test/modules/Makefile,
where the new hash test module landed on the same SUBDIRS line; no
functional change.

Regards,
Johannes Edmeier

On Wed, 30 Sept 2026 at 17:47, Johannes Edmeier
<johannes(dot)edmeier(at)steadybit(dot)com> wrote:
>
> 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

--
JOHANNES EDMEIER
Co-Founder & Head of Engineering
steadybit GmbH

johannes(dot)edmeier(at)steadybit(dot)com
+49 (0) 151 1086 7077

steadybit.com
Hochstrasse 11 | 42697 Solingen | Germany
Commercial Register: District Court Wuppertal | HRB 30206 | Germany