Optimize point queries with integer keys

[penberg]

SQLite has a SeekRowid bytecode for point queries for tables with integer keys:

sqlite> EXPLAIN SELECT id FROM users WHERE id = 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     7     0                    0   Start at 7
1     OpenRead       0     2     0     0              0   root=2 iDb=0; users
2     Integer        1     1     0                    0   r[1]=1
3     SeekRowid      0     6     1                    0   intkey=r[1]
4     Rowid          0     2     0                    0   r[2]=users.rowid
5     ResultRow      2     1     0                    0   output=r[2]
6     Halt           0     0     0                    0
7     Transaction    0     0     2     0              1   usesStmtJournal=0
8     Goto           0     1     0                    0

In Limbo, we end up doing a full table scan instead:

limbo> EXPLAIN SELECT id FROM users WHERE id = 1;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     12    0                    0   Start at 12
1     OpenReadAsync      0     2     0                    0   root=2
2     OpenReadAwait      0     0     0                    0
3     RewindAsync        0     0     0                    0
4     RewindAwait        0     -4    0                    0
5       RowId            0     1     0                    0   r[1]=users.rowid
6       Ne               1     2     9                    0   if r[1]!=r[2] goto 9
7       RowId            0     3     0                    0   r[3]=users.rowid
8       ResultRow        3     1     0                    0   output=r[3]
9     NextAsync          0     0     0                    0
10    NextAwait          0     4     0                    0
11    Halt               0     0     0                    0
12    Transaction        0     0     0                    0
13    Integer            1     2     0                    0   r[2]=1
14    Goto               0     1     0                    0