ClickHouse：主查询过滤时带窗口函数的子查询性能劣化问题

原本希望创建包含窗口函数、且能在视图定义外进行过滤的视图，但性能表现极差。由于视图的使用逻辑与子查询类似，开展了以下测试：

1. 主查询中过滤的简单子查询运行正常

select * from (select *
    FROM default.service_library sl )
    where account_id = 1592301

2. 子查询添加窗口函数后出现内存溢出

select * from (select *
    ,(sum(is_dead) over (partition by account_id, cluster_agent_id, run_id)) as is_dead2
    FROM default.service_library sl )
    where account_id = 1592301

3. 将过滤条件移至子查询内后问题解决

select * from (select *,    
    (sum(is_dead) over (partition by account_id, cluster_agent_id, run_id)) as is_dead2
    FROM default.service_library sl where account_id = 672423) 

对应执行计划 #

第一个查询的执行计划 #

┌─explain─────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))         │
│   Filter ((WHERE + (Projection + Before ORDER BY))) │
│     Filter (WHERE)                                  │
│       ReadFromMergeTree (default.service_library)   │
└─────────────────────────────────────────────────────┘

第二个查询的执行计划 #

┌─explain─────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                             │
│   Filter ((WHERE + (Projection + Before ORDER BY)))                                     │
│     Window (Window step for window 'PARTITION BY account_id, cluster_agent_id, run_id') │
│       Sorting (Sorting for window 'PARTITION BY account_id, cluster_agent_id, run_id')  │
│         Expression (Before window functions)                                            │
│           ReadFromMergeTree (default.service_library)                                   │
└─────────────────────────────────────────────────────────────────────────────────────────┘

第三个查询的执行计划 #

┌─explain───────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + (Before ORDER BY + (Projection + Before ORDER BY))))        │
│   Window (Window step for window 'PARTITION BY account_id, cluster_agent_id, run_id') │
│     Sorting (Sorting for window 'PARTITION BY account_id, cluster_agent_id, run_id')  │
│       Expression (Before window functions)                                            │
│         Filter (WHERE)                                                                │
│           ReadFromMergeTree (default.service_library)                                 │
└───────────────────────────────────────────────────────────────────────────────────────┘

表结构DDL #

CREATE TABLE default.service_library
(
account_id Int32,
checksum String,
cluster_agent_id Int32,
entity_guid String,
language String,
name String,
real_agent_id Int32,
version String,
created_at DateTime DEFAULT now(),
run_id Int32,
is_dead UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (account_id, entity_guid, name, version, cluster_agent_id, real_agent_id)

内容的提问来源于stack exchange，提问作者Danila Ermakov