Performance regression in SELECT 1

[penberg]

We have a large performance regression in SELECT 1, which looks to have been caused by two different points in time. The first time I can observe a regression is after #655 merge. I unfortunately am unable to pinpoint the commit as the pull request is not bisectable internally. The regression does get worse over time too, but I did not yet look into it.

[penberg]

The problem is essentially Statement::reset() being extremely costly. I tried to bring back some performance with https://github.com/tursodatabase/limbo/actions/runs/12973129487/job/36181402243?pr=781 (which it does), but it's not complete fix because we're now not resetting the program state correctly.

As discussed with @jussisaurio, we should just have some limit (e.g. 64) of cursor and keep a single array of them in ProgramState and eliminate the hash tables at least.

[penberg]

If someone want to hack on it, this (after making it work) should improve things quite a bit:

index 44d6927..c70812b 100644
--- a/core/vdbe/mod.rs
+++ b/core/vdbe/mod.rs
@@ -193,13 +193,17 @@ impl RegexCache {
     }
 }
 
+pub enum Cursor {
+    BTreeTable(BTreeCursor),
+    BTreeIndex(BTreeCursor),
+    Pseudo(PseudoCursor),
+    Sorter(Sorter),
+}
+
 /// The program state describes the environment in which the program executes.
 pub struct ProgramState {
     pub pc: InsnReference,
-    btree_table_cursors: RefCell<BTreeMap<CursorID, BTreeCursor>>,
-    btree_index_cursors: RefCell<BTreeMap<CursorID, BTreeCursor>>,
-    pseudo_cursors: RefCell<BTreeMap<CursorID, PseudoCursor>>,
-    sorter_cursors: RefCell<BTreeMap<CursorID, Sorter>>,
+    cursors: RefCell<BTreeMap<CursorID, Cursor>>,
     registers: Vec<OwnedValue>,
     last_compare: Option<std::cmp::Ordering>,
     deferred_seek: Option<(CursorID, CursorID)>,```

[jussisaurio]

using an array causes stack overflow. some explorations here #782