PostgreSQL中按参与数排序的查询优化方案咨询

看了你的查询、表结构和执行计划，问题根源很清晰：全表扫描+大规模磁盘排序拖慢了查询速度。咱们一步步来解决：

1. 最关键的索引优化 #

你的participations表目前没有针对project_id的索引，这导致查询时不得不全表扫描43万条记录，再进行磁盘排序来匹配projects的连接键。直接创建这个索引就能解决大部分问题：

CREATE INDEX idx_participations_project_id ON participations (project_id);

为什么这个索引有效？ #

• 有了这个索引后，数据库可以直接按project_id分组统计参与量，不需要全表扫描和排序。
• 统计COUNT(*)时，数据库可以直接使用索引数据（不需要回表查原数据），大幅减少IO开销。

如果你的PostgreSQL版本是11+，还可以考虑覆盖索引进一步优化（不过上面的基础索引已经足够解决大部分问题）：

CREATE INDEX idx_participations_project_id_cover ON participations (project_id) INCLUDE (id);

这个索引包含了统计需要的最小数据，避免了任何回表操作。

2. 重构查询语句，减少排序数据量 #

原查询是先JOIN两张表再GROUP BY，会产生43万条中间结果后再排序。我们可以先统计每个项目的参与量，再和projects表关联，这样排序的数据量会小很多：

SELECT p.*, pc.ct
FROM projects p
INNER JOIN (
    -- 先统计每个项目的参与数，结果只有几千条（对应存在参与的项目）
    SELECT project_id, COUNT(*) AS ct
    FROM participations
    GROUP BY project_id
) pc ON p.id = pc.project_id
ORDER BY pc.ct DESC
LIMIT 5;

这个重构的优势： #

• 子查询只处理participations表，用索引快速生成project_id和对应计数，结果集只有约1800条（和执行计划里的rows=1884一致）。
• 后续排序只针对这1800条数据，而不是原查询的43万条，排序开销骤降。

3. 额外优化点（可选） #

从执行计划看，projects表也进行了全表扫描和排序：

->  Seq Scan on projects  (cost=0.00..458.07 rows=2907 width=1131)

如果你的业务中只需要查询未删除的项目（deleted_at IS NULL），可以在查询中添加过滤条件，并给projects表创建一个包含id和deleted_at的索引：

-- 添加过滤条件的查询
SELECT p.*, pc.ct
FROM projects p
INNER JOIN (
    SELECT project_id, COUNT(*) AS ct
    FROM participations
    GROUP BY project_id
) pc ON p.id = pc.project_id
WHERE p.deleted_at IS NULL -- 过滤已删除项目
ORDER BY pc.ct DESC
LIMIT 5;

-- 创建对应索引
CREATE INDEX idx_projects_id_deleted ON projects (id) WHERE deleted_at IS NULL;

这会让projects表的扫描从全表变成索引扫描，进一步减少查询时间。

效果预期 #

添加索引并重构查询后，你的查询时间应该能从2秒降到几十毫秒级别，和你那类简单查询的速度接近。

内容的提问来源于stack exchange，提问作者rap-2-h