Dinu wrote:
Hi all,
I've ran into an optimisation problem with a double-left join that
works as
an "either" clause.
The query is as follows:
SELECT *
FROM
a
LEFT JOIN
b ON <cond>
LEFT JOIN
c ON <cond>
WHERE
b.someId IN (1,2,3) OR
c.someId IN (4,5)
This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
However, the semantically equivalent:
SELECT *
FROM
a
LEFT JOIN
b ON <cond> AND b.someId IN (1,2,3)
LEFT JOIN
c ON <cond>AND c.someId IN (4,5)
WHERE
b.someId IS NOT NULL OR
c.someId IS NOT NULL
Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY
Hello, the discussion about whether the two queries are equivalent is
not satisfactory to me. What Keith sais
Your "where" condition is effectively converted an OUTER JOIN into
an INNER JOIN ..
is true. But the OR condition makes this true for either the one or
the other outer join. I hope this is what Dinu means here:
And the two queries are algebrically identical. "(X ∊ S or X:=null)
AND (X is not null)" is
equivalent to "X ∊ S is not null". The two queries might look
different only
from an imperative programming point of view.
Anyway the two queries return the same set of rows.
Furthermore: what is a "bloated" execution plan?
I set up some test data and the query deamed bloated appears just as
fast. See below.
This test also show a small semantic difference in the two queries.
The set of rows is the same but the second query leaves certain
details null if only one of the OR conditions is true. That occurs in
row 1.
The outcome of the "bloated" execution plan is more complete.
Possibly I am too pragmatical and don't understand the discussion.
E. Pasma
My test script:
create table a (a integer primary key, ab, ac);
create table b (b integer primary key, d);
create table c (c integer primary key, d);
insert into a values (null,1,1);
insert into a select null, 2,2 from a;
insert into a select null, 3,2 from a;
insert into a select null, 4,4 from a;
insert into a select null, 5,5 from a;
insert into a select null, 6,6 from a;
insert into a select null, 7,7 from a;
insert into a select null, 8,8 from a;
insert into a select null, 9,9 from a;
insert into a select null, 10,10 from a;
insert into b values (1,1),(2,2),(3,3),(4,3),(6,3);
insert into c values (1,1),(4,5),(5,5),(7,1);
.eqp on
.timer on
SELECT *
FROM
a
LEFT JOIN
b ON b=ab
LEFT JOIN
c ON c=ac
WHERE
b.d IN (1,2,3) OR
c.d IN (4,5)
;
SELECT *
FROM
a
LEFT JOIN
b ON b=ab AND b.d IN (1,2,3)
LEFT JOIN
c ON c=ac AND c.d IN (4,5)
WHERE
b.d IS NOT NULL OR
c.d IS NOT NULL
;
Output:
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
1|1|1|1|1|1|1
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.003 user 0.001587 sys 0.000358
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|1|1||
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.002 user 0.001560 sys 0.000296
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
