investigate: do Drizzle's call-shaped forms (like/ilike/gt/order_by/jsonb_*) actually engage functional indexes?

[coderdan]

Summary

Drizzle emits call-shaped forms for everything except equality (where it emits bare `=` — tracked in a sibling issue). The call-shaped forms look right — they're function calls, which is the right shape for matching functional indexes — but the function names don't match EQL's documented index extractors. Whether the planner pushes them down depends on operator/function inlinability, which we haven't verified.

What Drizzle emits (with file:line evidence)

Operator	Drizzle emits	Documented functional index extractor
`like` / `ilike` (`packages/drizzle/src/pg/operators.ts:853`)	`eql_v2.like(col, …)` / `eql_v2.ilike(col, …)`	`eql_v2.bloom_filter(col)` (GIN)
`gt` / `gte` / `lt` / `lte` (`:697`)	`eql_v2.gt(col, …)` etc.	(no Supabase index path today; OPE work in flight)
`between` (`:791`)	`eql_v2.gte(col,…) AND eql_v2.lte(col,…)`	(same — no Supabase path)
`asc` / `desc` (`:1636,1653`)	`ORDER BY eql_v2.order_by(col) ASC/DESC`	(no Supabase path; OPE work)
`jsonbPathQueryFirst` / `jsonbGet` / `jsonbPathExists` (`:927-931`)	`eql_v2.jsonb_path_query_first(col, …)`, `col -> …`, `eql_v2.jsonb_path_exists(col, …)`	`eql_v2.ste_vec(col)` (GIN)

What we don't know

For each Drizzle form, does the planner engage the documented functional index, fall back to seq scan, or hit some other path? Possible outcomes:

1. Inlining wins: `eql_v2.like(col, $1)` is inlined to `eql_v2.bloom_filter(col) @> eql_v2.bloom_filter($1)`, and the planner matches the GIN index. Best case.
2. Inlining blocked, fallback is correct but slow: `eql_v2.like` is opaque to the planner (e.g. plpgsql with SET search_path — the same constraint that PR fix: pin search_path on every eql_v2 function encrypt-query-language#177 ran into for the GIN containment helpers), so the index never engages and the function evaluates per-row. Functional but seq-scan-shaped.
3. Worse — index totally absent: there's no `eql_v2.like` functional index recipe documented anywhere, and the underlying bloom filter index isn't engaged by this form even with inlining. Need a different documented index pattern, or a different emitted form.

Without EXPLAIN against the bench fixture, we're guessing.

Reproduction setup

The encrypt-query-language repo has the canonical fixture:

• `tests/sqlx/fixtures/bench_data.sql` — 10K-row encrypted fixture.
• `tests/sqlx/fixtures/bench_setup.sql` — creates `bench_text_bloom_idx` (GIN on `bloom_filter`), `bench_text_hmac_idx` (hash on `hmac_256`), `bench_int_ore_idx` (btree on encrypted_int).
• `tests/sqlx/fixtures/drop_operator_classes.sql` — simulates Supabase mode.

For each Drizzle form:

1. Apply bench fixtures (with and without `drop_operator_classes`).
2. Construct the query Drizzle would emit by hand.
3. Run `EXPLAIN (ANALYZE, BUFFERS)` and observe whether the documented functional index is used.
4. Note inline-blockers (e.g. SET search_path on the called function — verifiable via `SELECT prosrc, proconfig FROM pg_proc WHERE proname = 'like' AND pronamespace = 'eql_v2'::regnamespace`).

Outcome and follow-ups

• If form 1 (inlining wins): close as no-action, document.
• If form 2 (inlining blocked): file a follow-up either to (a) make the relevant EQL functions inlinable (bare `LANGUAGE SQL` without SET, with schema-qualified bodies — the same shape used in perf: register cross-type btree/hash operators with eql_v2 opfamilies encrypt-query-language#186 for cross-type opfamily support functions), or (b) change Drizzle's emitted form to bypass the indirection.
• If form 3 (no index path): much bigger problem; needs a coordinated fix between EQL (define a recipe) and Drizzle (emit the matching form).

Priority

Medium-but-foundational. The findings determine whether Drizzle is fundamentally OK on Supabase (modulo the equality fix in the sibling issue) or whether we need to revise the Drizzle layer more broadly.

Related

• (sibling) Drizzle eq/ne/inArray bare-form issue — direct fix, no investigation needed.
• (parent) encryptedSupabase critical issue — different root cause (PostgREST limitation) but same Supabase-perf umbrella.
• fix: pin search_path on every eql_v2 function encrypt-query-language#177 — the inlinability constraint that may bite here.
• ci: add bench-supabase job to validate the supabase-variant install path encrypt-query-language#187 — proposed Supabase bench CI; this investigation's findings should feed into the Supabase test surface.