Django+PostgreSQL超4000万行慢查询优化求助

针对你的PostgreSQL慢查询问题，结合执行计划和业务逻辑，我整理了以下几种实用的优化思路：

1. 打造精准的复合覆盖索引，避免回表与冗余过滤 #

你的查询需要同时满足source_id IS NULL、removed = FALSE，并且关联target_id到ModelB的c_id。当前ModelA的索引model_a_source_target_idx可能只包含source_id和target_id，无法直接过滤removed条件，导致每次索引扫描后还要回表判断removed状态。

可以创建带过滤条件的复合索引：

CREATE INDEX idx_model_a_target_source_removed ON model_a (target_id, source_id) 
WHERE removed = FALSE;

这个索引的好处是：

• 前缀target_id匹配关联条件，快速定位关联ModelB的记录
• 包含source_id，直接过滤出source_id IS NULL的记录
• 过滤条件removed = FALSE提前排除无效数据，减少扫描行数
• 若查询字段都能被索引覆盖，还可以加INCLUDE子句进一步避免回表

同时检查ModelB的索引，确保c_id的索引是最优的。如果当前的model_b_model_c_removed_filename索引不是以c_id为前缀，可以单独创建：

CREATE INDEX idx_model_b_c_id ON model_b (c_id) 
WHERE removed = FALSE; -- 若ModelB也有removed字段

2. 调整查询逻辑，先获取小结果集再关联 #

执行计划显示ModelB的扫描返回了5907行，然后通过Nested Loop关联ModelA。如果我们先把符合条件的ModelB ID提取出来，再用IN查询关联ModelA，可能会触发更高效的Hash Join或者Bitmap Scan：

在Django中改写查询：

# 先获取符合条件的ModelB ID列表
valid_target_ids = ModelB.objects.filter(c=my_c).values_list('id', flat=True)
# 再查询ModelA
result = ModelA.objects.filter(
    source__isnull=True,
    target_id__in=valid_target_ids,
    removed=False
)

这种方式会先生成一个小的ID集合，再在ModelA中批量匹配，减少嵌套循环的次数，尤其当ModelB的结果集较小时效果明显。

3. 利用物化视图预计算结果（适合高频查询场景） #

如果这个查询是业务中经常执行的，并且对数据实时性要求不高，可以创建物化视图预先计算好符合条件的记录：

CREATE MATERIALIZED VIEW mv_model_a_valid AS
SELECT ma.*
FROM model_a ma
INNER JOIN model_b mb ON ma.target_id = mb.id
WHERE ma.source_id IS NULL 
  AND ma.removed = FALSE 
  AND mb.c_id = 389;

-- 给物化视图建索引加速查询
CREATE UNIQUE INDEX idx_mv_model_a_id ON mv_model_a_valid (id);
CREATE INDEX idx_mv_model_a_target_id ON mv_model_a_valid (target_id);

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

# Django中可以给物化视图创建对应的Model，直接查询
result = ModelAValidMV.objects.all()

注意定期刷新物化视图保持数据一致性：

REFRESH MATERIALIZED VIEW mv_model_a_valid;
-- 如果需要并发刷新，可以用：
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_model_a_valid;

4. 冗余字段，消除JOIN关联（适合更新低频场景） #

如果业务允许，可以把ModelB的c_id冗余到ModelA中，这样查询时不需要关联ModelB，直接在ModelA上过滤：

1. 给ModelA添加字段：

class ModelA(...):
    source = models.ForeignKey(ModelB, related_name='source', ...)
    target = models.ForeignKey(ModelB, related_name='target', ...)
    target_c_id = models.IntegerField(null=True) # 冗余ModelB的c_id

2. 通过Django信号或PostgreSQL触发器同步target_c_id的值：

   • 当ModelB的c_id更新时，自动更新关联的ModelA的target_c_id
   • 新增ModelA记录时，自动填充target_c_id

3. 创建索引：

CREATE INDEX idx_model_a_c_source_removed ON model_a (target_c_id, source_id) 
WHERE removed = FALSE;

4. 改写查询：

result = ModelA.objects.filter(
    source__isnull=True,
    target_c_id=my_c.id,
    removed=False
)

这种方式彻底消除了JOIN操作，查询效率会大幅提升，唯一需要维护的是数据一致性，适合ModelB的c_id不经常更新的场景。

5. 调整PostgreSQL配置参数，优化并行扫描与内存使用 #

从执行计划看，查询使用了3个并行Worker，但如果服务器内存充足，可以适当调整参数提升并行效率：

• 修改postgresql.conf：

# 增加每个并行任务的内存分配
work_mem = 32MB # 默认是4MB，根据服务器内存调整
# 提升每个Gather节点的并行Worker数量
max_parallel_workers_per_gather = 4 # 默认是4，可以根据CPU核心数调整

调整后需要重启PostgreSQL生效，这能让并行扫描和嵌套循环更高效，减少内存不足导致的磁盘交换。

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