Summary
Mirrors #193 for the range operators. The <, <=, >, >= operators on eql_v2_encrypted were declared LANGUAGE plpgsql wrapping eql_v2.compare()'s priority list (Block ORE → CLLW u64 → CLLW var → OPE → hmac → literal fallback). plpgsql function bodies are never inlined by the planner, so bare-form range predicates (WHERE col < $1) could not structurally match any functional index — including a btree on eql_v2.ore_block_u64_8_256(col). The result was unavoidable seq scans on managed Postgres installs that lack eql_v2.encrypted_operator_class (Supabase, anything that excludes the operator-family).
This is Phase 2 of the broader predicate/extractor RFC. Phase 1 (#193) handled equality, inequality, and LIKE/ILIKE. Phase 2 was deferred because it depended on settling the Block-ORE-only contract for range comparisons.
Operators in scope
| Operator |
Replace with (LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE) |
< (eql_v2_encrypted ×3 overloads) |
SELECT eql_v2.ore_block_u64_8_256(a) < eql_v2.ore_block_u64_8_256(b) |
<= (eql_v2_encrypted ×3 overloads) |
SELECT eql_v2.ore_block_u64_8_256(a) <= eql_v2.ore_block_u64_8_256(b) |
> (eql_v2_encrypted ×3 overloads) |
SELECT eql_v2.ore_block_u64_8_256(a) > eql_v2.ore_block_u64_8_256(b) |
>= (eql_v2_encrypted ×3 overloads) |
SELECT eql_v2.ore_block_u64_8_256(a) >= eql_v2.ore_block_u64_8_256(b) |
For the chain to reach index matching, the inner eql_v2.ore_block_u64_8_256_{eq,neq,lt,lte,gt,gte} functions also have to be inlinable (IMMUTABLE STRICT PARALLEL SAFE).
Behaviour change
Range operators no longer fall through eql_v2.compare()'s priority list. Columns carrying only ore_cllw_u64_8, ore_cllw_var_8, opf, or opv terms now raise from the ore_block_u64_8_256 extractor when used with bare-form range operators. Callers in that situation must rewrite to the matching extractor form, e.g. WHERE eql_v2.ore_cllw_u64_8(col) < eql_v2.ore_cllw_u64_8($1::jsonb).
This narrower contract is what makes the inlining work — the planner can structurally match the bare-form predicate against a functional btree on eql_v2.ore_block_u64_8_256(col).
What this fixes
- Bare-form range queries (
WHERE col < $1, WHERE col > $1, etc.) now engage the functional ORE index on Supabase and any --exclude-operator-family install.
- Removes the perf cliff observed on benches:
WHERE val < $1 ORDER BY val LIMIT 10 at 100k rows went from 6.3 s → ~885 ms (natural form) / ~1.4 ms (hybrid form with extractor as sort key).
Out of scope
- CASE-style operator bodies that re-introduce CLLW / OPE under the same inlined operators. Deferred to a future release.
ORDER BY col natural-form sort-key matching the functional index. Postgres won't structurally match ORDER BY col against ORDER BY eql_v2.ore_block_u64_8_256(col); the residual Sort node is now fast (each comparison inlined) but it still exists. Hybrid form (ORDER BY eql_v2.ore_block_u64_8_256(col)) avoids the Sort node entirely.
Related
Completed by
#211 (merged 2026-05-18).
Summary
Mirrors #193 for the range operators. The
<,<=,>,>=operators oneql_v2_encryptedwere declaredLANGUAGE plpgsqlwrappingeql_v2.compare()'s priority list (Block ORE → CLLW u64 → CLLW var → OPE → hmac → literal fallback). plpgsql function bodies are never inlined by the planner, so bare-form range predicates (WHERE col < $1) could not structurally match any functional index — including a btree oneql_v2.ore_block_u64_8_256(col). The result was unavoidable seq scans on managed Postgres installs that lackeql_v2.encrypted_operator_class(Supabase, anything that excludes the operator-family).This is Phase 2 of the broader predicate/extractor RFC. Phase 1 (#193) handled equality, inequality, and LIKE/ILIKE. Phase 2 was deferred because it depended on settling the Block-ORE-only contract for range comparisons.
Operators in scope
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE)<(eql_v2_encrypted ×3 overloads)SELECT eql_v2.ore_block_u64_8_256(a) < eql_v2.ore_block_u64_8_256(b)<=(eql_v2_encrypted ×3 overloads)SELECT eql_v2.ore_block_u64_8_256(a) <= eql_v2.ore_block_u64_8_256(b)>(eql_v2_encrypted ×3 overloads)SELECT eql_v2.ore_block_u64_8_256(a) > eql_v2.ore_block_u64_8_256(b)>=(eql_v2_encrypted ×3 overloads)SELECT eql_v2.ore_block_u64_8_256(a) >= eql_v2.ore_block_u64_8_256(b)For the chain to reach index matching, the inner
eql_v2.ore_block_u64_8_256_{eq,neq,lt,lte,gt,gte}functions also have to be inlinable (IMMUTABLE STRICT PARALLEL SAFE).Behaviour change
Range operators no longer fall through
eql_v2.compare()'s priority list. Columns carrying onlyore_cllw_u64_8,ore_cllw_var_8,opf, oropvterms now raise from theore_block_u64_8_256extractor when used with bare-form range operators. Callers in that situation must rewrite to the matching extractor form, e.g.WHERE eql_v2.ore_cllw_u64_8(col) < eql_v2.ore_cllw_u64_8($1::jsonb).This narrower contract is what makes the inlining work — the planner can structurally match the bare-form predicate against a functional btree on
eql_v2.ore_block_u64_8_256(col).What this fixes
WHERE col < $1,WHERE col > $1, etc.) now engage the functional ORE index on Supabase and any--exclude-operator-familyinstall.WHERE val < $1 ORDER BY val LIMIT 10at 100k rows went from 6.3 s → ~885 ms (natural form) / ~1.4 ms (hybrid form with extractor as sort key).Out of scope
ORDER BY colnatural-form sort-key matching the functional index. Postgres won't structurally matchORDER BY colagainstORDER BY eql_v2.ore_block_u64_8_256(col); the residual Sort node is now fast (each comparison inlined) but it still exists. Hybrid form (ORDER BY eql_v2.ore_block_u64_8_256(col)) avoids the Sort node entirely.Related
docs/plans/uniform-predicate-extractor-pairs-rfc.md.docs/upgrading/v2.3.md— caller-facing migration guidance forore_cllw_*/ OPE columns.Completed by
#211 (merged 2026-05-18).