如何优化PostgreSQL查询速度？现有查询耗时约26ms

当前查询语句 #

SELECT
    referrer_id, count(id) as referrals_count
FROM users
WHERE referrer_id != 0
GROUP BY referrer_id
order by referrals_count desc
limit 10;

执行计划分析（翻译后） #

执行计划显示：PostgreSQL对users表执行顺序扫描，随后进行分组聚合操作，接着按referrals_count降序排序，最后返回前10条结果。未使用任何已创建的索引，这是因为优化器判断在当前数据分布下，全表扫描的成本低于索引扫描。

优化方案 #

1. 创建覆盖索引 #

如果表中referrer_id = 0的记录占比不低，创建包含查询所需字段的覆盖索引可以避免全表扫描：

-- PostgreSQL 11及以上版本
CREATE INDEX idx_users_referrer_id_include ON users(referrer_id) INCLUDE(id);

-- 低版本PostgreSQL（<11），将id加入索引键
CREATE INDEX idx_users_referrer_id_id ON users(referrer_id, id);

覆盖索引仅包含referrer_id和id字段，PostgreSQL可以直接从索引中获取数据完成统计，无需回表查询原数据。

2. 更新统计信息 #

确保PostgreSQL拥有最新的表统计数据，帮助优化器做出更合理的执行计划选择：

ANALYZE users;

3. 使用物化视图预计算结果 #

如果业务允许非实时统计，物化视图是最优方案——提前计算好聚合结果，查询时直接读取预计算数据：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_referral_top_counts AS
SELECT
    referrer_id, count(id) as referrals_count
FROM users
WHERE referrer_id != 0
GROUP BY referrer_id;

-- 为物化视图创建排序索引，加速取前10的查询
CREATE INDEX idx_mv_referrals_desc ON mv_referral_top_counts(referrals_count DESC);

-- 需要更新数据时，刷新物化视图
REFRESH MATERIALIZED VIEW mv_referral_top_counts;

之后查询直接从物化视图获取：

SELECT referrer_id, referrals_count
FROM mv_referral_top_counts
ORDER BY referrals_count DESC
LIMIT 10;

4. 评估当前性能合理性 #

15万行数据耗时26ms本身属于可接受的范围，如果业务对这个延迟没有更高要求，无需额外优化。若追求极致性能，优先考虑物化视图方案。

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