大表关联统计近30天数据慢查询优化求助

问题背景 #

原查询语句：

SELECT COUNT(s0_.id) AS sclr_0 FROM public.stat s0_ INNER JOIN link l1_ ON s0_.link_id = l1_.id WHERE s0_.created_at > '2024-12-14 07:46:04' AND l1_.team_id = 37;

• 数据规模：link表800万行，stat表1450万行
• 执行耗时：超14秒，查询结果仅34577条
• 核心需求：统计指定团队（team_id=37）旗下所有链接近30天的stat数据量

从EXPLAIN ANALYZE结果可见，性能瓶颈在于：通过link_id扫描stat表后，需要过滤大量不符合时间条件的记录（单条link扫描时过滤3503行），现有索引仅覆盖link_id，无法提前筛选时间条件，导致IO和计算开销过高。

优化方案 #

1. 创建复合索引（核心优化） #

创建包含link_id和created_at的复合索引，让数据库直接通过索引完成link_id匹配和时间过滤，无需回表或过滤大量无效数据：

CREATE INDEX idx_stat_link_created_at ON stat (link_id, created_at);

该索引能让查询直接定位到符合link_id且满足created_at条件的记录，大幅减少扫描行数和过滤开销。

2. 优化COUNT函数调用 #

将COUNT(s0_.id)替换为COUNT(*)，后者无需校验字段非空，且在索引覆盖场景下无需回表读取主键，效率更高：

SELECT COUNT(*) AS sclr_0 FROM public.stat s0_ INNER JOIN link l1_ ON s0_.link_id = l1_.id WHERE s0_.created_at > '2024-12-14 07:46:04' AND l1_.team_id = 37;

3. 更新统计信息并优化连接方式 #

当前执行计划中，优化器对link和stat的行数预估与实际偏差较大，可能导致选择低效的嵌套循环连接。先更新表统计信息，帮助优化器做出更优选择：

ANALYZE link;
ANALYZE stat;

若更新后仍使用嵌套循环且效率低下，可临时测试哈希连接（仅用于验证，不建议长期强制）：

-- 临时禁用嵌套循环
SET enable_nestloop = off;
-- 执行查询
SELECT COUNT(*) AS sclr_0 FROM public.stat s0_ INNER JOIN link l1_ ON s0_.link_id = l1_.id WHERE s0_.created_at > '2024-12-14 07:46:04' AND l1_.team_id = 37;
-- 恢复默认设置
SET enable_nestloop = on;

4. 预聚合与物化视图（针对频繁查询场景） #

若该统计查询需频繁执行，可创建物化视图预聚合数据，定期刷新以平衡实时性与性能：

-- 创建物化视图，按团队、链接、日期聚合统计量
CREATE MATERIALIZED VIEW mv_team_stat_daily AS
SELECT 
    l.team_id, 
    s.link_id, 
    DATE_TRUNC('day', s.created_at) AS stat_date, 
    COUNT(*) AS stat_count
FROM public.stat s
JOIN public.link l ON s.link_id = l.id
GROUP BY l.team_id, s.link_id, DATE_TRUNC('day', s.created_at);

-- 创建索引加速查询
CREATE INDEX idx_mv_team_stat ON mv_team_stat_daily (team_id, stat_date);

查询时直接从物化视图汇总数据：

SELECT SUM(stat_count) AS sclr_0
FROM mv_team_stat_daily
WHERE team_id = 37 AND stat_date >= CURRENT_DATE - INTERVAL '30 days';

定期刷新物化视图（若需实时性，可添加唯一索引后使用CONCURRENTLY）：

REFRESH MATERIALIZED VIEW mv_team_stat_daily;

5. 验证link表索引效率 #

当前link表通过team_id索引扫描耗时仅29ms，已满足需求。若后续团队链接数量大幅增长，可替换为(team_id, id)复合索引，避免回表读取id字段：

-- 若现有索引仅为team_id，可替换为复合索引
DROP INDEX IF EXISTS idx_36ac99f1296cd8ae;
CREATE INDEX idx_link_team_id_id ON link (team_id, id);

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