Description
For INSERT (insert_single, insert_batch_10, insert_no_explicit_pk) and UPDATE (update_pk, update_filtered_range, update_indexed_column, update_multi_column), our codegen — after the Insert (or Delete+Insert rebuild) opcode — issues a fresh SeekRowid back into the table cursor and re-reads columns via Column to construct the key for IdxInsert, instead of reusing the registers that were already populated (from the original INSERT/UPDATE values) before the row write happened.
Oracle (sqlite3) computes the index key via SCopy/IntCopy straight from the live registers already holding those values — no reseek, no re-read from disk. Ours pays an extra btree seek + page touch per written row for every table that has any index, on every INSERT and UPDATE.
Visible unrolled in insert_batch_10's opcode dump (repeated per row, e.g. addr 14-17 and 29-32 pattern).
Complexity
Estimate: medium
Reasoning: Requires codegen changes in src/codegen/index_maintenance.rs (both insert and update paths) to track and reuse the registers already holding column values instead of re-fetching. Needs care to handle cases where a column feeding an index is computed indirectly (e.g. via n = n + 1 expressions) vs. a literal, and to not break correctness for multi-column indexes.
Context
Found via a top-down opcode-level sweep comparing our codegen's VDBE output against libsqlite3's EXPLAIN output across the 15 CRUD scenarios in tests/performance/crud.rs. Likely the most systemic and mechanical fix among findings — same root cause affects both INSERT and UPDATE paths identically. Related to #659 (seek-path inlining fix) only in that both touch seek cost; this ticket is about eliminating an unnecessary seek in codegen, not making an existing necessary one faster.
Acceptance Criteria
Additional Notes
Consider fixing INSERT and UPDATE in separate, smaller PRs if the register-lifetime tracking differs meaningfully between the two codegen paths — but file as one ticket since root cause and fix shape are the same.
Description
For INSERT (
insert_single,insert_batch_10,insert_no_explicit_pk) and UPDATE (update_pk,update_filtered_range,update_indexed_column,update_multi_column), our codegen — after theInsert(orDelete+Insertrebuild) opcode — issues a freshSeekRowidback into the table cursor and re-reads columns viaColumnto construct the key forIdxInsert, instead of reusing the registers that were already populated (from the original INSERT/UPDATE values) before the row write happened.Oracle (sqlite3) computes the index key via
SCopy/IntCopystraight from the live registers already holding those values — no reseek, no re-read from disk. Ours pays an extra btree seek + page touch per written row for every table that has any index, on every INSERT and UPDATE.Visible unrolled in
insert_batch_10's opcode dump (repeated per row, e.g. addr 14-17 and 29-32 pattern).Complexity
Estimate: medium
Reasoning: Requires codegen changes in
src/codegen/index_maintenance.rs(both insert and update paths) to track and reuse the registers already holding column values instead of re-fetching. Needs care to handle cases where a column feeding an index is computed indirectly (e.g. vian = n + 1expressions) vs. a literal, and to not break correctness for multi-column indexes.Context
Found via a top-down opcode-level sweep comparing our codegen's VDBE output against libsqlite3's
EXPLAINoutput across the 15 CRUD scenarios intests/performance/crud.rs. Likely the most systemic and mechanical fix among findings — same root cause affects both INSERT and UPDATE paths identically. Related to #659 (seek-path inlining fix) only in that both touch seek cost; this ticket is about eliminating an unnecessary seek in codegen, not making an existing necessary one faster.Acceptance Criteria
IdxInsertkeys from registers already computed before/at theInsertopcode, without a follow-upSeekRowid/Columnre-readDelete+InsertrebuildEXPLAINfor these scenarios shows the redundant seek/column-read opcodes removedcargo bench --bench crudshows improvement on insert/update scenarios for tables with at least one indexAdditional Notes
Consider fixing INSERT and UPDATE in separate, smaller PRs if the register-lifetime tracking differs meaningfully between the two codegen paths — but file as one ticket since root cause and fix shape are the same.