perf: eql_v2.like / eql_v2.ilike are VOLATILE — blocks planner inlining and bloom_filter index match

[coderdan]

Summary

eql_v2.like and eql_v2.ilike are LANGUAGE sql functions with single-statement bodies that already expand to the canonical bloom-filter containment form:

SELECT eql_v2.bloom_filter(a) @> eql_v2.bloom_filter(b);

This is the exact shape that the documented Supabase functional index expects:

CREATE INDEX my_text_bloom_idx ON t USING gin (eql_v2.bloom_filter(col));

But the functions are marked VOLATILE. The Postgres planner refuses to inline volatile functions into index matching (correctly — a volatile call may have side effects or non-deterministic results). So WHERE eql_v2.like(col, value) is treated as an opaque function call on the row's column value, the index never matches, and customer queries silently seq-scan.

Reproduction

In a fresh EQL install with a 10k-row fixture (see cipherstash/stack packages/bench):

EXPLAIN (ANALYZE)
  SELECT id FROM bench WHERE eql_v2.like(enc_text, '<encrypted_value>'::eql_v2_encrypted);
-- Plan: Seq Scan on bench  (cost=0..4065  actual time=0..234.207ms)

EXPLAIN (ANALYZE)
  SELECT id FROM bench
   WHERE eql_v2.bloom_filter(enc_text)
      @> eql_v2.bloom_filter('<encrypted_value>'::eql_v2_encrypted);
-- Plan: Bitmap Index Scan on bench_text_bloom_idx (actual time=3.424ms)

The wrapped form is ~60× faster on a small fixture; the gap widens with row count.

Function metadata

SELECT proname, prolang::regtype, provolatile, prosrc
  FROM pg_proc
 WHERE pronamespace='eql_v2'::regnamespace
   AND proname IN ('like','ilike');

 proname | prolang |  provolatile  |                       prosrc
---------+---------+---------------+----------------------------------------------------
 like    | sql     | v (VOLATILE)  | SELECT eql_v2.bloom_filter(a) @> eql_v2.bloom_filter(b);
 ilike   | sql     | v (VOLATILE)  | SELECT eql_v2.bloom_filter(a) @> eql_v2.bloom_filter(b);

The body is already perfect for inlining — pure deterministic over its inputs (modulo eql_v2.bloom_filter, which is itself IMMUTABLE). Marking IMMUTABLE is the only change needed.

Suggested fix

ALTER FUNCTION eql_v2.like(eql_v2_encrypted, eql_v2_encrypted) IMMUTABLE;
ALTER FUNCTION eql_v2.ilike(eql_v2_encrypted, eql_v2_encrypted) IMMUTABLE;

Or in the function definitions themselves: change VOLATILE to IMMUTABLE next to LANGUAGE sql.

Why this matters

Customers using eql_v2.like(col, val) directly (raw SQL, ORMs other than Drizzle) silently get seq-scan performance on Supabase even though the documented index recipe is in place. Drizzle has shipped a workaround (cipherstash/stack#430) that emits the wrapped form directly, but that's per-integration; fixing the underlying function helps everyone.

Related

• investigate: do Drizzle's call-shaped forms (like/ilike/gt/order_by/jsonb_*) actually engage functional indexes? stack#422 — investigation that surfaced this (also tracks similar issues with gt / gte / lt / lte / order_by / jsonb_path_*, though those need different remediation).
• cipherstash/stack#430 — Drizzle-side wrap that bypasses the volatility issue while this fix lands.
• #177 — prior art for the inlinability constraint.

Other functions with similar shape

While auditing for this issue, the following also looked relevant:

• eql_v2.gt / gte / lt / lte — all LANGUAGE plpgsql VOLATILE. plpgsql can never inline; they'd also need a rewrite to LANGUAGE sql IMMUTABLE (and a defined containment form on a Supabase-friendly index, which doesn't exist yet — separate scope).
• eql_v2.jsonb_path_exists / eql_v2.jsonb_path_query_first — LANGUAGE plpgsql IMMUTABLE. plpgsql blocks inlining. Needs a body rewrite to inlinable SQL on eql_v2.ste_vec(col) @> ... (separate scope; needs more investigation on what the right ste_vec containment form is).
• eql_v2.order_by — LANGUAGE plpgsql IMMUTABLE. Same plpgsql block; no Supabase index path either.

This issue is just for like / ilike since they're the clear win — single-statement SQL bodies that just need a volatility flip.