On 10/4/17, dave <d...@ziggurat29.com> wrote:
> 1) is there an orthodox method of indicating that a query plan request from
> xBestIndex is a no-go,
Give that plan a huge estimatedCost.
As a backup, in the exceedingly unlikely event that SQLite chooses
your no-go plan in spite of the huge estimatedCost, also provide a
unique idxNum and if xFilter sees that idxNum, have xFilter throw an
error with error message text that is something like "query planner
could not find an acceptable solution".
> 2) am I using the 'pIdxInfo->aConstraintUsage[nIdx].omit' variable
> appropriately? My interpretation is that means 'the vtable can fully handle
> the constraint, sqlite does not need to do a double-check on it afterwards'.
> And I guess as a bonus 4th question: What is the established orthodoxy in
> picking estimatedCost anyway?
It is not overly sensitive to the scale of your cost estimates. For
best results, let 1.0 be the same time as required to do a single
b-tree lookup on a one-page b-tree. In other words, the cost of
reading a single page of the database file from disk into cache and
then doing a little decoding work to perform a binary search among the
approximately 200 keys on that page.
You don't know how to estimate that? Then guess. As long as the
relative costs for other invocations of xBestIndex on the same virtual
table are in reasonable proportion, everything should work fine.
In the source tree, go to the ext/misc folder and grep for
"estimatedCost" to see how some of the extension virtual tables do it.
D. Richard Hipp
sqlite-users mailing list