PostgreSQL中ORDER BY x LIMIT 1与LIMIT 2的性能差异排查

我有如下数据表，需要获取特定test_id的行并按created_at排序。两列均已建立索引，但发现使用LIMIT 1时性能远低于其他LIMIT值，其他LIMIT值（如LIMIT 10、LIMIT 100）的性能与LIMIT 2一致。

数据表结构 #

数据统计 #

2418139 Distinct rows (test_id)
283036392 Distinct rows (created_at)
283085093 Total rows

查询性能差异 #

使用LIMIT 2时的执行计划 #

EXPLAIN ANALYZE VERBOSE SELECT "my_table"."id" FROM "my_table" WHERE ("my_table"."test_id" = '00018843-d632-42d4-b832-cf9d6df8e454'::uuid) ORDER BY "my_table"."created_at" ASC LIMIT 2;
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=13137.72..13137.72 rows=2 width=56) (actual time=0.033..0.034 rows=1 loops=1)
   Output: id, identifier, type, created_at
   ->  Sort  (cost=13137.72..13145.82 rows=3242 width=56) (actual time=0.033..0.033 rows=1 loops=1)
         Output: id, created_at
         Sort Key: my_table.created_at
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using my_table_test_id_d24b61ed on public.my_table  (cost=0.57..13105.30 rows=3242 width=56) (actual time=0.014..0.015 rows=1 loops=1)
               Output: id, created_at
               Index Cond: (my_table.test_id = '00018843-d632-42d4-b832-cf9d6df8e454'::uuid)
 Query Identifier: 5848686225449084285
 Planning Time: 0.093 ms
 Execution Time: 0.065 ms
(12 rows)

使用LIMIT 1时的执行计划 #

EXPLAIN ANALYZE VERBOSE SELECT "my_table"."id" FROM "my_table" WHERE ("my_table"."test_id" = '00018843-d632-42d4-b832-cf9d6df8e454'::uuid) ORDER BY "my_table"."created_at" ASC LIMIT 1;
                                                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..10735.90 rows=1 width=56) (actual time=73459.135..73459.135 rows=1 loops=1)
   Output: id, created_at
   ->  Index Scan using my_table_created_at_231d382e on public.my_table  (cost=0.57..34803947.27 rows=3242 width=56) (actual time=73459.134..73459.134 rows=1 loops=1)
         Output: id, created_at
         Filter: (my_table.test_id = '00018843-d632-42d4-b832-cf9d6df8e454'::uuid)
         Rows Removed by Filter: 110868840
 Query Identifier: 5848686225449084285
 Planning Time: 0.085 ms
 Execution Time: 73459.155 ms
(9 rows)

优化器选择了错误的执行路径，预估成本与实际耗时差异极大，我已尝试以下操作但无改善：

• 手动执行VACUUM ANALYZE（上述查询计划为执行后的结果）
• 调整work_mem（默认值为4MB）
• 调整effective_cache_size（默认值为266313056kB）

环境信息 #

• PostgreSQL版本：14.10
• 部署环境：AWS RDS Aurora PostgreSQL，由AWS CDK部署

需求 #

我使用Django 4.2作为应用框架，希望找到适配其标准ORM的解决方案，修复该问题或优化优化器行为。

解决方案 #

1. 创建复合索引（最优方案）
   针对test_id过滤+created_at排序的查询模式，创建复合索引：

CREATE INDEX idx_my_table_test_id_created_at ON my_table (test_id, created_at);

该索引能让PostgreSQL直接获取符合条件的有序数据，无需额外排序或低效扫描，无论LIMIT值是多少都能高效执行。

在Django ORM中，可通过模型Meta类定义索引：

from django.db import models

class MyTable(models.Model):
    id = models.UUIDField(primary_key=True, unique=True)
    test_id = models.UUIDField(db_index=True)
    created_at = models.DateTimeField(db_index=True)

    class Meta:
        indexes = [
            models.Index(fields=['test_id', 'created_at'], name='idx_my_table_test_id_created_at'),
        ]

执行makemigrations和migrate即可完成索引创建。

2. 强制使用指定索引（临时方案）
   若无法立即创建复合索引，可通过Django的RawSQL添加查询提示，强制优化器使用test_id的索引：

from django.db.models import RawSQL

MyTable.objects.filter(test_id='00018843-d632-42d4-b832-cf9d6df8e454')\
    .order_by('created_at')\
    .annotate(_=RawSQL('/*+ IndexScan(my_table my_table_test_id_d24b61ed) */', []))\
    .first()

注意：查询提示为PostgreSQL 12+特性，需根据实际索引名称调整。

3. 调整优化器成本参数
   微调random_page_cost参数（默认值为4），降低随机IO成本预估，让优化器更倾向于索引扫描：

SET random_page_cost = 1.1; -- 适合SSD存储环境

此调整为会话/全局级，需谨慎测试避免影响其他查询。

4. 临时替代方案
   在Django中用order_by('created_at')[:2].first()替代first()，虽然会获取2条数据，但性能与LIMIT 2一致，最终只取第一条结果。

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