选择带有偏移限制的查询速度太慢。

Question

我从互联网资源中了解到，当偏移量增加时，查询速度会变慢。但在我的情况下，我认为它太慢了。我正在使用 postgres 9.3。

这是查询语句（id 是主键）：

select * from test_table offset 3900000 limit 100;

它大约需要 10秒 返回数据。我认为这太慢了。表中有大约 4百万 条记录。数据库的总大小为 23GB。

机器配置：

RAM: 12 GB
CPU: 2.30 GHz
Core: 10

我已更改的postgresql.conf文件中的一些值如下。其他值为默认值。

shared_buffers = 2048MB
temp_buffers = 512MB
work_mem = 1024MB
maintenance_work_mem = 256MB
dynamic_shared_memory_type = posix
default_statistics_target = 10000
autovacuum = on
enable_seqscan = off   ## its not making any effect as I can see from Analyze doing seq-scan

除此之外，我还尝试通过更改random_page_cost = 2.0和cpu_index_tuple_cost = 0.0005的值，但结果仍然相同。

查询的Explain（analyze，buffers）结果如下：

"Limit  (cost=10000443876.02..10000443887.40 rows=100 width=1034) (actual time=12793.975..12794.292 rows=100 loops=1)"
"  Buffers: shared hit=26820 read=378984"
"  ->  Seq Scan on test_table  (cost=10000000000.00..10000467477.70 rows=4107370 width=1034) (actual time=0.008..9036.776 rows=3900100 loops=1)"
"        Buffers: shared hit=26820 read=378984"
"Planning time: 0.136 ms"
"Execution time: 12794.461 ms"

全球的人们如何处理Postgres中的这个问题？任何替代方案对我都有帮助。

更新：添加order by id（也尝试了其他索引列），以下是解释：

"Limit  (cost=506165.06..506178.04 rows=100 width=1034) (actual time=15691.132..15691.494 rows=100 loops=1)"
"  Buffers: shared hit=110813 read=415344"
"  ->  Index Scan using test_table_pkey on test_table  (cost=0.43..533078.74 rows=4107370 width=1034) (actual time=38.264..11535.005 rows=3900100 loops=1)"
"        Buffers: shared hit=110813 read=415344"
"Planning time: 0.219 ms"
"Execution time: 15691.660 ms"

- Sabuj Hassan

---

[无关] work_mem = 1024MB 可能太高了，default_statistics_target = 10000 对于一般用途来说也太高了。关闭 autovacuum 是不必要且危险的。你已经关闭 autovacuum 多长时间了？ - wildplasser

ID有多稀疏？一行可以刪除嗎？多久會刪除？'test_table'沒有空隙，但在生產中可能會發生。表定義可以真正幫助否則就是猜測的運動。 - Clodoaldo Neto

@ClodoaldoNeto 可以删除行。例如，10行中有6行的ID为1-10被删除。这就是为什么我现在无法使用“id >”或“id <”。 - Sabuj Hassan

典型的使用情况是什么？用户会直接跳转到第n页并离开，还是会继续向前翻页？ - Clodoaldo Neto

这个回答解决了你的问题吗？改善PostgreSQL中OFFSET的性能 - Mohammed Essehemy

Answer 1

查询速度慢是因为需要定位前offset行并扫描接下来的100行。当处理大量偏移量时，无论进行多少优化都无法改变这一点。

这是因为您的查询使用了offset 3900000，即3.9M行，从而让数据库引擎访问了大量的行。加快查询速度的选项并不多。

超快速的RAM、SSD等设备会有所帮助。但是这只能以一个恒定的因素获得收益，这意味着这只是把问题推迟到了更大的偏移量。

确保表格适合内存，并且还有足够的空间可以提供更大的恒定因素帮助——除了第一次。但是对于足够大的表或索引，这可能是不可能的。

确保进行索引扫描在某种程度上是可行的（请参见velis的答案；它有很多优点）。问题在于，对于所有实际目的，您可以将索引视为存储磁盘位置和索引字段的表格。（它比那更优化，但这是一个合理的第一近似值。）当行数足够多时，您仍然会遇到具有足够大偏移量的问题。

尝试存储和维护行的精确位置也是一种昂贵的方法。（例如，benjist建议这样做。）虽然技术上可行，但它遭受类似于使用树结构中的MPTT所导致的限制：在读取方面你会有很大的收益，但在插入、更新或以需要更新数据大块的方式删除节点时，会产生过多的写入时间。

