如何降低PostgreSQL中ARRAY_AGG查询的运行时长？

首先指出原查询的冗余问题：你当前的查询里，CTE agged_data 完全多余，直接查询 user_info 即可；另外 LIMIT 100 对聚合查询没有意义——聚合后只会返回一行结果，这两个冗余会增加不必要的开销。

以下是几种优化/替代方案：

方案1：优化原查询并添加索引 #

先简化原查询：

SELECT 
  ARRAY_AGG(DISTINCT birth_date) AS birth_dates,
  ARRAY_AGG(DISTINCT place_of_birth) AS places_of_birth,
  ARRAY_AGG(DISTINCT first_name) AS first_names
FROM user_info;

给每个需要聚合去重的列单独创建B树索引，PostgreSQL可以利用索引快速获取去重后的列值，避免全表扫描后的排序去重：

CREATE INDEX idx_user_info_birth_date ON user_info(birth_date);
CREATE INDEX idx_user_info_place_of_birth ON user_info(place_of_birth);
CREATE INDEX idx_user_info_first_name ON user_info(first_name);

如果列允许为空，且不需要包含NULL值在结果里，可以在聚合时加上 WHERE 列 IS NOT NULL，进一步减少计算量。

方案2：分查询聚合后在应用层组装 #

如果多列聚合的开销实在太大，可以拆分每个列的去重聚合为单独查询，每个查询能独立利用索引，总耗时可能比一次聚合更短：

单个列的查询示例：

-- 获取所有不同的birth_date
SELECT ARRAY_AGG(DISTINCT birth_date) FROM user_info;
-- 获取所有不同的place_of_birth
SELECT ARRAY_AGG(DISTINCT place_of_birth) FROM user_info;
-- 获取所有不同的first_name
SELECT ARRAY_AGG(DISTINCT first_name) FROM user_info;

也可以用UNION ALL把结果合并成一个结果集，方便应用层统一处理：

SELECT 'birth_date' AS column_name, ARRAY_AGG(val) AS distinct_values
FROM (SELECT DISTINCT birth_date AS val FROM user_info) t
UNION ALL
SELECT 'place_of_birth' AS column_name, ARRAY_AGG(val) AS distinct_values
FROM (SELECT DISTINCT place_of_birth AS val FROM user_info) t
UNION ALL
SELECT 'first_name' AS column_name, ARRAY_AGG(val) AS distinct_values
FROM (SELECT DISTINCT first_name AS val FROM user_info) t;

17k行的表中，每个单查询都会非常快，即使多执行几次，对数据库的压力也远小于一次慢查询。

方案3：利用系统表（非实时场景） #

如果不需要实时获取最新的不同值，只是需要列的常见值或统计结果，可以查询pg_stats系统表：

SELECT 
  attname AS column_name,
  most_common_vals AS common_values
FROM pg_stats
WHERE tablename = 'user_info' AND attname IN ('birth_date', 'place_of_birth', 'first_name');

注意：pg_stats里的most_common_vals只存储最常见的部分值，且统计信息是定期更新的（可手动执行ANALYZE user_info更新），不适合需要实时准确结果的场景。

额外建议 #

• 先执行ANALYZE user_info更新表的统计信息，让PostgreSQL生成更优的执行计划。
• 用EXPLAIN ANALYZE查看原查询的执行计划，确认是否存在全表扫描、排序等耗时操作，针对性优化。

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