时间戳

首页 > 解决方案 > PostgreSQL - ORDER BY with LIMIT 未按预期使用索引

postgresql - PostgreSQL - ORDER BY with LIMIT 未按预期使用索引

问题描述

我们有两个表 -event_deltas和deltas_to_retrieve- 在相同的两列上都有 BTREE 索引：

CREATE TABLE event_deltas
(
  event_id     UUID REFERENCES events(id) NOT NULL,
  version      INT NOT NULL,
  json_patch   JSONB NOT NULL,
  PRIMARY KEY (event_id, version)
);

CREATE TABLE deltas_to_retrieve(event_id UUID NOT NULL, version INT NOT NULL);
CREATE UNIQUE INDEX event_id_version ON deltas_to_retrieve (event_id, version);

就表大小而言，deltas_to_retrieve是一个约 500 行的小型查找表。该event_deltas表包含约 7,000,000 行。由于后一个表的大小，我们希望限制一次检索的数量。因此，查询表如下：

SELECT ed.event_id, ed.version
FROM deltas_to_retrieve zz, event_deltas ed
WHERE zz.event_id = ed.event_id
  AND ed.version > zz.version
ORDER BY ed.event_id, ed.version
LIMIT 5000;

如果没有LIMIT，例如，我正在查看查询返回 ~30,000 行。

这个查询的奇怪之处在于ORDER BY. 由于现有的索引，数据会按照我们想要的顺序返回，不管有没有它。我宁愿把明确的保留在ORDER BY那里，这样我们就可以防止未来的变化，以及可读性等。然而，就目前情况而言，它对性能有很大的负面影响。

根据文档：

一个重要的特殊情况是 ORDER BY 与 LIMIT n 的组合：显式排序必须处理所有数据以识别前 n 行，但如果有与 ORDER BY 匹配的索引，则可以直接检索前 n 行，根本不扫描其余部分。

这让我认为，鉴于我们已经拥有的索引，ORDER BY根本不应该减慢查询速度。但是，在实践中，我看到执行时间约为 10 秒，ORDER BY而没有 <1s。我已包括以下输出的计划EXPLAIN：

没有 ORDER BY

只是EXPLAIN：

QUERY PLAN
Limit  (cost=0.56..20033.38 rows=5000 width=20)
  ->  Nested Loop  (cost=0.56..331980.39 rows=82859 width=20)
        ->  Seq Scan on deltas_to_retrieve zz  (cost=0.00..9.37 rows=537 width=20)
        ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..616.66 rows=154 width=20)
              Index Cond: ((event_id = zz.event_id) AND (version > zz.version))

更详细EXPLAIN (ANALYZE, BUFFERS)：

QUERY PLAN
Limit  (cost=0.56..20039.35 rows=5000 width=20) (actual time=3.675..2083.063 rows=5000 loops=1)
"  Buffers: shared hit=1450 read=4783, local hit=2"
  ->  Nested Loop  (cost=0.56..1055082.88 rows=263260 width=20) (actual time=3.673..2080.745 rows=5000 loops=1)
"        Buffers: shared hit=1450 read=4783, local hit=2"
        ->  Seq Scan on deltas_to_retrieve zz  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.022..0.307 rows=241 loops=1)
              Buffers: local hit=2
        ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..619.07 rows=155 width=20) (actual time=1.317..8.612 rows=21 loops=241)
              Index Cond: ((event_id = zz.event_id) AND (version > zz.version))
              Heap Fetches: 5000
              Buffers: shared hit=1450 read=4783
Planning Time: 1.150 ms
Execution Time: 2084.647 ms

订购方式

只是EXPLAIN：

QUERY PLAN
Limit  (cost=0.84..929199.06 rows=5000 width=20)
  ->  Merge Join  (cost=0.84..48924145.53 rows=263260 width=20)
        Merge Cond: (ed.event_id = zz.event_id)
        Join Filter: (ed.version > zz.version)
        ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..48873353.76 rows=12318733 width=20)
        ->  Materialize  (cost=0.28..6178.03 rows=1700 width=20)
              ->  Index Only Scan using event_id_version on deltas_to_retrieve zz  (cost=0.28..6173.78 rows=1700 width=20)

更详细EXPLAIN (ANALYZE, BUFFERS)：

QUERY PLAN
Limit  (cost=0.84..929199.06 rows=5000 width=20) (actual time=4457.770..506706.443 rows=5000 loops=1)
"  Buffers: shared hit=78806 read=1071004 dirtied=148, local hit=63"
  ->  Merge Join  (cost=0.84..48924145.53 rows=263260 width=20) (actual time=4457.768..506704.815 rows=5000 loops=1)
        Merge Cond: (ed.event_id = zz.event_id)
        Join Filter: (ed.version > zz.version)
"        Buffers: shared hit=78806 read=1071004 dirtied=148, local hit=63"
        ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..48873353.76 rows=12318733 width=20) (actual time=4.566..505443.407 rows=1813438 loops=1)
              Heap Fetches: 1814767
              Buffers: shared hit=78806 read=1071004 dirtied=148
        ->  Materialize  (cost=0.28..6178.03 rows=1700 width=20) (actual time=0.063..2.524 rows=5000 loops=1)
              Buffers: local hit=63
              ->  Index Only Scan using event_id_version on deltas_to_retrieve zz  (cost=0.28..6173.78 rows=1700 width=20) (actual time=0.056..0.663 rows=78 loops=1)
                    Heap Fetches: 78
                    Buffers: local hit=63
Planning Time: 1.088 ms
Execution Time: 506709.819 ms

我在阅读这些计划方面不是很有经验，但它显然认为它需要检索所有内容，对其进行排序然后返回 TOP N，而不是仅使用索引获取第一个 N。Seq Scan它在较小的桌子上做 adeltas_to_retrieve而不是Index Only Scan- 这是问题所在吗？那张表很小（约 500 行），所以我想知道它是否只是因为这个而不费心使用索引？

Postgres 版本：11.12

标签： postgresql

解决方案

升级到 Postgres 13 为我们解决了这个问题，引入了增量排序。从有关该功能的一些文档中：

增量排序：排序是一项性能密集型任务，因此该领域的每一项改进都会产生影响。现在 PostgreSQL 13 引入了增量排序，它利用了查询的早期排序并仅对增量未排序字段进行排序，从而增加了排序块适合内存的机会，从而提高了性能。

新的查询计划EXPLAIN如下，查询现在始终在 <500 毫秒内完成：

QUERY PLAN
Limit  (cost=71.06..820.32 rows=5000 width=20)
  ->  Incremental Sort  (cost=71.06..15461.82 rows=102706 width=20)
"        Sort Key: ed.event_id, ed.version"
        Presorted Key: ed.event_id
        ->  Nested Loop  (cost=0.84..6659.05 rows=102706 width=20)
              ->  Index Only Scan using event_id_version on deltas_to_retrieve zz  (cost=0.28..1116.39 rows=541 width=20)
              ->  Index Only Scan using event_deltas_pkey on event_deltas ed  (cost=0.56..8.35 rows=190 width=20)
                    Index Cond: ((event_id = zz.event_id) AND (version > zz.version))

推荐阅读