SQLite Forum

Documentation for where query optimizations & relation algebra occurs?
Login
My hunch is that you should be doing the analysis inside of your virtual table implementation.

The xBestIndex method will be called at least twice

-) Without usable constraints (i.e. full table scan). Return the number of rows in the table.

-) With the constraints ((col3, EQ), (col4, EQ)). Return the expected number of rows that will match both constraints. E.g. if there are 10 discrete values for each column, return number of rows divided by 100.

You need to encode whatever xBestIndex comes up with in the index number nd index string return values, and in a way that your xFilter function understands. You also need to tell SQLite, which constraints are used, their order in the argument list, and if you can guarantee them (so SQLite can omit the value check)

The xFilter method will be called with the index number and index string belonging to the query plan that SQLite selected for execution, plus the comparison values.

This is where you do the magic of obtaining a "short list" of the matching records which SQLite steps through by calling the xNext method.

This way, you are letting SQLite do the heavy lifting of parsing SQL and creating a query plan, while you can concentrate on getting 
the smallest number of rows to operate on in the fastest possible way.

BTW: For the query you show, xBestIndex arguments will include the request to ORDER BY col2 to discern between keeping a single running total per group (ordered presentation), or building a temporary btree (unordered presentation).