正如希望更加清晰地了解的那样，在处理如此大的偏移量时，并没有任何真正的神奇方法。通常最好看看其他替代方法。

如果您基于ID（或日期字段或任何其他可索引的字段集）进行分页，则潜在的技巧（例如blogspot使用的技巧）是使您的查询从索引中的任意点开始。

换句话说，不是：

example.com?page_number=[huge]

做类似这样的事情：

example.com?page_following=[id]

这样，您可以保留索引中的位置跟踪，并且查询变得非常快速，因为它可以直接前往正确的起始点，而无需穿过大量行：

select * from foo where ID > [id] order by ID limit 100

自然地，您失去了跳转到例如第3000页的能力。但请认真思考一下：您上次是在哪个网站上跳转到一个巨大的页面而不是直接进入其月度归档或使用其搜索框？

如果您正在分页，但想以任何方式保持页面偏移量，另一种方法是禁止使用更大的页面编号。这并不傻：这就是Google在搜索结果中所做的。运行搜索查询时，Google会给出估计的结果数量（您可以使用explain获得合理的数字），然后允许您浏览前几千个结果--没有更多了。他们这样做的原因之一是出于性能考虑--正是您遇到的问题。

- Denis de Bernardy

---

这可能是最快的解决方案，但需要注意的是它无法实现页面的随机访问，只能提供简单的“上一页”和“下一页”功能（用于分页）。 - pozs

@pozs：确实如此。第一段非常清楚地说明了这一点，我希望如此。在一般情况下，基本上没有什么好的选择。第二个开始：“如果你正在分页”。如果 OP 处于稍微不同的情况下，可能会适用于他的等效物，并且这个答案将希望将他放在正确的轨道上。如果没有，根据我的经验，这是一个无望的事情：无论如何优化都不会使阅读 4M 行 - 以任何顺序 - 保持一致快速。 - Denis de Bernardy

@Denis 谢谢。我之前尝试过了。但是当用户想要跳转到某一页（例如第590页）时，它对我没有帮助。 - Sabuj Hassan

@SabujHassan：对于这种用例，你真的没有太多可以做的了……巨大的偏移量会让数据库引擎在开始输出你要查找的行之前遍历数以亿计的行，无论你希望什么、祈祷什么或者希望什么，都不会让数据库引擎神奇地读取更少的行：你实际上是指示它读取了很多行。当然，超快的硬件或确保进行索引扫描可能会在某种程度上改善情况；但并不是实质性的。当然，确保表适合内存，并有足够的空间，也会改善情况。 - Denis de Bernardy

@Denis，你能否更新你的回答并加上一句话“大偏移跳跃很慢”？ - Sabuj Hassan

Answer 2

我已经赞同了Denis的回答，但我会自己添加一些建议，也许可以为您特定的使用案例带来一些性能优势：

假设您实际的表不是test_table，而是一些巨大的复合查询，可能涉及多个连接。您可以先确定所需的起始ID:

select id from test_table order by id offset 3900000 limit 1

这应该比原始查询快得多，因为它只需要扫描索引而不是整个表。获取此 ID 然后打开了一个用于全面提取的快速索引搜索选项：

select * from test_table where id >= (what I got from previous query) order by id limit 100

- velis

---

不幸的是，在索引扫描中遍历3900001行也可能很慢。 :-) 但是，它应该会快两倍左右，因为只读取一个较小的表（即索引），而不是两个表（索引和实际表）。 - Denis de Bernardy

实际上，它应该比2倍快得多。即使索引页面中没有存储有关记录计数的信息，索引记录也是2列记录（并且是int类型），实际表可能包含更多列，通常具有大型varchar字段。 - velis

这让我找到了正确的方法来优化我的偏移查询，从500000+行的数据库中将查询时间从10秒减少到<200毫秒。诀窍是尽早在查询中执行偏移+限制操作。最初，我选择了一个包含连接和聚合的视图，并在其中进行了偏移，但更好的解决方案是内联视图查询，并将最内层的 from 从 from my_large_table 更改为 from (select * from my_large_table order by id offset 488000 limit 2000)。这可以确保所有昂贵的操作仅针对每次2000个元素进行。 - iFreilicht

Answer 3

您没有说明您的数据是主要只读还是经常更新。如果您只能在一次创建表并且每隔一段时间更新它（比如每几分钟），那么您的问题将很容易解决：

