Correct functional-index expression per v3 domain is undiscoverable — indexing `eql_v3.ste_vec(col)` builds a permanently dead index

[tobyhede]

Summary

Choosing the right functional index for an EQL v3 column currently requires reading the SQL bundle's function bodies. The correct expression is documented — but on the operator, which is not where someone writing CREATE INDEX is looking. We shipped a permanently dead index in cipherstash/stack as a result, and nothing caught it.

Two small, non-breaking asks at the bottom. Not a request to change or remove any function.

What happened

In cipherstash/stack#770 we ported a benchmark fixture from EQL v2 to v3 and renamed its three functional indexes:

CREATE INDEX bench_text_hmac_idx    ON bench USING hash (eql_v3.eq_term(enc_text));    -- correct
CREATE INDEX bench_text_bloom_idx   ON bench USING gin  (eql_v3.match_term(enc_text)); -- correct
CREATE INDEX bench_jsonb_stevec_idx ON bench USING gin  (eql_v3.ste_vec(enc_jsonb));   -- never used

The third can never be matched by any containment query. @> on eql_v3_json_search is backed by (3.0.2 bundle):

CREATE FUNCTION eql_v3."@>"(a public.eql_v3_json_search, b eql_v3.query_json)
RETURNS boolean LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
AS $$ SELECT eql_v3.to_ste_vec_query(a)::jsonb @> eql_v3.to_ste_vec_query(b)::jsonb $$;

which inlines, so the expression the planner matches against is eql_v3.to_ste_vec_query(col)::jsonb — not eql_v3.ste_vec(col).

The failure is completely silent:

• eql_v3.ste_vec(val jsonb) exists, and eql_v3_json_search is a domain over jsonb, so the call resolves.
• It's IMMUTABLE, so it's legal in an index expression.
• It returns jsonb[], so GIN's default array_ops accepts it and CREATE INDEX succeeds.

The index builds, pg_indexes reports it, it costs write throughput on every insert, and it is never read. Nothing in Postgres warns about an unused index, and a smoke test asserting the index exists passes happily.

Why it's easy to get wrong

The v2 implementation genuinely went through ste_vec. In the v2 bundle:

CREATE FUNCTION eql_v2.ste_vec_contains(a eql_v2_encrypted, b eql_v2_encrypted) ...
  sv_a := eql_v2.ste_vec(a);
  sv_b := eql_v2.ste_vec(b);

v3 rewrote containment to normalise both sides to selector-only jsonb and use native jsonb @> — a deliberate improvement, precisely so a functional index can engage. But that moved the indexable expression to to_ste_vec_query, and a v2→v3 rename that preserves the function name silently loses the meaning.

The naming sets a trap. eq_term / ord_term / match_term are column-side extractors named <capability>_term, so a reader expects the JSON slot in that family to be ste_vec. It isn't — the JSON index expression has no _term name at all, and eql_v3.ste_vec is a general element accessor used by eql_v3.jsonb_array_length and eql_v3.jsonb_array_elements.

The rule is invisible in the SQL. "Index the expression the operator inlines to" holds for all three, but only two of them happen to be named after their capability. You can't tell which case you're in without opening the function body.

Asks

1. COMMENT ON FUNCTION eql_v3.ste_vec(jsonb) — say that it is an element accessor, not an index-term extractor, and point at to_ste_vec_query(col)::jsonb for containment indexes. There's precedent for using comments this way in the bundle:

COMMENT ON FUNCTION eql_v3.jsonb_path_query_first(jsonb, text) IS
  'eql-inline-critical: raw-jsonb path first helper; must stay inlinable (unpinned search_path)';

One line, no behaviour change, and it lands in \df+ where someone debugging an unused index would actually see it.

2. A documented per-domain index recipe. Something like a table of domain → recommended index DDL in the EQL docs, or a helper that emits the DDL. The information exists today only as a comment on eql_v3."@>":

Inlines to native jsonb @> over eql_v3.to_ste_vec_query(a)::jsonb, so a functional GIN index on the same expression engages.

That comment is correct and was the thing that let us diagnose it — it's just filed under the operator rather than anywhere index-shaped. Deriving all three indexes for one small table meant grepping a 59k-line bundle for function bodies.

Related invariant worth stating explicitly

Index engagement depends on the operator wrappers staying inlinable (LANGUAGE sql + IMMUTABLE + single-SELECT body). If one were ever changed to plpgsql or VOLATILE, every functional index over its body would stop being matched — silently, with no error and no plan change anyone would notice without an EXPLAIN assertion. If that's an intentional guarantee of the bundle, it may be worth naming as one (the eql-inline-critical: comment convention already gestures at it).

Workaround on our side

cipherstash/stack now pins each bench index against the operator body in the installed bundle, so a mismatch fails without needing a database: scripts/__tests__/bench-index-expressions.test.mjs.

Found while reviewing cipherstash/stack#770. Bundle version: @cipherstash/eql@3.0.2.