Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
>CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t2 using (a); 0|0|0|SCAN TABLE t1 EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) >Why is t3 with its unique index (on a) not omitted from the last query plan? I see this was added to the commit history timeline yesterday “Update the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint.” which will hopefully solve the problem. In the meantime changing line 3 to CREATE TABLE t3 (a INTEGER NOT NULL) does result in table t3 being omitted from the explain query plan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
Sorry, meant to post this SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t3 (a INTEGER); sqlite> CREATE UNIQUE INDEX ndx on t3(a); sqlite> EXPLAIN SELECT t1.a FROM t1 left join t2 using (a); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 7 000 Start at 7 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 Rewind 0 6 000 3 Rowid 0 1 000 r[1]=rowid 4 ResultRow 1 1 000 output=r[1] 5 Next 0 3 001 6 Halt 0 0 000 7 Transaction0 0 4 0 01 usesStmtJournal=0 8 Goto 0 1 000 sqlite> EXPLAIN SELECT t1.a FROM t1 left join t3 using (a); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 17000 Start at 17 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 OpenRead 2 5 0 k(2,,) 02 root=5 iDb=0; ndx 3 Rewind 0 16000 4 Integer0 1 000 r[1]=0; init LEFT JOIN no-match flag 5 Rowid 0 2 000 r[2]=rowid 6 SeekGE 2 122 1 00 key=r[2] 7 IdxGT 2 122 1 00 key=r[2] 8 Integer1 1 000 r[1]=1; record LEFT JOIN hit 9 Rowid 0 3 000 r[3]=rowid 10ResultRow 3 1 000 output=r[3] 11 Next 2 7 100 12 IfPos 1 15000 if r[1]>0 then r[1]-=0, goto 15 13 NullRow2 0 000 14 Goto 0 8 000 15Next 0 4 001 16Halt 0 0 000 17Transaction0 0 4 0 01 usesStmtJournal=0 18Goto 0 1 000 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t2 (a INTEGER PRIMARY KEY); sqlite> CREATE TABLE t3 (a INTEGER); sqlite> CREATE UNIQUE INDEX ndx on t3(a); sqlite> EXPLAIN SELECT t1.a FROM t1 left join t2 using (a); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 7 000 Start at 7 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 Rewind 0 6 000 3 Rowid 0 1 000 r[1]=rowid 4 ResultRow 1 1 000 output=r[1] 5 Next 0 3 001 6 Halt 0 0 000 7 Transaction0 0 4 0 01 usesStmtJournal=0 8 Goto 0 1 000 sqlite> EXPLAIN SELECT t1.a FROM t1 left join t3 using (a); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 17000 Start at 17 1 OpenRead 0 2 0 0 00 root=2 iDb=0; t1 2 OpenRead 2 5 0 k(2,,) 02 root=5 iDb=0; ndx 3 Rewind 0 16000 4 Integer0 1 000 r[1]=0; init LEFT JOIN no-match flag 5 Rowid 0 2 000 r[2]=rowid 6 SeekGE 2 122 1 00 key=r[2] 7 IdxGT 2 122 1 00 key=r[2] 8 Integer1 1 000 r[1]=1; record LEFT JOIN hit 9 Rowid 0 3 000 r[3]=rowid 10ResultRow 3 1 000 output=r[3] 11 Next 2 7 100 12 IfPos 1 15000 if r[1]>0 then r[1]-=0, goto 15 13 NullRow2 0 000 14 Goto 0 8 000 15Next 0 4 001 16Halt 0 0 000 17Transaction0 0 4 0 01 usesStmtJournal=0 18Goto 0 1 000 From: Hick Gunter<mailto:h...@scigames.at> Sent: 29 January 2018 12:24 To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins' The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit unique index, whose lookup is printed. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 13:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 3.22 'planner omits unused left joins' SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a sqlite> INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE sqlite> INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left sqlite> join t2 using (a); 0|0|0|SCAN TABLE t1 sqlite> EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) Why is t3 with its unique index on a not omitted from the last query plan? ___ 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 ___
Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
I am still stuck with 3.7.14.1 here, so maybe the QP is different WRT joins that are not required to satisfy the select field list. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 14:23 An: SQLite mailing list Betreff: Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins' Are you sure you’re using 3.22 Gunter? I can’t see any trace of t2 in the first explain (with query plan omitted). From: Hick Gunter<mailto:h...@scigames.at> Sent: 29 January 2018 12:24 To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins' The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit unique index, whose lookup is printed. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 13:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 3.22 'planner omits unused left joins' SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a sqlite> INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE sqlite> INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left sqlite> join t2 using (a); 0|0|0|SCAN TABLE t1 sqlite> EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) Why is t3 with its unique index on a not omitted from the last query plan? ___ 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 ___ 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
Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
Are you sure you’re using 3.22 Gunter? I can’t see any trace of t2 in the first explain (with query plan omitted). From: Hick Gunter<mailto:h...@scigames.at> Sent: 29 January 2018 12:24 To: 'SQLite mailing list'<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins' The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit unique index, whose lookup is printed. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 13:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 3.22 'planner omits unused left joins' SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a sqlite> INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE sqlite> INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left sqlite> join t2 using (a); 0|0|0|SCAN TABLE t1 sqlite> EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) Why is t3 with its unique index on a not omitted from the last query plan? ___ 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] 3.22 'planner omits unused left joins'
The "unused left join" is not "unused". Use "explain" instead of "explain query plan" and you will see that the left join is performed in both cases. The difference is that t2 has an INTEGER PRIMARY KEY and a lookup there is apparently not printed out in the query plan. Table t3 has an explicit unique index, whose lookup is printed. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 29. Jänner 2018 13:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] 3.22 'planner omits unused left joins' SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE t1 (a INTEGER PRIMARY KEY); CREATE TABLE t2 (a sqlite> INTEGER PRIMARY KEY); CREATE TABLE t3 (a INTEGER); CREATE UNIQUE sqlite> INDEX ndx on t3(a); EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left sqlite> join t2 using (a); 0|0|0|SCAN TABLE t1 sqlite> EXPLAIN QUERY PLAN SELECT t1.a FROM t1 left join t3 using (a); 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t3 USING COVERING INDEX ndx (a=?) Why is t3 with its unique index on a not omitted from the last query plan? ___ 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
