For your JOIN case, I would expect the following "conversation" (simplified and based on the original cost based QP, the NGQP may be doing something else)
xBestIndex( table A, no constraints) -> Full table scan FTS(A), cost = CARD(A), count=CARD(A) xBestIndex( table A, OID) -> primary key lookup PK(A), cost = LOG(CARD(A)), count=1, unique flag set xBestIndex( table B, no constraints) -> FTS B xBestIndex( table B, Owner) -> partial table scan PTS(B, Owner); since this seems to be unsupported, this is also FTS(B) Now it considers the possible query plans Lookup in table A, followed by a full table scan of table B: LOG(CARD(A)) * CARD(B) (because there is no index on B.Owner) Full table scan of table B, followed by lookup in table A: CARD(B) * LOG(CARD(A)) Since the cost estimate is the same, it is free to choose either plan, apparently the second one. In both cases, a full table scan of table B is performed. Why does this cause a problem in the second query plan? Note: you can use the keyword "cross" to force the desired order. Does your query work then? What is the difference in the way that table B is handled? -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von David Fletcher Gesendet: Sonntag, 29. Juli 2018 18:42 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex/xFilter question First, thanks in advance for any help offered. I'm pretty comfortable using sqlite but just now starting to develop with virtual tables. I'm running into troubles and I'm pretty sure it's because my mental model of sqlite is wimpy. I'm trying to build a tool that interfaces to C++ objects in memory that are basically arranged as a tree (or graph or mesh). For example, let's suppose I have a class called A and objects of this class can own any number of B objects. Every A and B object has an ObjectID. Each object also has an owner. In C/C++ I would do this sort of thing: A* pA = GetOjectGivenID(12345); B* pB; BIterator iter(pA); while ((pB = iter.Next()) != 0) { assert(pB->pOwner == pA); } That's simple enough. I'm trying to achieve the same effect using virtual tables, each of which follow this basic pattern: create table A(OID integer primary key, Owner integer, Attr1 integer, Attr text, ...) without rowid; That is, the first column of every table is OID, the object ID. For most of the tables, there's also a column called Owner and maybe other columns that act as foreign keys, too. (I haven't actually gone to the trouble to denote the columns are foreign keys just yet.) Everything works when I have just a single table with SQL like this: select * from A where A.OID == 12345; The statement will walk through all of the rows. Behind the scenes, a C++ iterator is doing all of the work. I'm struggling with joins. This statement doesn't work in my application: select * from A join B on A.OID == B.Owner where A.OID == 12345; The xBestIndex function is called a couple of times if this is the first time the table has been defined. (xBestIndex is called fewer times if the table has been seen before. I'm guessing sqlite is caching some info.) When the xFilter function is called, it's being handed the virtual table for B, not A. Because there's no object ID to act as the iterator's source, the xFilter function ends in failure. I'm struggling to find aConstraintUsage settings that will cause A, the left-hand table in my mental model, to be presented before B. I'm setting the estimated number of rows correctly, I think. I think the argvIndex values are being set correctly, too, I think. At least sqlite isn't complaining about malformed expressions. But, something is missing. I spent some time looking at other examples but most of them seem to use other tables within sqlite to hold the data vs. objects in memory. I did stumble across https://osquery.io/ yesterday, which looks interesting and useful. It's also somewhat complex and I haven't delved into its xBestIndex/xFilter implementations just yet. I think I'd benefit from looking at any application that uses C/C++ objects in memory plus iterators to traverse objects in a tree/graph/mesh/etc. Can anyone point out projects like this? I think I'd also benefit with documentation that shows more of what's happening behind the scenes with xBestIndex/xFilter. Can anyone point me to documentation that is more detailed? I've looked at the sqlite docs, Jay Kreibich's book& Mike Owens book and "Query Anything" documentation. Thanks, David _______________________________________________ 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
