perf: range scans check bounds via per-row comparison opcode instead of baking bound into seek

[iheitlager]

Description

For range-scan scenarios (read_indexed_range, update_filtered_range, delete_filtered_range), our codegen checks the upper/lower bound manually per row via an explicit IdxCompareGT/Gt opcode plus a Goto, whereas oracle bakes the bound directly into the seek opcode itself (e.g. SeekGT ... p4=1) followed by a plain Next/Rewind loop with no per-row comparison opcode at all.

This is a smaller cost per row than #661/#663/#664, but it's paid on every row scanned, so it compounds on large range scans.

Complexity

Estimate: small
Reasoning: Codegen change to fold the range predicate into the seek opcode's own bound-check behavior (as SQLite's VDBE seek opcodes support), removing the separate per-row comparison + branch. Scoped to src/codegen/select/range_scan.rs (and its UPDATE/DELETE equivalents if they share the codegen path).

Context

Found via the top-down opcode-level sweep (codegen vs oracle EXPLAIN) across tests/performance/crud.rs's scenarios. Lowest priority of the batch (#661, #663, #664, this) since per-row cost is smaller, but flagged since it's a straightforward, well-understood VDBE idiom mismatch.

Acceptance Criteria

• Range-scan codegen (read/update/delete) uses a bound-carrying seek opcode instead of a separate per-row IdxCompareGT/Gt+Goto
• Opcode comparison against oracle's EXPLAIN shows the per-row comparison opcode removed for these scenarios
• cargo bench --bench crud -- read_indexed_range / update_filtered_range / delete_filtered_range shows measurable improvement
• Existing range-scan correctness/parity tests still pass

[iheitlager]

Merged in #672 (f70e92b).

Findings changed scope from the original estimate: real oracle EXPLAIN (not EXPLAIN QUERY PLAN) showed SELECT's range-seek path was already near-optimal — no per-row bound-check to fold into the seek. The actual gap was UPDATE/DELETE never using the index at all for range predicates.

• UPDATE: fixed. try_compile_range_row_seek (range_scan.rs) wired into update.rs. update_filtered_range ~148ms → ~107ms (~28% faster).
• DELETE: prototyped correct, but reverted — the required two-pass materialization (this engine's cursors can't safely self-mutate mid-scan, unlike real sqlite3's save/restore) regressed delete_filtered_range by ~10% on this benchmark's highly-selective predicate over a small table. Needs cursor position save/restore first; opening as a follow-up rather than shipping a regression.

spend: ~2x the "small" estimate — the premise didn't survive contact with real EXPLAIN, and the actual fix was full-sized for one of the two statements.

---

🤖 Analysis by Claude