json_tree: the smaller performance findings from the first benchmarks #1324

Summary

The json_tree family (EQL v4's encrypted JSON document type, written by Stack Encrypt's JSON index) was benchmarked at 10,000 rows against the v3 SteVec type and native jsonb (cipherstash/benches, branch feat/json-tree-benches, report/JSON_TREE_COMPARISON.md). The three large findings, the containment projection being recomputed per recheck, the subtree walk rescanning the document per node, and the range comparison losing its index under a prepared statement, are fixed in #1325, stacked on #1288. Four smaller findings remain and are collected here so they are not lost.

What is left

1. -> scans every entry on every extraction. eql_v4.json_tree_path_query_first(doc, selector) runs jsonb_array_elements(doc -> 'sv') and filters on s. That is 0.18 ms on a five-node document and 0.79 ms on a 220-node one, per row. An index of the entries by selector, stored beside sv (a {selector: [ordinal, …]} object, as q now is for containment) or built as a jsonb lookup, would make it one -> and one array read.
2. json_tree_contains always plans a second GIN probe for alt. The body is q @> (b - 'alt') OR (b ? 'alt' AND q @> (b -> 'alt')), and the planner emits a BitmapOr of two index probes even when the needle carries no alt (every needle whose root is a container). The second probe is cheap but visible in every plan; a form that lets the planner drop it when alt is absent, or a separate operator for scalar-root needles, would remove it.
3. ORDER BY eql_v4.ord_term(col -> sel) LIMIT 10 measured 0.92 ms against the v3 SteVec's 0.25 ms, both index scans on the functional btree. The plan reads 96 buffers for ten rows; the difference is not explained yet and is worth a look together with item 1, since the index scan re-extracts through -> for the output.
4. A nested document is 24 times its plaintext (96,596 characters against 4,034 for the benchmark's 220-node shape): about 440 characters per entry, from six 32-character hex tags, a base64 AES-GCM ciphertext with a 12-byte nonce and 16-byte tag per node, and the sealed root's copy of the whole document. The design document's open decisions 1 (base64url instead of hex for the sixteen-byte fields, about 40 characters per entry) and 6, and the per-path policy with opaque subtrees, are the levers. The 100,000-row nested table is 8 GB on disk at this size.

Numbers, 10,000 rows

Related

Refs #1288 and #1325. Design: docs/plans/2026-10-17-json-index-stack-encrypt-spike.md, "Implementation notes (2026-10-21, Phase 3)".