• 添加一个新列 "offset_id"
• 对您按ID排序的完整数据集，通过递增数字来创建一个offset_id：1、2、3、4...
• 使用"where offset_id >= 3900000 limit 100"代替"offset ... limit 100"

- benjist

---

请注意，当您删除一行时，您还需要重新计算每个后续行的“offset_id”。根据表格的大小和行的删除频率，这可能意味着需要进行大量的表写操作。 - Denis de Bernardy

当然。这就是为什么我说它必须始终针对"您的完整数据集"进行操作 - 如果对他的用例来说这是可行的话。他没有说。 - benjist

Answer 4

您可以通过两步来进行优化

第一步：从3900000条记录中获取最大ID

select max(id) (select id from test_table order by id limit 3900000);

然后使用此最大ID获取接下来的100条记录。

select * from test_table id > {上一步中最大的ID} order by id limit 100 ;

这将更快，因为两个查询都将通过ID进行索引扫描。

- Manvendra Jina

---

您在这里将大量行放入内存中。3900000行很容易导致服务器崩溃。 - Pål Thingbø

Answer 5

这样可以以半随机的顺序获取行。您没有在查询中对结果进行排序，因此结果按照文件中存储的方式返回数据。问题在于，当您更新行时，它们的顺序可能会更改。

要解决这个问题，您应该在查询中添加“order by”。这样，查询将以相同的顺序返回行。而且，它还能够使用索引加快查询速度。

所以有两件事情：添加索引，将“order by”添加到查询中。两者都针对同一列。如果您想使用id列，则不需要添加索引，只需将查询更改为类似以下内容的内容：

select * from test_table order by id offset 3900000 limit 100;

- Szymon Lipiński

---

谢谢。但我认为它没有起作用。我有一个id是PK，并尝试使用它进行order by。不起作用。我已经更新了我的问题。还尝试了另一个索引列last_update_date，结果相同。 - Sabuj Hassan

返回一个有序结果显然会使其变慢，尽管对于分页来说是有意义的，如果这是 OP 想要的。 - Clodoaldo Neto

Answer 6

我不知道你的数据的所有细节，但400万行可能有些庞大。如果有合理的方法将表进行分片并将其分成更小的表，可能会很有益。

为了解释这一点，让我举个例子。假设我有一个数据库，在其中有一个名为survey_answer的表，该表变得非常大且非常慢。现在假设这些调查答案都来自一个独特的客户组（我也有一个客户表来跟踪这些客户）。那么我可以做的是创建一个名为survey_answer的表，它不包含任何数据，但是是一个父表，并且拥有许多子表，实际上包含遵循命名格式survey_answer_<clientid>的数据，这意味着我将拥有每个客户的一个子表survey_answer_1、survey_answer_2等等。然后，当我需要选择该客户的数据时，我将使用该表。如果我需要选择所有客户的数据，则可以从父级survey_answer表中选择，但速度较慢。但是对于获取单个客户的数据（这是我主要做的），它将很快。

这是如何拆分数据的一个例子，还有很多其他的方法。另一个例子是，如果我的 survey_answer 表不容易按客户拆分，而我知道通常一次只访问一年的数据，那么我可以根据年份创建子表，比如 survey_answer_2014、survey_answer_2013 等。然后，如果我知道我一次不会访问超过一年的数据，我实际上只需要访问其中可能两个子表就可以获得所需的所有数据。

在您的情况下，我只知道 id。我们也可以通过这个来分割（虽然可能不是最理想的）。假设我们将每个表的行数限制在大约1000000行左右。因此，我们的子表将是 test_table_0000001_1000000、test_table_1000001_2000000、test_table_2000001_3000000、test_table_3000001_4000000 等。因此，不要直接传递偏移量 3900000，而是先进行一些计算，并确定你想要的表是带有偏移量900000的 test_table_3000001_4000000 表。类似这样：

SELECT * FROM test_table_3000001_4000000 ORDER BY id OFFSET 900000 LIMIT 100;

如果分片表不可行，您可能可以使用部分索引来实现类似的功能，但我仍然建议首先进行分片。在这里了解更多关于部分索引的信息。

希望这有所帮助。（此外，我同意Szymon Guz的观点，您需要一个ORDER BY）。

编辑：请注意，如果您需要在获取100个结果之前删除行或选择性地排除行，则按ID进行分片将变得非常困难（正如Denis所指出的那样；而且按ID进行分片本身就不是很好）。但是，如果您只是对数据进行分页，并且只插入或编辑（虽然不常见，但确实会发生；例如日志），则可以合理地按ID进行分片（尽管我仍然会选择其他东西进行分片）。

