|
At the moment optimizer does not push condition into Window function in SELECT part of inner query:
create or replace table t1 (a int, b int, c int, index(a, b, c));
|
create or replace table t2 (a int, b int, c int, index(a, b, c));
|
|
insert into t1 select seq, seq, seq from seq_1_to_100000;
|
insert into t2 select seq, seq, seq from seq_1_to_100000;
|
|
explain
|
select * from
|
(
|
select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )
|
from t1 join t2
|
where
|
t1.a=t2.a and t1.b=t2.b
|
)
|
v1 where (a, b, c) = (50000,50000,50000);
|
|
+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
|
| 2 | DERIVED | t1 | index | a | a | 15 | NULL | 100000 | Using where; Using index; Using temporary |
|
| 2 | DERIVED | t2 | ref | a | a | 10 | tdsdbsgowner.t1.a,tdsdbsgowner.t1.b | 1 | Using index |
|
+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+
|
So optimizer is processing all rows for inner query instead of using WHERE condition from outer query
|