PostgreSQL有序查询选错多列索引，如何引导其使用指定高效索引？

以下几种方案均可解决该问题，优先推荐改写SQL的方案，兼容性最好且性能最稳定：

1. 改写SQL为LATERAL关联写法（最优方案） #

你要获取每个指定活动的最新一条效果记录，本质是对每个campaignid取排序后的TOP 1，用LATERAL关联的写法可以强制让PostgreSQL对每个活动单独走复合索引查询，完全避免全表扫描或选错索引的问题：

SELECT c.campaignid, e.created
FROM unnest(ARRAY[1,2,3,7]) AS c(campaignid) -- 把要查询的活动ID放在数组里即可
LEFT JOIN LATERAL (
    SELECT created 
    FROM effects
    WHERE effects.campaignid = c.campaignid
    ORDER BY created DESC
    LIMIT 1
) e ON true;

该写法下，每个campaignid的查询都会命中effects_campaignid_created_desc_idx复合索引，直接取索引最顶部的1条记录，哪怕是数据量极大的campaignid=7，查询耗时也在毫秒级。

2. 调整规划器成本参数引导索引选择 #

PostgreSQL默认认为随机IO成本远高于顺序IO，所以当判断查询涉及的记录占比高时会倾向选择全表扫描。你可以在会话级别调低随机IO成本参数，引导规划器优先选择索引：

-- 会话级别生效，执行查询前运行即可
SET random_page_cost = 1.1;

如果希望只对当前查询生效，可以结合pg_hint_plan插件使用hint指定参数：

SELECT /*+ Set(random_page_cost 1.1) */ 
DISTINCT ON (campaignid)
    campaignid,
    created
FROM
    effects
WHERE
    campaignid IN(1, 2, 3,7)
ORDER BY
    campaignid,
    created DESC;

3. 用hint强制指定使用目标索引 #

如果已经安装了pg_hint_plan插件，可以直接强制指定查询使用effects_campaignid_created_desc_idx索引：

SELECT /*+ IndexScan(effects effects_campaignid_created_desc_idx) */ 
DISTINCT ON (campaignid)
    campaignid,
    created
FROM
    effects
WHERE
    campaignid IN(1, 2, 3,7)
ORDER BY
    campaignid,
    created DESC;

4. 清理冗余干扰索引 #

你现有的effects_created_idx单列索引是导致查询campaignid=7时走反向索引扫描的核心原因，如果该索引没有其他业务查询必须使用，可以直接删除，规划器没有其他可选路径就会自动使用复合索引。

5. 更新统计信息 #

如果是表统计信息不准确导致的规划器判断错误，可以手动更新表统计信息：

ANALYZE effects;

该方案对数据分布极不均匀的场景效果有限，建议优先选择改写SQL的方案。

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