PostgreSQL子查询误用低效索引问题排查与优化咨询

问题背景 #

用户需要从campaigns表的指定行（id为53、54）中，关联effects表获取对应行的最新记录，原查询语句如下：

SELECT
    id,
    (
        SELECT
            created
        FROM
            effects
        WHERE
            effects.campaignid = campaigns.id
        ORDER BY
            effects.created DESC
        LIMIT 1
    ) AS last_activity
FROM
    campaigns
WHERE
    deleted_at IS NULL
    AND id in(53, 54);

为优化性能，用户创建了覆盖(campaignid, created)的BTREE复合索引effects_campaign_created_idx，此外还有一个用于其他场景的BTREE单字段索引effects_created_idx（基于created字段）。

但出现异常：子查询突然不再选用高效的复合索引，转而使用低效的单字段索引，导致查询耗时从约40ms飙升至约5分钟；而单独执行子查询（使用相同的campaignid值）时，会正确选用高效索引。

用户尝试将子查询改为MAX(created)替代ORDER BY created DESC LIMIT 1，性能依然不佳。已执行reindex和analyze操作，且effects表中campaignid为53、54的记录占比不高。

原查询的EXPLAIN ANALYZE结果 #

explain analyze SELECT
    id,
    (
        SELECT
            created
        FROM
            effects
        WHERE
            effects.campaignid = campaigns.id
        ORDER BY
            effects.created DESC
        LIMIT 1) AS last_activity
FROM
    campaigns
WHERE
    deleted_at IS NULL
    AND id in(53, 54);

执行输出：

Seq Scan on campaigns  (cost=0.00..4.56 rows=2 width=12) (actual time=330176.476..677186.438 rows=2 loops=1)
  Filter: ((deleted_at IS NULL) AND (id = ANY ('{53,54}'::integer[])))
  Rows Removed by Filter: 45
  SubPlan 1
    ->  Limit  (cost=0.43..0.98 rows=1 width=8) (actual time=338593.165..338593.166 rows=1 loops=2)
          ->  Index Scan Backward using effects_created_idx on effects  (cost=0.43..858859.67 rows=1562954 width=8) (actual time=338593.160..338593.160 rows=1 loops=2)
                Filter: (campaignid = campaigns.id)
                Rows Removed by Filter: 14026092
Planning Time: 0.245 ms
Execution Time: 677195.239 ms

MAX改写后的EXPLAIN ANALYZE结果 #

EXPLAIN ANALYZE SELECT
    campaigns.id,
    subquery.created
FROM
    campaigns
    LEFT JOIN (
        SELECT
            campaignid,
            MAX(created) created
        FROM
            effects
        GROUP BY
            campaignid) subquery ON campaigns.id = subquery.campaignid
WHERE
    campaigns.deleted_at IS NULL
    AND campaigns.id in(53, 54);

执行输出：

