With the current interface, the xBestIndex function has the possibility of returning "effort" and "result set size" separately, instead of just an aggregate "effort" (which was at the time documented to assume "result set size").
Since we have nearly no native tables here, the question of "relative to native tables" does not pop up. For disk-based native tables and CTree based VT I would tend to assume about equal effort-to-result-set ratios, whereas memory section VTs are probably significantly faster. Since the QP operates with logarithmic cost estimates, I return 1 for a single row unique key retreival and the number of records for a full table scan. Partial key retrievals score a best estimate based on the assumption of equal selectivity per key field (e.g. for a 1000 row table and a three field key, it would be 1 for full key, 10 for the leading 2 key fields, 100 for a single leading key field and 1000 for no key fields = full table scan). This is also factored in when choosing between several indexes that all match the constraints to some degree (2 leading fields form a 3 field key is preferred over 3 leading fields from a 7 field key), and also considering a requested ORDER BY clause. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 24. Juli 2019 11:41 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Re: Estimated Costs and Memory DBs On Wed, Jul 24, 2019 at 10:45 AM Hick Gunter <h...@scigames.at> wrote: > The speed of a virtual table depends on the backing store and software > used to implement it. > [DD] Sure. virtual-tables can also access the disk and do expensive things. [DD] I did say "example given" for my fast-pure-memory-no-decoding case. > We have virtual tables that reference CTree files as well as virtual > tables that reference memory sections here. The advantage is that the VT implementation can adjust it's answers in the > xBestIndex function. [DD] I'm not sure I see your point. My point (and Justin's if I understand him right), is that the relative [DD] costs from tables vs virtual-tables is hard to figure out, which could skew results of the planner [DD] toward sub-optimal plans. [DD] Most of my queries involve only my own virtual tables, so I use arbitrary relative costs, like [DD] 1 if returning a single row via a (virtual) unique index or PK, 2 if returning a range of rows, and 4 for a full table scan. [DD] But these "relative for my vtable costs" are probably completely wrong when mixed with "real" tables, [DD] disk-based or in-memory. There must be some meaningful correlations between all costs for an optimal plan. [DD] Or am I missing something? --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
