PostgreSQL千万级数据DISTINCT查询耗时过长，求优化方案

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

你的查询过滤条件是colour = 'red'，目标是获取去重的column_1，创建**(colour, column_1)**的复合索引可以让数据库直接通过索引完成查询，无需全表扫描和额外排序：

CREATE INDEX idx_table1_colour_column1 ON table_1 (colour, column_1);

这个索引的优势在于：

• 索引会先按colour分组，快速定位到所有colour='red'的条目
• 同一colour下的column_1是有序存储的，数据库可以直接遍历有序的索引项完成去重，避免对5000万行数据做全量排序

2. 放弃递归CTE方案 #

你尝试的递归CTE完全没必要，这种方式会多次发起查询，每次只能获取一条新数据，效率远低于原生的DISTINCT或GROUP BY，直接弃用即可。

3. 更新统计信息 #

如果数据库的统计信息过时，优化器可能无法选择最优执行计划，执行以下命令更新表的统计信息：

ANALYZE table_1;

4. 临时调整内存参数（针对PostgreSQL） #

如果查询时因为内存不足导致使用磁盘临时表，会大幅拖慢速度，可以临时调大work_mem参数（根据服务器内存调整，比如设为64MB或128MB）：

SET work_mem = '64MB';

调整后再执行查询，排序操作可以在内存中完成，速度会明显提升。

5. 物化视图（适合非实时查询场景） #

如果数据不是需要实时更新，而是允许一定延迟，可以创建物化视图预先存储去重后的结果：

CREATE MATERIALIZED VIEW mv_table1_colour_column1 AS
SELECT colour, column_1
FROM table_1
GROUP BY colour, column_1;

后续查询直接从物化视图获取数据：

SELECT column_1 FROM mv_table1_colour_column1 WHERE colour = 'red';

可以定期刷新物化视图保证数据时效性：

REFRESH MATERIALIZED VIEW mv_table1_colour_column1;

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