Hash Right Join  (cost=667460.06..667462.46 rows=2 width=12) (actual time=30516.620..30573.091 rows=2 loops=1)
  Hash Cond: (effects.campaignid = campaigns.id)
  ->  Finalize GroupAggregate  (cost=667457.45..667459.73 rows=9 width=16) (actual time=30251.920..30308.379 rows=23 loops=1)
        Group Key: effects.campaignid
        ->  Gather Merge  (cost=667457.45..667459.55 rows=18 width=16) (actual time=30251.832..30308.271 rows=49 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=666457.43..666457.45 rows=9 width=16) (actual time=30156.539..30156.544 rows=16 loops=3)
                    Sort Key: effects.campaignid
                    Sort Method: quicksort  Memory: 25kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Partial HashAggregate  (cost=666457.19..666457.28 rows=9 width=16) (actual time=30155.951..30155.957 rows=16 loops=3)
                          Group Key: effects.campaignid
                          Batches: 1  Memory Usage: 24kB
                          Worker 0:  Batches: 1  Memory Usage: 24kB
                          Worker 1:  Batches: 1  Memory Usage: 24kB
                          ->  Parallel Seq Scan on effects  (cost=0.00..637166.13 rows=5858213 width=16) (actual time=220.784..28693.182 rows=4684157 loops=3)
  ->  Hash  (cost=2.59..2.59 rows=2 width=4) (actual time=264.653..264.656 rows=2 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on campaigns  (cost=0.00..2.59 rows=2 width=4) (actual time=264.612..264.640 rows=2 loops=1)
              Filter: ((deleted_at IS NULL) AND (id = ANY ('{53,54}'::integer[])))
              Rows Removed by Filter: 45
Planning Time: 0.354 ms
JIT:
  Functions: 34
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 9.958 ms, Inlining 409.293 ms, Optimization 308.279 ms, Emission 206.936 ms, Total 934.465 ms
Execution Time: 30578.920 ms

优化器选错索引的原因 #

• 统计信息估算偏差：尽管执行了analyze，PostgreSQL对关联子查询的基数估算可能出现错误。优化器可能认为通过effects_created_idx倒序扫描，能快速找到匹配campaignid的记录，但实际该campaignid的记录在索引中位置极靠后，导致扫描大量无效行。
• 参数化查询的计划限制：子查询中campaigns.id是动态参数，优化器生成计划时无法获取具体值，只能使用通用估算逻辑；而单独执行子查询时使用的是常量值，优化器能精准估算匹配行数，因此选对索引。
• 索引成本计算错误：优化器计算索引扫描成本时，可能错误评估了两个索引的开销，比如认为单字段索引的I/O成本更低，忽略了实际需要过滤的大量无效行。

确保选用正确索引的查询调整方法 #

1. 强制指定索引（PostgreSQL 11+） #

在子查询中直接指定要使用的复合索引，避免优化器选错：

SELECT
    id,
    (
        SELECT
            created
        FROM
            effects INDEX (effects_campaign_created_idx)
        WHERE
            effects.campaignid = campaigns.id
        ORDER BY
            effects.created DESC
        LIMIT 1
    ) AS last_activity
FROM
    campaigns
WHERE
    deleted_at IS NULL
    AND id in(53, 54);

2. 改写为LATERAL JOIN #

这种写法更明确地表达关联逻辑，优化器更容易识别并选用复合索引：

SELECT
    c.id,
    e.last_activity
FROM
    campaigns c
LEFT JOIN LATERAL (
    SELECT created AS last_activity
    FROM effects
    WHERE effects.campaignid = c.id
    ORDER BY created DESC
    LIMIT 1
) e ON true
WHERE
    c.deleted_at IS NULL
    AND c.id IN (53, 54);

3. 优化MAX写法避免全表分组 #

原MAX写法会对整个effects表分组计算，性能低下，改为针对每个campaignid单独计算MAX：

SELECT
    c.id,
    (SELECT MAX(created) FROM effects WHERE campaignid = c.id) AS last_activity
FROM
    campaigns c
WHERE
    c.deleted_at IS NULL
    AND c.id IN (53, 54);

调试查询优化器行为的高级方法 #

• 查看详细执行计划：使用EXPLAIN (ANALYZE, VERBOSE, BUFFERS)，获取缓冲区使用、索引扫描细节，对比优化器估算的行数与实际行数，判断统计信息是否准确。
• 临时禁用低效索引：通过ALTER INDEX effects_created_idx SET (enable = false);临时禁用单字段索引，测试优化器是否会自动选用复合索引，验证成本估算问题。测试完成后可通过ALTER INDEX effects_created_idx SET (enable = true);恢复。
• 检查表统计信息：执行SELECT * FROM pg_stats WHERE tablename = 'effects';，查看campaignid的n_distinct、most_common_vals等字段，确认统计数据是否能准确反映表数据分布。
• 使用pg_hint_plan扩展：安装pg_hint_plan扩展后，可通过注释指定索引，例如：

SELECT
    id,
    (
        SELECT /*+ IndexScan(effects effects_campaign_created_idx) */
            created
        FROM
            effects
        WHERE
            effects.campaignid = campaigns.id
        ORDER BY
            effects.created DESC
        LIMIT 1
    ) AS last_activity
FROM
    campaigns
WHERE
    deleted_at IS NULL
    AND id in(53, 54);

• 查看优化器决策日志：修改postgresql.conf中的log_min_messages = debug1和log_planner_stats = on，重启数据库后执行查询，通过日志查看优化器选择索引的完整决策过程。

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