SQLite Forum

Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed?
There are two separate things going on here.

SQLite is asking about a constraint AND about an ordering.

I guess you are already correctly setting an index number and the orderByComsumed flag (the query plan should reflect this) so that your xFilter and xNext functions return rows in the correct order.

What you are observing is consistent with telling SQLite that you can NOT handle the constraint; SQLite codes a check and discards any returned rows that do not match. It will not stop until you tell it that the EOF has been reached (xEOF returns true).

You can speed things up by handling the constraint (field,>) yourself. You need to assign an argv index value to receive the value to compare the field against in the xFilter function and code to return EOF when the constraint is no longer met.

You should also change your estimated cost and estimated rows return values to reflect that you will be retrieving, on average, half as many rows for a greater/less than scan.

If you set the omit flag, SQLite will trust your VT table implementation and not code a constraint check itself.