Hi folks,
I am trying to fully understand the impact and correct use of a few subtle
features related to virtual tables' the xBestIndex mechanism, and their
correct use. Here are my current beliefs:
* pIdxInfo->estimatedCost
obviously the cost of the proposed plan; a metric of the 'viscosity' of
the table when traversing through xNext relative to other tables and
especially to filesystem access
* pIdxInfo->estimatedRows
obviously the approximate number of rows that a proposed plan will return.
But less obvious to me is how this materially affects the query plan,
especially relative to pIdxInfo->estimatedCost
and a little bit with respect to:
* pIdxInfo->idxFlags
when the SQLITE_INDEX_SCAN_UNIQUE is set. Isn't setting
pIdxInfo->estimatedRows to 0 or 1 enough to communicate this same
information?
Anyway, I am dutifully setting both estimatedRows and idxFlags in cases
where I have a 0-or-1-result table (I have several of these), and I am also
estimatedRows to LLONG_MAX along with estimatedCost to DBL_MAX in cases
where a plan can never be executed (btw I would respectfully suggest perhaps
using a bit in idxFlags to communicate 'never use this plan, it will never
work').
I haven't had any ill effects doing the above, but wonder if that is
'correct'. Also, it would be interesting just to know what the material
effect of estimatedRows and idxFlags is, so that I can maybe use them more
effectively.
Any thoughts or corrections to my thinking? Thanks in advance; cheers!
-dave
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
