PostgreSQL双列ORDER BY查询的最优执行计划问题排查

问题背景 #

现有表table_name，核心字段包括user_id、parent_id、id、date_m、media_type、name，已创建两个索引：

• ix_table_name_user_id_parent_id_date_m_media_type_name（以下简称index1）：(user_id, parent_id, date_m, media_type, name)
• 唯一约束索引uk_table_name_user_id_parent_id（以下简称index2）：(user_id, parent_id, name)

初始查询语句：

SELECT *
FROM table_name
WHERE user_id = 2
    AND parent_id = 1
    AND date_m > '2018-09-01T11:41:24'::timestamp
ORDER BY date_m
LIMIT 100

该查询使用index1，性能优异，但ORDER BY date_m无法保证唯一排序，存在相同date_m值时可能遗漏行的问题。

修改为ORDER BY date_m, id或ORDER BY date_m, name后，查询优化器错误选择index2，需要逐行过滤date_m条件，导致性能骤降（从ms级变为秒级）。而index1的存储顺序完全匹配ORDER BY date_m, name，且包含所有WHERE条件字段，优化器的选择不符合预期。

可行解决方案 #

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

直接在查询中添加索引提示，强制优化器选择index1，避免错误选择：

SELECT *
FROM table_name
WHERE user_id = 2
    AND parent_id = 1
    AND date_m > '2018-09-01T11:41:24'::timestamp
ORDER BY date_m, name
LIMIT 100
INDEX ix_table_name_user_id_parent_id_date_m_media_type_name;

2. 创建贴合查询的专用索引 #

如果不想依赖索引提示，可以创建一个更紧凑、完全匹配查询过滤+排序逻辑的索引：

CREATE INDEX IF NOT EXISTS ix_table_name_user_parent_date_name
ON table_name (user_id, parent_id, date_m, name);

这个索引的顺序完美匹配WHERE (user_id, parent_id) + ORDER BY (date_m, name)，优化器会优先选择该索引，同时索引体积更小，查询性能更优。

3. 更新表统计信息 #

优化器选错索引可能是因为表的统计信息过时，执行以下命令更新统计，帮助优化器做出正确判断：

ANALYZE table_name;

4. 调整排序规则匹配现有索引 #

index1的顺序是user_id, parent_id, date_m, media_type, name，如果业务可以接受，将排序规则改为ORDER BY date_m, media_type, name，完全匹配索引的存储顺序，优化器会自动选择index1，无需修改索引或添加提示：

SELECT *
FROM table_name
WHERE user_id = 2
    AND parent_id = 1
    AND date_m > '2018-09-01T11:41:24'::timestamp
ORDER BY date_m, media_type, name
LIMIT 100

验证效果 #

修改后查看执行计划，应显示使用目标索引（index1或新创建的专用索引），执行计划中不会出现Sort步骤，执行时间将回到ms级别，解决性能骤降问题。

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