NexusDB

What you have to realize is that there are optimizations that the query engine cannot apply to a live cursor. For a live query, the engine can only use one index to generate the result, as you can't have two indices applied at the same time. For canned queries, the engine can apply any number of index ranges one after another and then combine the results into one.

Generally, for live cursors, the engine tries to apply the index and range with the highest probability of limiting the result set and then filters for any conditions that are not caught by the applied index and range using a separate expression filter, which is then evaluated dynamically for each row. For this reason, in queries where you have multiple disjunctions and/or conjunctions that fit indices, the canned version of the query will typically be more efficient. The difference becomes more and more pronounced as you add constraints to the live result.

Say you do something along the lines of

SELECT * FROM SomeTable WHERE B > 3 AND B < 1000 ORDER BY A;

On a live result, even if you have an index on SomeTable.B, the query engine can't use it to limit the result because the ORDER BY stipulates that the index on A must be the active one in the returned live cursor. For a canned result by contrast, the engine will first use the B index when selecting the rows for the result and then sort the result - now stored in a temp table - manually on A.