主页 / dba / 问题 / 110636
Accepted
happy_marmoset
Asked: 2015-08-11 23:54:24 +0800 CST

添加 ORDER BY 时使用 LIMIT 的查询性能不佳？

  • 772

给定表格：

柱子 类型 修饰符 贮存
ID 大整数 不为空默认 nextval('items_id_seq'::regclass) 清楚的
数据 文本 不为空 扩展的
object_id 大整数 不为空 清楚的

和索引：

  • "items_pkey" 主键，btree (id)
  • “items_object_id_idx” btree (object_id)

当我执行时：

SELECT *
FROM items
WHERE object_id = 123
LIMIT 1;

它很快返回 0 行。但是，当我使用 执行此查询时ORDER BY，它会挂起很长时间：

SELECT *
FROM items
WHERE object_id = 123
ORDER BY id DESC  -- I added the ORDER BY
LIMIT 1;

是什么解释了这种差异？

查询计划

快速查询（无ORDER BY）

                                                                          QUERY PLAN                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms

慢查询（带ORDER BY）

                                                                            QUERY PLAN                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms
postgresql postgresql-performance

2 个回答

  1. 试图解释为什么两个查询之间的性能存在差异。

    这一个：SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1满足任意一行的匹配object_id，所以索引 onobject_id是一个自然的选择。该查询需要最少的 I/O：索引扫描以查找第一个匹配值加上一次堆读取以获取整行。

    替代方案：SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1要求所有匹配的行object_id按另一列排序id，然后返回最大值为 的行id。如果要使用索引，则object_id需要执行以下操作：扫描索引以查找每个匹配项object_id；对于每场比赛去获取实际的行；然后对所有获取的行进行排序id并返回最大的行id。

    优化器选择的替代方案，大概是基于object_id直方图，是：id向后扫描整个索引；对于每个值，去获取行并检查值是否object_id匹配；返回第一个匹配的行，它将具有最大可能id值。这种替代方法避免了对行进行排序，所以我猜优化器更喜欢在object_id.

    索引的存在(object_id asc, id desc)允许另一种选择：扫描此新索引以查找与提供的object_id值匹配的第一个条目，根据定义，该条目将具有最高id值；去获取一个匹配的行并返回。显然，这是最有效的方法。

    • 19
  2. Best Answer

    我发现有两种方法可以加快速度，

    • 添加更好的索引。
    • 使用优化围栏优化查询。

    指数

    一种方法是添加更好的索引，如mustaccio's answer中所示。这具有产生最快查询的优点。

    优化围栏

    另一种方法是通过将查询包装在子选择中来隔离查询。请注意，内部查询没有LIMIT. 此解决方案可能非常缓慢。您可以看到有 4239 行匹配object_id = 123。这意味着虽然您可以立即恢复这些行（因为它是索引扫描并且非常快），但您仍然必须在之后对它们进行排序。Mustaccio 解决方案包括让它们按索引排序（使其明显更快）。

    SELECT *
FROM (
     SELECT *
     FROM items 
     WHERE object_id = 123
     ORDER BY id DESC
) AS items
ORDER BY id DESC
LIMIT 1;

                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=16629.84..16629.86 rows=1 width=59)
   ->  Sort  (cost=16629.84..16640.44 rows=4239 width=59)
         Sort Key: items.id
         ->  Bitmap Heap Scan on items  (cost=125.42..16374.45 rows=4239 width=59)
               Recheck Cond: (object_id = 123::bigint)
                   ->  Bitmap Index Scan on items_object_id_idx  (cost=0.00..124.36 rows=4239 width=0)
                     Index Cond: (object_id = 123::bigint)
    • 9

相关问题