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,
Advertising
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'. Yes. Correct. > 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 d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
