如何让PostgreSQL使用相关索引？排查执行计划异常

表结构 #

create table metrics_measurement
(
    id                       bigint generated by default as identity
        constraint metrics_measurement_pkey
            primary key,
    measured_at              timestamp with time zone not null,
    measured_value           double precision         not null,
    metric_id                integer                  not null
        constraint metrics_measurement_metric_id_24fcec65_fk_metrics_metric_id
            references metrics_metric
            deferrable initially deferred,
    processed_into_aggregate boolean                  not null,
    created_at               timestamp with time zone not null,
    updated_at               timestamp with time zone not null
);

执行查询 #

SELECT * 
FROM "metrics_measurement" 
WHERE "metrics_measurement"."metric_id" = 6433 
ORDER BY "metrics_measurement"."measured_at" ASC 
LIMIT 1;

问题现象 #

预期PostgreSQL会先通过metric_id索引过滤数据，再排序取第一条，但实际执行计划为全表扫描后过滤排序，耗时极长；手动设置SET enable_seqscan TO OFF后，执行计划符合预期，性能提升两个数量级。

已尝试对表执行ANALYZE、修改metric_id的统计信息采样率为10000后再次ANALYZE，但查询优化器仍不选择索引。

排查与解决方案 #

1. 确认metric_id索引的有效性 #

先检查metric_id上是否存在索引：

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'metrics_measurement' AND indexdef LIKE '%metric_id%';

• 无索引则直接创建：

CREATE INDEX idx_metrics_measurement_metric_id ON metrics_measurement(metric_id);

• 已有索引则检查是否被使用过：

SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'metrics_measurement';

若idx_scan为0，说明索引从未被调用，可尝试重建索引：

REINDEX INDEX idx_metrics_measurement_metric_id;

2. 分析目标metric_id的数据分布 #

查看metric_id=6433的记录占比，判断优化器误判原因：

SELECT COUNT(*) FROM metrics_measurement WHERE metric_id = 6433;

如果该值的记录数占全表比例极高，优化器可能认为全表扫描更高效，但结合LIMIT 1的场景，可进一步查看该metric_id下measured_at的分布规律：

SELECT MIN(measured_at), MAX(measured_at) FROM metrics_measurement WHERE metric_id = 6433;

若measured_at是递增插入的，可直接改写查询避免排序：

SELECT * FROM metrics_measurement WHERE metric_id = 6433 AND measured_at = (SELECT MIN(measured_at) FROM metrics_measurement WHERE metric_id = 6433);

3. 创建匹配查询逻辑的复合索引 #

针对WHERE metric_id=? + ORDER BY measured_at + LIMIT 1的需求，创建(metric_id, measured_at)复合索引，优化器可直接通过索引定位到目标数据，无需额外排序：

CREATE INDEX idx_metrics_measurement_metric_measured ON metrics_measurement(metric_id, measured_at);

4. 查看并调整优化器代价估算 #

用EXPLAIN ANALYZE对比两种执行计划的代价：

EXPLAIN ANALYZE SELECT * FROM "metrics_measurement" WHERE "metrics_measurement"."metric_id" = 6433 ORDER BY "metrics_measurement"."measured_at" ASC LIMIT 1;

若优化器低估了全表扫描代价，可临时调整*random_page_cost*参数（默认4，可设为1.1接近顺序扫描代价）：

SET random_page_cost = 1.1;

测试有效后可在postgresql.conf中修改并重启，注意这是全局参数。

5. 局部强制使用索引 #

无需全局禁用顺序扫描，可在单查询中指定索引：

SELECT * 
FROM "metrics_measurement" USE INDEX (idx_metrics_measurement_metric_id)
WHERE "metrics_measurement"."metric_id" = 6433 
ORDER BY "metrics_measurement"."measured_at" ASC 
LIMIT 1;

或临时关闭当前会话的顺序扫描：

SET enable_seqscan = OFF;
-- 执行目标查询
SELECT * FROM "metrics_measurement" WHERE "metrics_measurement"."metric_id" = 6433 ORDER BY "metrics_measurement"."measured_at" ASC LIMIT 1;
-- 恢复默认设置
SET enable_seqscan = ON;

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