ClickHouse布隆过滤器索引查询过慢，求优化解决方案
问题描述
我执行了一条简单的count查询，却处理了约10亿行数据，耗时75秒：
SELECT count(*) FROM events_distributed WHERE (orgId = '174a4727-1116-4c5c-8234-ab76f2406c4a') AND (timestamp >= '2022-12-05 00:00:00.000000000') Query id: e4312ff5-6add-4757-8deb-d68e0f3e29d9 ┌──count()─┐ │ 13071204 │ └──────────┘ 1 row in set. Elapsed: 74.951 sec. Processed 979.00 million rows, 8.26 GB (13.06 million rows/s., 110.16 MB/s.)
现有表结构与索引
我的events表结构及索引配置如下，已在orgid上创建布隆过滤器索引：
PARTITION BY toDate(timestamp) ORDER BY (timestamp);
INDEX idx_orgid orgid TYPE bloom_filter(0.01) GRANULARITY 1,
执行计划
以下是执行计划：
EXPLAIN indexes = 1 SELECT count(*) FROM events_distributed WHERE (orgid = '174a4727-1116-4c5c-8234-ab76f240fc4a') AND (timestamp >= '2022-12-05 00:00:00.000000000') AND (timestamp <= '2022-12-06 00:00:00.000000000') Query id: 879c2ce5-c4c7-4efc-b0e2-25613848afad ┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ Expression ((Projection + Before ORDER BY)) │ │ MergingAggregated │ │ Union │ │ Aggregating │ │ Expression (Before GROUP BY) │ │ Filter (WHERE) │ │ ReadFromMergeTree (users.events) │ │ Indexes: │ │ MinMax │ │ Keys: │ │ timestamp │ │ Condition: and((timestamp in (-Inf, '1670284800']), (timestamp in ['1670198400', +Inf))) │ │ Parts: 12/342 │ │ Granules: 42122/407615 │ │ Partition │ │ Keys: │ │ toDate(timestamp) │ │ Condition: and((toDate(timestamp) in (-Inf, 19332]), (toDate(timestamp) in [19331, +Inf))) │ │ Parts: 12/12 │ │ Granules: 42122/42122 │ │ PrimaryKey │ │ Keys: │ │ timestamp │ │ Condition: and((timestamp in (-Inf, '1670284800']), (timestamp in ['1670198400', +Inf))) │ │ Parts: 12/12 │ │ Granules: 30696/42122 │ │ Skip │ │ Name: idx_orgid │ │ Description: bloom_filter GRANULARITY 1 │ │ Parts: 8/12 │ │ Granules: 20556/30696 │ │ ReadFromRemote (Read from remote replica) │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 32 rows in set. Elapsed: 0.129 sec.
疑问与常用查询
处理10亿行仅得到1300万行的计数，显然效率低下，请问如何提速？由于我的org最多1万个，给orgid创建SET索引是否更优？
我常用的查询语句分为两类：
第一类是按时间间隔聚合的查询：
SELECT org_level, min(timestamp) as minTimeStamp,max(timestamp) as maxTimeStamp, toStartOfInterval(toDateTime(timestamp), INTERVAL <step> second) as roundedDownTs, count(*) as cnt, orgid FROM events_distributed WHERE orgid = 'foo' and timestamp BETWEEN <one week> GROUP BY roundedDownTs, orgid, org_level ORDER BY roundedDownTs DESC;
注：<step>取值为0, 60, 240, 1440, 10080
第二类是时间切片的查询（通常为一周），需按时间戳降序返回：
SELECT org_text FROM events_distributed WHERE (orgid = '174a4727-1116-4c5c-8234-ab76f2406c4a') AND (timestamp >= '2022-12-01 00:00:00.000000000' and timestamp <= '2022-12-07 00:00:00.000000000') order by timestamp DESC LIMIT 51;
1. 调整主键排序键，纳入orgid作为前缀
当前主键仅为timestamp，无法借助主键索引快速定位指定orgid的数据。修改表的排序键为：
ORDER BY (orgid, timestamp);
这样在查询指定orgid时，ClickHouse可通过主键索引直接定位到该orgid对应的时间范围数据，大幅减少扫描的颗粒数。
2. 替换布隆过滤器为SET索引
orgid基数仅约1万，属于低基数场景，SET索引比布隆过滤器更适合这类等值过滤场景——它能精确过滤不含目标orgid的颗粒，没有布隆过滤器的误判问题，过滤效率更高。创建语句如下（需先删除原有布隆过滤器索引）：
INDEX idx_orgid orgid TYPE set(10000) GRANULARITY 1;
3. 用物化视图预聚合提升聚合查询性能
针对常用的时间间隔聚合查询，创建物化视图提前计算聚合结果，避免每次查询都扫描原始数据：
CREATE MATERIALIZED VIEW mv_events_agg ENGINE = AggregatingMergeTree() PARTITION BY toDate(timestamp) ORDER BY (orgid, org_level, roundedDownTs) AS SELECT orgid, org_level, toStartOfInterval(toDateTime(timestamp), INTERVAL 60 second) as roundedDownTs, -- 可针对不同step创建多视图，或用tuple存储多粒度 minState(timestamp) as minTimeStamp, maxState(timestamp) as maxTimeStamp, countState(*) as cnt FROM events GROUP BY orgid, org_level, roundedDownTs;
查询时直接从物化视图读取预聚合数据，能极大降低计算开销。
4. 优化TopN时间排序查询
若已将主键改为(orgid, timestamp)，指定orgid后数据已按timestamp排序，此时LIMIT查询可直接快速获取最新N条数据，无需全局排序。如果保留原有主键，可考虑创建倒序索引，但更推荐调整主键为(orgid, timestamp)，同时优化两类查询场景。
5. 检查分布式表分片策略
若events_distributed是分布式表，需确保分片策略能让过滤条件下推到分片节点，避免中控节点聚合大量数据。比如按orgid或timestamp分片，保证各分片数据量均衡，且过滤逻辑在分片侧生效。
内容的提问来源于stack exchange，提问作者user1870400
