[Bug] SQL backends: LSI Query plans a sort step because the ORDER BY skips base_pk

[LeeroyHannigan]

Summary

On PostgreSQL and SQLite, a Query on a local secondary index plans a sort step on top of the index scan instead of a pure index walk. Results are correct; the cost is a sort of the rows read for the page, on every page. Global secondary index and base-table page reads on the same builds plan as a single index seek with no sort (#361, #362).

Mechanism

Both SQL backends create one ordering index per index table over the columns (pk, skN, base_pk, base_skN) (ddl.rs, the order_cols list). For a GSI the Query orders by (skN, base_pk, base_skN), which is that index minus the constant pk, so the planner walks it directly. For an LSI, order_by_columns omits base_pk (it is constant inside an LSI partition, because an LSI shares the base table's partition key), so the ORDER BY is (skN, base_skN). Neither planner can prove base_pk is constant within the partition, so it cannot use the index order for base_skN:

PostgreSQL: Incremental Sort over the index scan (measured: 3 groups, 111 rows in, 37 kB quicksort for a page of 101 rows in a 50,000 row partition; 119 buffers, 0.42 ms; the equivalent GSI page is 104 buffers with no sort node).
SQLite: SEARCH USING INDEX ... (pk=? AND sk_n<?) plus USE TEMP B-TREE FOR LAST TERM OF ORDER BY.

The sort is bounded by the LIMIT (the planner stops after enough rows for the page), so the cost is per page, not per partition. The reverse direction was the one measured; the forward direction emits the same ORDER BY shape and is expected to plan the same way.

Fix

Add base_pk to the LSI ORDER BY and cursor columns, between skN and base_skN, so the ORDER BY matches the physical index exactly. Within an LSI partition base_pk equals pk, so the emitted order and every page boundary are unchanged; only the plan changes. The cursor builder and ORDER BY builder share one column list per backend after #362, so this is one branch in order_by_columns on each backend (crates/storage-postgres/src/data/query_scan.rs, crates/storage-sqlite/src/data/query_scan.rs) plus the corresponding bind. An alternative is a second index without base_pk for LSI tables, which costs storage for no gain over the first option.

Verification: EXPLAIN (ANALYZE, BUFFERS) on an LSI page read shows an Index Scan (or Index Scan Backward) with the row comparison in the Index Cond and no Sort node; SQLite EXPLAIN QUERY PLAN shows no temp B-tree; the wire ordering tests from #361 and #362 stay green.

Notes

Found while measuring query plans for #362 on servers built from fix/hash-only-gsi-reverse-cursor and its parent; the LSI SQL is identical on both and on main.