Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
9dbae1df75219e2accd8993015ce0ffc |
User & Date: | dan 2017-07-18 17:34:41.909 |
References
2025-05-31
| ||
16:17 | Relax query flattener constraint (3b) and thereby allow flattening the RHS of a LEFT JOIN even if the RHS contains a virtual table. This was previously disallowed by [9dbae1df75219e2a] as a performance optimization. It turns out that the constraint causes performance issues, and we do not have a record of any performance issue that it solves. (check-in: 1ddaa92057 user: drh tags: trunk) | |
Context
2017-07-18
| ||
18:19 | Add the union-vtab extension - still experimental and under active development. (check-in: 948f95046b user: drh tags: trunk) | |
17:34 | Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case. (check-in: 9dbae1df75 user: dan tags: trunk) | |
17:13 | Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. (check-in: d2f0b2e859 user: dan tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
3335 3336 3337 3338 3339 3340 3341 | ** ** (2) The subquery is not an aggregate or (2a) the outer query is not a join ** and (2b) the outer query does not use subqueries other than the one ** FROM-clause subquery that is a candidate for flattening. (2b is ** due to ticket [2f7170d73bf9abf80] from 2015-02-09.) ** ** (3) The subquery is not the right operand of a LEFT JOIN | | > | | 3335 3336 3337 3338 3339 3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351 | ** ** (2) The subquery is not an aggregate or (2a) the outer query is not a join ** and (2b) the outer query does not use subqueries other than the one ** FROM-clause subquery that is a candidate for flattening. (2b is ** due to ticket [2f7170d73bf9abf80] from 2015-02-09.) ** ** (3) The subquery is not the right operand of a LEFT JOIN ** or (a) the subquery is not itself a join and (b) the FROM clause ** of the subquery does not contain a virtual table and (c) the ** outer query is not an aggregate. ** ** (4) The subquery is not DISTINCT. ** ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT ** sub-queries that were excluded from this optimization. Restriction ** (4) has since been expanded to exclude all DISTINCT subqueries. ** |
︙ | ︙ | |||
3541 3542 3543 3544 3545 3546 3547 | ** are processed - there is no mechanism to determine if the LEFT JOIN ** table should be all-NULL. ** ** See also tickets #306, #350, and #3300. */ if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ isLeftJoin = 1; | | | 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 | ** are processed - there is no mechanism to determine if the LEFT JOIN ** table should be all-NULL. ** ** See also tickets #306, #350, and #3300. */ if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ isLeftJoin = 1; if( pSubSrc->nSrc>1 || isAgg || IsVirtual(pSubSrc->a[0].pTab) ){ return 0; /* Restriction (3) */ } } #ifdef SQLITE_EXTRA_IFNULLROW else if( iFrom>0 && !isAgg ){ /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for ** every reference to any result column from subquery in a join, even though |
︙ | ︙ |
Changes to test/fts3join.test.
︙ | ︙ | |||
76 77 78 79 80 81 82 83 84 | ) ON tt.a = rrr } {1 1 abc 2 {} {}} do_execsql_test 3.2 { SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc') } {1 abc 2 abc} finish_test | > > > > > > > > > > > > > > > > > > > > | 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | ) ON tt.a = rrr } {1 1 abc 2 {} {}} do_execsql_test 3.2 { SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc') } {1 abc 2 abc} #------------------------------------------------------------------------- # Test that queries of the form found in test case 4.2 use an automatic # index to avoid running multiple fts queries. # do_execsql_test 4.1 { CREATE VIRTUAL TABLE ft4 USING fts3(x); CREATE TABLE t4(y, z); CREATE INDEX t4y ON t1(y); } do_eqp_test 4.2 { SELECT * FROM t4 LEFT JOIN ( SELECT docid, * FROM ft4 WHERE ft4 MATCH ? ) AS rr ON t4.rowid=rr.docid WHERE t4.y = ?; } { 1 0 0 {SCAN TABLE ft4 VIRTUAL TABLE INDEX 3:} 0 0 0 {SCAN TABLE t4} 0 1 1 {SEARCH SUBQUERY 1 AS rr USING AUTOMATIC COVERING INDEX (docid=?)} } finish_test |