In the following example, where I have a simple table with an expression index and query: ``` DROP TABLE IF EXISTS test; CREATE TABLE test(x text); CREATE INDEX testIndex ON test(length(x)); EXPLAIN SELECT length(x) FROM test INDEXED BY testIndex; ``` It uses the execution plan below, which opens both the table as well as index and do a DeferredSeek of the table. Note, it doesn't actually access anything from the main table (i.e. it doesn't retrieve the value and execute `length` on it - it merely returns the precomputed value from the index (good) ), but it still seeks into the main table: ``` addr opcode p1 p2 p3 p4 p5 comment 0 Init 0 9 0 0 Start at 9 1 OpenRead 0 2 0 1 0 root=2 iDb=0; test 2 OpenRead 1 3 0 k(2,,) 0 root=3 iDb=0; testIndex 3 Rewind 1 8 1 0 0 4 DeferredSeek 1 0 0 0 Move 0 to 1.rowid if needed 5 Column 1 0 1 0 r[1]= 6 ResultRow 1 1 0 0 output=r[1] 7 Next 1 4 0 1 8 Halt 0 0 0 0 9 Transaction 0 0 68 0 1 usesStmtJournal=0 10 Goto 0 1 0 0 ``` IF I however change the index to include the column itself: ``` CREATE INDEX testIndex ON test(length(x), x); ``` It no longer does that, and instead changes the execution plan to this: ``` addr opcode p1 p2 p3 p4 p5 comment 0 Init 0 7 0 0 Start at 7 1 OpenRead 1 3 0 k(3,,,) 0 root=3 iDb=0; testIndex 2 Rewind 1 6 1 0 0 3 Column 1 0 1 0 r[1]= 4 ResultRow 1 1 0 0 output=r[1] 5 Next 1 3 0 1 6 Halt 0 0 0 0 7 Transaction 0 0 71 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 ``` This is much more ideal since it doesn't do the deferred seek and immediately return the computer result stored in the table - which it already has. However, note that it again doesn't actually read the second column I added, nor obviously recompute anything from it, it merely needs to be present in order for the index to return the first column directly. Is it possible to remove this restriction so that the query engine can use the second execution plan without that second unused column? Related to this, even with the 2nd column present, the optimizer doesn't recognize that testIndex is a value way to perform: ``` SELECT length(x) FROM test ``` without explicitly forcing the `INDEXED BY`