- Trevor Young

---

这不是一个有效的方法，因为如果行被删除，就不能保证你最终返回第3900000行及以上的行。 - Denis de Bernardy

这取决于数据库的管理方式。有很多情况下，数据永远不会被删除（以便保留历史记录），而是通过更改列中的值来“停用”它。我还说过按ID分片并不理想。如果有更有用的数据可用，则应按更有用的数据进行分片。我只是根据提供的少量信息进行工作。 - Trevor Young

当然，如果您试图对第3900000行及以上的活动行进行分页，仍然不能保证它会在任何特定的分区中。如果有什么不同，那么情况会更糟，因为您需要读取每一行或者需要具有更大的索引以忽略非活动行。 - Denis de Bernardy

是的，但问题在于...数据库表中的4M行实际上只是略微超过微小的数量。问题在于无论是索引条目还是表内行，都无法通过任何优化使读取3.9M行变快；按照现有情况，OP的查询需要读取那么多行。 - Denis de Bernardy

Id并不是最理想的选择，我已经在我的回答中添加了一条注释。由于我们现在已经有很多评论了，我会放弃这个话题。 - Trevor Young

Answer 7

为避免大表的缓慢分页，始终使用自增主键，然后使用以下查询语句：

SELECT * FROM test_table WHERE id > (SELECT min(id) FROM test_table WHERE id > ((1 * 10) - 10)) ORDER BY id DESC LIMIT 10

1：是页面数
10：是每页记录数

已测试，适用于5000万条记录。

- Yassine Khachlek

---

自动递增列不能保证按1递增。您可以手动跳过递增，通过删除行或使用序列缓存。 - Pål Thingbø

Answer 8

如果基于ID而不是偏移/限制进行分页怎么样？

以下查询将给出ID，将所有记录拆分为大小为per_page的块。它不依赖于记录是否被删除。

SELECT id AS from_id FROM (
  SELECT id, (ROW_NUMBER() OVER(ORDER BY id DESC)) AS num FROM test_table
) AS rn
WHERE num % (per_page + 1) = 0;

使用这些 from_IDs，您可以向页面添加链接。使用索引迭代 :from_ids 并将以下链接添加到页面：

<a href="/test_records?from_id=:from_id">:from_id_index</a>

当用户访问页面时，检索ID大于请求的:from_id的记录。

SELECT * FROM test_table WHERE ID >= :from_id ORDER BY id DESC LIMIT :per_page

对于第一页链接，使用from_id=0即可。

<a href="/test_records?from_id=0">1</a>

- Hirurg103

Answer 9

首先，你必须使用order by子句来定义limit和offset，否则会得到不一致的结果。

为了加快查询速度，你可以使用计算索引，但只限于以下条件：

1. 新插入的数据严格按照id顺序排列。 2. 不对id列进行删除或更新操作。

下面是具体步骤：

1. 创建一个行位置函数：

```sql create or replace function id_pos (id) returns bigint as 'select count(id) from test_table where id <= $1;' language sql immutable; ```

2. 在id_pos函数上创建一个计算索引：

```sql create index table_by_pos on test_table using btree(id_pos(id)); ```

使用方法（偏移量为3900000，限制数量为100）：

```sql select * from test_table where id_pos(id) >= 3900000 and sales_pos(day) < 3900100; ```

这样，查询将不会计算前面的3900000条数据，而只会计算后面的100条数据，从而提高了查询速度。

请注意，此方法适用于两种情况，否则位置将发生变化。

- Soni Harriz

---

这不是一个有效的方法，也不是一个有效的不可变函数，因为在表中删除一行将意味着需要重建所有后续行的索引。 - Denis de Bernardy

我正在使用类似这样的方法将数据并行发送到基于云的可视化工具，该工具仅允许追加或替换数据集。我每天重新构建物化视图中的行号，然后发送ID>previous_id LIMIT 200,000的行块。以前我使用日期范围，但我遇到了极其稀疏的数据和一段时间内过多要发送的行之间的冲突。我需要发送表中的所有数据，但确保一次不发送太多行。 - trench

Answer 10

解决这个问题有两种简单的方法

• 将查询分成两个子查询，第一个子查询在仅索引扫描上执行所有繁重的工作，如此处所述
• 创建计算索引来保存偏移量，如此处所述，可以使用窗口函数进行增强。

- Mohammed Essehemy