3.22 'planner omits unused left joins'

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

3.22 'planner omits unused left joins'

curmudgeon
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 QUERY PLAN SELECT t1.a FROM t1 left 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

Hick Gunter
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Jänner 2018 13:13
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

curmudgeon
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:[hidden email]>
Sent: 29 January 2018 12:24
To: 'SQLite mailing list'<mailto:[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Jänner 2018 13:13
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

Hick Gunter
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Jänner 2018 14:23
An: SQLite mailing list <[hidden email]>
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:[hidden email]>
Sent: 29 January 2018 12:24
To: 'SQLite mailing list'<mailto:[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Jänner 2018 13:13
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

curmudgeon
In reply to this post by Hick Gunter
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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     7     0                    00  Start at 7
1     OpenRead       0     2     0     0              00  root=2 iDb=0; t1
2     Rewind         0     6     0                    00
3       Rowid          0     1     0                    00  r[1]=rowid
4       ResultRow      1     1     0                    00  output=r[1]
5     Next           0     3     0                    01
6     Halt           0     0     0                    00
7     Transaction    0     0     4     0              01  usesStmtJournal=0
8     Goto           0     1     0                    00

sqlite> EXPLAIN SELECT t1.a FROM t1 left join t3 using (a);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     17    0                    00  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     16    0                    00
4       Integer        0     1     0                    00  r[1]=0; init LEFT JOIN no-match flag
5       Rowid          0     2     0                    00  r[2]=rowid
6       SeekGE         2     12    2     1              00  key=r[2]
7         IdxGT          2     12    2     1              00  key=r[2]
8         Integer        1     1     0                    00  r[1]=1; record LEFT JOIN hit
9         Rowid          0     3     0                    00  r[3]=rowid
10        ResultRow      3     1     0                    00  output=r[3]
11      Next           2     7     1                    00
12      IfPos          1     15    0                    00  if r[1]>0 then r[1]-=0, goto 15
13      NullRow        2     0     0                    00
14      Goto           0     8     0                    00
15    Next           0     4     0                    01
16    Halt           0     0     0                    00
17    Transaction    0     0     4     0              01  usesStmtJournal=0
18    Goto           0     1     0                    00

From: Hick Gunter<mailto:[hidden email]>
Sent: 29 January 2018 12:24
To: 'SQLite mailing list'<mailto:[hidden email]>
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 29. Jänner 2018 13:13
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

curmudgeon
In reply to this post by Hick Gunter
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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     7     0                    00  Start at 7
1     OpenRead       0     2     0     0              00  root=2 iDb=0; t1
2     Rewind         0     6     0                    00
3       Rowid          0     1     0                    00  r[1]=rowid
4       ResultRow      1     1     0                    00  output=r[1]
5     Next           0     3     0                    01
6     Halt           0     0     0                    00
7     Transaction    0     0     4     0              01  usesStmtJournal=0
8     Goto           0     1     0                    00


sqlite> EXPLAIN SELECT t1.a FROM t1 left join t3 using (a);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     17    0                    00  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     16    0                    00
4       Integer        0     1     0                    00  r[1]=0; init LEFT JOIN no-match flag
5       Rowid          0     2     0                    00  r[2]=rowid
6       SeekGE         2     12    2     1              00  key=r[2]
7         IdxGT          2     12    2     1              00  key=r[2]
8         Integer        1     1     0                    00  r[1]=1; record LEFT JOIN hit
9         Rowid          0     3     0                    00  r[3]=rowid
10        ResultRow      3     1     0                    00  output=r[3]
11      Next           2     7     1                    00
12      IfPos          1     15    0                    00  if r[1]>0 then r[1]-=0, goto 15
13      NullRow        2     0     0                    00
14      Goto           0     8     0                    00
15    Next           0     4     0                    01
16    Halt           0     0     0                    00
17    Transaction    0     0     4     0              01  usesStmtJournal=0
18    Goto           0     1     0                    00


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 3.22 'planner omits unused left joins'

curmudgeon
In reply to this post by curmudgeon
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users