On Tue, Mar 28, 2017 at 10:26 AM, Max Vlasov <max.vla...@gmail.com> wrote:
> I sometimes use virtual tables to implement some kind of one-to-many > output. One of examples mentioned previously was the comma list virtual > table when a field containing comma-separated values might be used to > output rows of values from this list. Other example - performing > regexp-like query against a text and outputting the result columns. But > this involves assuming that some columns of the virtual table are required > "input" parameters while the other is "output". For example, for comma > list, the required parameter is the list, the output - extracted values. > The consequence of this trick is that this virtual table is not fully > functional one, so you can't query select * from it, it's useful only by > providing where or "join .. on" clause containing the required "input" > parameters. > > I usually encouraged sqlite to provide all required input parameters by > reporting a "cheap" value of estimatedCost when I recognized my "input" > parameters in xBestIndex call and providing an "expensive" values for any > other cases. This usually worked for simple and complex cases until > 3.8.0.0. It seems the version introduced Next-Generation Query Planner and > I noticed that in some complex cases my cheap-expensive recommendations > were ignored, so even when I noticed in the debugger that cheap value was > provided for the right index and expensive for all other cases, the > following xFilter provided not all values required probably deciding it > knew better :). Before this I used cheap value 1 and expensive value 10000. > Testing (3.17.0) revealed that in some cases multiplying expensive by x100 > helped, but other cases required increasing this value even more. > > So, what is the maximum reasonable value of estimatedCost that will not > turn sqlite into possible overflow errors while telling at the same time > that I consider some variant very, very expensive? Or maybe changing cheap > from 1 to 0 will do the trick? > FWIW, I've often wondered about the cost estimates of real tables versus virtual tables, especially since many vtables implementations don't involve real IO but pure in-memory computations. There's very little advice or documentation on this important subject, and Max's email reveals that empirical testing leading to ad-hoc heuristics are vulnerable to breaking when SQLite itself evolves. More guidance and perhaps even some "normative" documentation is needed IMHO. Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
