Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2(EOL)
-
None
Description
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
Attachments
Issue Links
- duplicates
-
MDEV-10855 Window functions: condition pushdown through the PARTITION BY clause
- Closed
- relates to
-
MDEV-13389 Optimization for equi-joins of derived tables with window functions
- Closed