This is an EXPLAIN QUERY PLAN of one of the statements used to generate a report:
sele order from deta ---- ------------- ---- ---- 3 0 0 SCAN TABLE sgb_val_hist_ticket AS tx VIRTUAL TABLE INDEX 1: (~0 rows) 3 1 1 SCAN TABLE tag_141190 AS ta VIRTUAL TABLE INDEX 1: (~0 rows) 3 2 2 SCAN TABLE sgb_dd_prize_level AS pl VIRTUAL TABLE INDEX 1: (~0 rows) 3 0 0 USE TEMP B-TREE FOR GROUP BY 3 0 0 EXECUTE LIST SUBQUERY 4 3 0 0 EXECUTE LIST SUBQUERY 4 3 0 0 EXECUTE LIST SUBQUERY 4 3 0 0 EXECUTE LIST SUBQUERY 4 3 0 0 EXECUTE LIST SUBQUERY 4 3 0 0 EXECUTE LIST SUBQUERY 4 4 0 0 SCAN TABLE sgb_val_ticket AS tx VIRTUAL TABLE INDEX 1: (~0 rows) 4 1 1 SCAN TABLE tag_141190 AS ta VIRTUAL TABLE INDEX 1: (~0 rows) 4 2 2 SCAN TABLE sgb_dd_prize_level AS pl VIRTUAL TABLE INDEX 1: (~0 rows) 4 0 0 USE TEMP B-TREE FOR GROUP BY 4 0 0 EXECUTE LIST SUBQUERY 5 4 0 0 EXECUTE LIST SUBQUERY 5 4 0 0 EXECUTE LIST SUBQUERY 5 4 0 0 EXECUTE LIST SUBQUERY 5 4 0 0 EXECUTE LIST SUBQUERY 5 4 0 0 EXECUTE LIST SUBQUERY 5 2 0 0 COMPOUND SUBQUERIES 3 AND 4 USING TEMP B-TREE (UNION) 1 0 0 SCAN SUBQUERY 2 (~200 rows) 1 0 0 USE TEMP B-TREE FOR GROUP BY 0 0 0 SCAN SUBQUERY 1 AS t1 (~100 rows) 0 1 1 SCAN TABLE sgb_dd_prize_level AS pl2 VIRTUAL TABLE INDEX 1: (~0 rows) 0 2 2 SCAN TABLE sgb_dd_draw AS dr VIRTUAL TABLE INDEX 1: (~0 rows) 0 0 0 USE TEMP B-TREE FOR ORDER BY There are several CASE...END clauses and a UNION (of transaction data) cross joined to in-memory tables (of configuration data). Note that the sgb_dd_prize_level table is referenced by INDEX 1 (which is nice to know) several times, but no indication of the fields used is given. The following is clipped from the EXPLAIN of the same statement where select #3 is implemented: 24 VColumn 7 27 45 00 sgb_val_hist_ticket.record 25 Integer 1 43 0 00 NULL 26 Integer 1 44 0 00 NULL 27 VFilter 8 52 43 00 NULL 28 VColumn 8 5 50 00 tag_141190.game_no 29 ... 30 VColumn 7 0 45 00 sgb_val_hist_ticket.jurisdiction_no 31 SCopy 50 46 0 00 NULL 32 VColumn 8 8 47 00 tag_141190.draw_no 33 VColumn 8 10 48 00 tag_141190.prize_share_no 34 Integer 1 43 0 00 NULL 35 Integer 4 44 0 00 NULL 36 VFilter 9 51 43 ^E 00 NULL 37 ... 38 ... 39 ... 40 ... 41 VColumn 9 0 56 00 sgb_dd_prize_level.jurisdiction_no #24 tells us that table 7 is "sgb_val_hist_ticket" #28 tells us that table 8 is "tag_141190" #41 tells us that table 9 is "sgb_dd_prize_level" "sgb_val_hist_ticket" is a large CTree based table "tag_141190" is a "tag table" that splits binary data into a virtual table row. "sgb_dd_prize_level" is an in-memory table #27 is the VFilter call for table 8 with parameters passed beginning in register 43 #25 loads the INDEX NUMBER into register 43 #26 loads the argc of 1 into register 44 #24 loads the argv[0] into register 45 (record BLOB) So the tag table gets a binary field as the first and only user argument to the VFilter method. #36 is the VFilter call for table 9 with parameters passed beginning in register 43 #34 loads the INDEX NUMBER into register 43 #35 loads the argc of 4 into register 44 #30 loads the argv[0] into register 45 (jurisdiction_no) #31 loads the argv[1] into register 46 (game_no) copied from register 50 #32 loads the argv[2] into register 47 (draw_no) #33 loads the argv[3] into register 48 (prize_share_no) This compares favorably with the index definition of the table, so all is well. Contrast this with the following excerpt from a "less performing" version of the same statement: 20 Integer 0 38 0 00 NULL 21 Integer 0 39 0 00 NULL 22 VFilter 7 57 38 ^E 00 NULL 23 VColumn 7 0 40 00 sgb_dd_prize_level.jurisdiction_no 24 Integer 1 38 0 00 NULL 25 Integer 1 39 0 00 NULL 26 VFilter 8 56 38 ^C 00 NULL 27 VColumn 8 27 46 00 sgb_val_hist_ticket.record 28 Integer 1 44 0 00 NULL 29 Integer 1 45 0 00 NULL 30 VFilter 9 55 44 00 NULL 31 VColumn 9 5 51 00 tag_141190.game_no 32 VColumn 7 1 52 00 sgb_dd_prize_level.game_no 33 Ne 52 54 51 collseq(BINARY) 6b NULL 34 Ne 36 54 51 collseq(BINARY) 6b NULL 35 VColumn 9 8 53 00 tag_141190.draw_no 36 VColumn 7 2 54 00 sgb_dd_prize_level.draw_no 37 Ne 54 54 53 collseq(BINARY) 6b NULL 38 VColumn 9 10 55 00 tag_141190.prize_share_no 39 VColumn 7 3 56 00 sgb_dd_prize_level.prize_share_no 40 Ne 56 54 55 collseq(BINARY) 6b NULL #22 is the VFilter call for sgb_dd_prize_level by INDEX 0 (physical order) with 0 constraints = FULL TABLE SCAN #26 is the VFilter call for sgb_val_hist_ticket by INDEX 1 with 1 constraint (jurisdiction_no, which happens to be constant) = FULL TABLE SCAN #30 is the VFilter call for tag_141190 required to access the fields #31..#40 perform the rest of the join on (jurisdiction_no, game_no, draw_no, prize_share_no) There is no doubt that performing a full cross join will take significantly more time... BTW: in the first excerpt, there are no checks of the returned fields against the constraints; this shows that the "omit" flags were set by the xBestIndex method (and the virtual table had better guarantee that only matching rows will be returned, or else...). Hope this helps Gunter -----Ursprüngliche Nachricht----- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Montag, 18. November 2013 09:44 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Using EXPLAIN to validate vtable xFilter [WAS: EXPLAIN QUERY PLAN] On Mon, Nov 18, 2013 at 8:46 AM, Hick Gunter <h...@scigames.at> wrote: > I use EXPLAIN to make sure that my virtual tables' xFilter methods get > called with the proper parameters. > Hi Hick, Would you mind sharing some details on this please? As a heavy vtable user, and those vtables implementing custom indexes (sometimes providing ordering too), I'm interested in what you mean exactly by this. We've had a few bugs in the past around vtable indexing, so if there's a technique to detect those you'd be willing to share, that'd be great. Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
