PostgreSQL Order By索引调优：全量数据排序查询性能优化求助

需求：获取指定business_id下未归档的全部宾客列表，按last_name字母序排序，用于自动补全选择框，无法分页，数据库中该业务下有5000+宾客。

已创建以下索引：

• index_guests_on_last_name
• index_guests_on_business_id_last_name
• index_guests_on_business_id_archived_last_name

无LIMIT时的查询及执行计划 #

执行以下查询时，执行计划仅使用index_guests_on_business_id，耗时超100ms：

explain analyze
SELECT "guests"."id", "guests"."first_name", "guests"."last_name" 
FROM "guests" 
WHERE "guests"."business_id" = 12345 AND "guests"."archived" = false 
ORDER BY last_name ASC

执行计划输出：

Sort  (cost=12272.74..12284.51 rows=4710 width=26) (actual time=116.785..118.183 rows=5696 loops=1)
  Sort Key: last_name
  Sort Method: quicksort  Memory: 654kB
     ->  Bitmap Heap Scan on guests  (cost=94.80..11985.39 rows=4710 width=26) (actual time=3.336..16.203 rows=5696 loops=1)
        Recheck Cond: (business_id = 12345)
        Filter: (NOT archived)
        Rows Removed by Filter: 1
        Heap Blocks: exact=4517
        ->  Bitmap Index Scan on index_guests_on_business_id  (cost=0.00..93.62 rows=4960 width=0) (actual time=2.092..2.092 rows=5697 loops=1)
              Index Cond: (business_id = 12345)
Planning time: 0.162 ms
Execution time: 118.606 ms

添加LIMIT 100后的查询及执行计划 #

添加LIMIT 100后，查询耗时不到1毫秒，执行计划使用了index_guests_on_business_id_last_name：

explain analyze
SELECT "guests"."id", "guests"."first_name", "guests"."last_name" 
FROM "guests" 
WHERE "guests"."business_id" = 12345 AND "guests"."archived" = false 
ORDER BY last_name ASC LIMIT 100

执行计划输出：

Limit  (cost=0.42..317.05 rows=100 width=26) (actual time=0.034..0.339 rows=100 loops=1)
  ->  Index Scan using index_guests_on_business_id_last_name on guests  (cost=0.42..15704.88 rows=4960 width=26) (actual time=0.032..0.312 rows=100 loops=1)
        Index Cond: (business_id = 12345)
Planning time: 0.321 ms
Execution time: 0.567 ms

提问 #

如何优化全量返回数据时的查询性能，使其达到类似加LIMIT时的效果？

1. 强制使用匹配度更高的索引 #

PostgreSQL优化器在返回全量数据时，可能误判Bitmap Heap Scan+排序的成本更低。可以用索引提示强制指定使用index_guests_on_business_id_archived_last_name，该索引完全匹配过滤条件和排序需求，能避免后续排序操作：

explain analyze
SELECT "guests"."id", "guests"."first_name", "guests"."last_name" 
FROM "guests" 
WHERE "guests"."business_id" = 12345 AND "guests"."archived" = false 
ORDER BY last_name ASC
-- 强制使用目标索引
INDEX index_guests_on_business_id_archived_last_name;

2. 重建为覆盖索引 #

如果现有索引未包含查询所需的全部字段，数据库需要回表读取堆数据，会拖慢性能。重建包含id、first_name的覆盖索引：

CREATE INDEX index_guests_on_business_id_archived_last_name_covering
ON guests (business_id, archived, last_name)
INCLUDE (id, first_name);

覆盖索引可让数据库直接从索引中获取所有需要的数据，无需回表，大幅提升全量查询效率。

3. 更新表统计信息 #

优化器的决策依赖准确的统计数据，若统计信息过时，可能导致执行计划选择失误。更新表的统计信息：

ANALYZE guests;

4. 调整排序内存参数 #

当前查询排序使用了654KB内存，若内存不足会触发磁盘排序，进一步增加耗时。临时调高会话级别的排序内存：

SET work_mem = '2MB';

若需要全局生效，可修改postgresql.conf中的work_mem配置后重启服务。

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