postgresql 选择带有偏移限制的查询太慢了

Select query with offset limit is too much slow

提问by Sabuj Hassan

I have read from internet resources that a query will be slow when the offset increases. But in my case I think its too much slow. I am using postgres 9.3

我从互联网资源中了解到，当偏移量增加时，查询会变慢。但就我而言，我认为它太慢了。我在用postgres 9.3

Here is the query (idis primary key):

这是查询（id是主键）：

select * from test_table offset 3900000 limit 100;

It returns me data in around 10 seconds. And I think its too much slow. I have around 4 millionrecords in table. Overall size of the database is 23GB.

它返回我周围的数据10 seconds。而且我认为它太慢了。我4 million在表中有记录。数据库的总体大小为23GB.

Machine configuration:

机器配置：

RAM: 12 GB
CPU: 2.30 GHz
Core: 10

Few values from postgresql.conffile which I have changed are as below. Others are default.

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

Apart from these I have also tried by changing the values of random_page_cost = 2.0and cpu_index_tuple_cost = 0.0005and result is same.

除了这些，我也通过改变的值试过random_page_cost = 2.0和cpu_index_tuple_cost = 0.0005和结果是一样的。

Explain (analyze, buffers)result over the query is as below:

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"

How people around the world negotiates with this problem in postgres? Any alternate solution will be helpful for me as well.

世界各地的人们如何在 postgres 中解决这个问题？任何替代解决方案对我也有帮助。

UPDATE::Adding order by id(tried with other indexed column as well) and here is the explain:

更新::添加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"

回答by Denis de Bernardy

It's slow because it needs to locate the top offsetrows and scan the next 100. No amounts of optimization will change that when you're dealing with huge offsets.

它很慢，因为它需要定位顶部的offset行并扫描接下来的 100 行。当您处理巨大的偏移量时，再多的优化也不会改变这一点。

This is because your query literally instructthe DB engine to visit lots of rows by using offset 3900000-- that's 3.9M rows. Options to speed this up somewhat aren't many.

这是因为您的查询字面上指示数据库引擎通过使用访问大量行offset 3900000- 即 3.9M 行。加快速度的选项并不多。

Super-fast RAM, SSDs, etc. will help. But you'll only gain by a constant factor in doing so, meaning it's merely kicking the can down the road until you reach a larger enough offset.

超快 RAM、SSD 等将有所帮助。但是这样做只会使您获得一个恒定的因素，这意味着它只是将罐子推倒在地，直到您达到足够大的偏移量。

Ensuring the table fits in memory, with plenty more to spare will likewise help by a larger constant factor -- except the first time. But this may not be possible with a large enough table or index.

确保 table 适合内存，有更多的空闲空间同样会通过更大的常数因子来帮助 -除了第一次。但是对于足够大的表或索引，这可能是不可能的。

Ensuring you're doing index-only scans will work to an extent. (See velis' answer; it has a lot of merit.) The problem here is that, for all practical purposes, you can think of an index as a table storing a disk location and the indexed fields. (It's more optimized than that, but it's a reasonable first approximation.) With enough rows, you'll still be running into problems with a larger enough offset.

确保您正在执行仅索引扫描将在一定程度上起作用。（请参阅 velis 的回答；它有很多优点。）这里的问题是，出于所有实际目的，您可以将索引视为存储磁盘位置和索引字段的表。（它比那更优化，但它是合理的第一个近似值。）如果行足够多，您仍然会遇到偏移量足够大的问题。

Trying to store and maintain the precise position of the rows is bound to be an expensive approach too.(This is suggested by e.g. benjist.) While technically feasible, it suffers from limitations similar to those that stem from using MPTT with a tree structure: you'll gain significantly on reads but will end up with excessive write times when a node is inserted, updated or removed in such a way that large chunks of the data needs to be updated alongside.

尝试存储和维护行的精确位置也必然是一种昂贵的方法。（这是由例如 benjist 建议的。）虽然技术上可行，但它受到的限制类似于使用带有树结构的 MPTT 所产生的限制：您将在读取上获得显着收益，但当以需要同时更新大量数据的方式插入、更新或删除节点时，最终会导致写入时间过长。

As is hopefully more clear, there isn't any real magic bullet when you're dealing with offsets this large. It's often better to look at alternative approaches.

希望更清楚的是，当您处理如此大的偏移量时，没有任何真正的灵丹妙药。查看替代方法通常会更好。

If you're paginating based on the ID (or a date field, or any other indexable set of fields), a potential trick (used by blogspot, for instance) would be to make your query start at an arbitrary point in the index.

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

Put another way, instead of:

换一种方式，而不是：

example.com?page_number=[huge]

Do something like:

做类似的事情：

example.com?page_following=[huge]

That way, you keep a trace of where you are in your index, and the query becomes very fast because it can head straight to the correct starting point without plowing through a gazillion rows:

这样，您可以跟踪您在索引中的位置，并且查询变得非常快，因为它可以直接前往正确的起点，而无需翻阅无数行：

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

Naturally, you lose the ability to jump to e.g. page 3000. But give this some honest thought: when was the last time you jumped to a huge page number on a site instead of going straight for its monthly archives or using its search box?

自然地，您将无法跳转到例如第 3000 页。但是请诚实地思考一下：您最后一次跳转到网站上的大页码而不是直接访问其每月存档或使用其搜索框是什么时候？

If you're paginating but want to keep the page offset by any means, yet another approach is to forbid the use of larger page number. It's not silly: it's what Google is doing with search results. When running a search query, Google gives you an estimate number of results (you can get a reasonable number using explain), and then will allow you to brows the top few thousand results -- nothing more. Among other things, they do so for performance reasons -- precisely the one you're running into.

如果您正在分页但想以任何方式保持页面偏移，另一种方法是禁止使用更大的页码。这并不愚蠢：这就是 Google 对搜索结果所做的。运行搜索查询时，Google 会为您提供一个估计的结果数量（您可以使用 获得合理的数量explain），然后允许您浏览前几千个结果——仅此而已。除其他外，他们这样做是出于性能原因——正是您遇到的原因。

回答by velis

I have upvoted Denis's answer, but will add a suggestion myself, perhaps it can be of some performance benefit for your specific use-case:

我已经赞成丹尼斯的回答，但我自己会添加一个建议，也许它可以为您的特定用例带来一些性能优势：

Assuming your actual table is not test_table, but some huge compound query, possibly with multiple joins. You could first determine the required starting id:

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

select id from test_table order by id offset 3900000 limit 1

This should be much faster than original query as it only requires to scan the index vs the entire table. Getting this id then opens up a fast index-search option for full fetch:

这应该比原始查询快得多，因为它只需要扫描索引与整个表。获取此 id 然后打开一个快速索引搜索选项以进行完全获取：

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

回答by benjist

You didn't say if your data is mainly read-only or updated often. If you can manage to create your table at one time, and only update it every now and then (say every few minutes) your problem will be easy to solve:

您没有说明您的数据是主要只读还是经常更新。如果您可以设法一次性创建您的表，并且只时不时地（例如每隔几分钟）更新它，您的问题将很容易解决：

• Add a new column "offset_id"
• For your complete data set ordered by ID, create an offset_id simply by incrementing numbers: 1,2,3,4...
• Instead of "offset ... limit 100" use "where offset_id >= 3900000 limit 100"

• 添加新列“offset_id”
• 对于按 ID 排序的完整数据集，只需通过增加数字来创建 offset_id：1,2,3,4...
• 使用“where offset_id >= 3900000 limit 100”代替“offset ... limit 100”

回答by Manvendra Jina

you can optimise in two steps

您可以分两步优化

First get maximum id out of 3900000 records

首先从 3900000 条记录中获取最大 id

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

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

Then use this maximum id to get the next 100 records.

然后使用这个最大 id 来获取接下来的 100 条记录。

select * from test_table id > {max id from previous step) order by id limit 100 ;

select * from test_table id > {max id from previous step) order by id limit 100 ;

It will be faster as both queries will do index scan by id.

它会更快，因为两个查询都将按 id 进行索引扫描。

回答by Szymon Lipiński

This way you get the rows in semi-random order. You are not ordering the results in a query, so as a result, you get the data as it is stored in the files. The problem is that when you update the rows, the order of them can change.

这样你就可以半随机顺序获得行。您不是在查询中对结果进行排序，因此，您将获得存储在文件中的数据。问题是当你更新行时，它们的顺序可能会改变。

To fix that you should add order byto the query. This way the query will return the rows in the same order. What's more then it will be able to use an index to speed the query up.

要解决这个问题，您应该添加order by到查询中。这样查询将以相同的顺序返回行。更重要的是，它将能够使用索引来加快查询速度。

So two things: add an index, add order byto the query. Both to the same column. If you want to use the id column, then don't add index, just change the query to something like:

所以两件事：添加索引，添加order by到查询。两者都在同一列。如果要使用 id 列，则不要添加索引，只需将查询更改为：

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

回答by Trevor Young

I don't know all of the details of your data, but 4 million rows can be a little hefty. If there's a reasonable way to shard the table and essentially break it up into smaller tables it could be beneficial.

我不知道您数据的所有详细信息，但 400 万行可能有点大。如果有一种合理的方法来对表进行分片并基本上将其分解为更小的表，那可能是有益的。

To explain this, let me use an example. let's say that I have a database where I have a table called survey_answer, and it's getting very large and very slow. Now let's say that these survey answers all come from a distinct group of clients (and I also have a client table keeping track of these clients). Then something I could do is I could make it so that I have a table called survey_answer that doesn't have any data in it, but is a parent table, and it has a bunch of child tables that actually contain the data the follow the naming format survey_answer_<clientid>, meaning that I'd have child tables survey_answer_1, survey_answer_2, etc., one for each client. Then when I needed to select data for that client, I'd use that table. If I needed to select data across all clients, I can select from the parent survey_answer table, but it will be slow. But for getting data for an individual client, which is what I mostly do, then it would be fast.

为了解释这一点，让我举一个例子。假设我有一个数据库，其中有一个名为survey_answer 的表，它变得非常大而且非常慢。现在假设这些调查答案都来自一组不同的客户（我还有一个客户表来跟踪这些客户）。然后我可以做的就是我可以制作一个名为survey_answer的表，其中没有任何数据，但它是一个父表，并且它有一堆子表，这些表实际上包含以下数据命名格式survey_answer_<clientid>，这意味着我有子表survey_answer_1、survey_answer_2 等，每个客户一个。然后，当我需要为该客户选择数据时，我会使用该表。如果我需要在所有客户端中选择数据，我可以从父survey_answer 表中选择，但是会很慢。但是为了获取单个客户的数据，这是我最常做的事情，那么它会很快。

This is one example of how to break up data, and there are many others. Another example would be if my survey_answer table didn't break up easily by client, but instead I know that I'm typically only accessing data over a year period of time at once, then I could potentially make child tables based off of year, such as survey_answer_2014, survey_answer_2013, etc. Then if I know that I won't access more than a year at a time, I only really need to access maybe two of my child tables to get all the data I need.

这是如何拆分数据的一个示例，还有许多其他示例。另一个例子是，如果我的survey_answer表没有被客户轻易分解，但我知道我通常只一次访问一年内的数据，那么我可能会根据年份制作子表，例如survey_answer_2014、survey_answer_2013等。然后如果我知道我一次访问的时间不会超过一年，我真的只需要访问我的两个子表来获取我需要的所有数据。

In your case, all I've been given is perhaps the id. We can break it up by that as well (though perhaps not as ideal). Let's say that we break it up so that there's only about 1000000 rows per table. So our child tables would be test_table_0000001_1000000, test_table_1000001_2000000, test_table_2000001_3000000, test_table_3000001_4000000, etc. So instead of passing in an offset of 3900000, you'd do a little math first and determine that the table that you want is table test_table_3000001_4000000 with an offset of 900000 instead. So something like:

在你的情况下，我得到的可能只是id。我们也可以这样分解它（尽管可能不那么理想）。假设我们将其分解为每个表只有大约 1000000 行。因此，我们的子表将test_table_0000001_1000000，test_table_1000001_2000000，test_table_2000001_3000000，test_table_3000001_4000000等，所以，而不是传递一个的390万偏移，你首先做一些数学并确定所需的表是表test_table_3000001_4000000有90万的偏移反而。所以像：

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

Now if sharding the table is out of the question, you might be able to use partial indexes to do something similar, but again, I'd recommend sharding first. Learn more about partial indexes here.

现在，如果对表进行分片是不可能的，您也许可以使用部分索引来做类似的事情，但同样，我建议先进行分片。在此处了解有关部分索引的更多信息。

I hope that helps. (Also, I agree with Szymon Guz that you want an ORDER BY).

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

Edit:Note that if you need to delete rows or selectively exclude rows before getting your result of 100, then sharding by id will become very hard to deal with (as pointed out by Denis; and sharding by id is not great to begin with). But if your 'just' paginating the data, and you only insert or edit (not a common thing, but it does happen; logs come to mind), then sharding by id can be done reasonably (though I'd still choose something else to shard on).

编辑：请注意，如果您需要在获得 100 的结果之前删除行或有选择地排除行，那么按 id 分片将变得非常难以处理（正如 Denis 所指出的；并且按 id 分片开始时并不好） . 但是，如果您“只是”对数据进行分页，并且您只插入或编辑（这不是常见的事情，但确实会发生；我会想到日志），那么可以合理地按 id 进行分片（尽管我仍然会选择其他内容）分片）。

回答by Soni Harriz

First, you have to define limit and offset with order by clause or you will get inconsistent result.

首先，您必须使用 order by 子句定义限制和偏移量，否则您将得到不一致的结果。

To speed up the query, you can have a computed index, but only for these condition :

为了加快查询速度，您可以有一个计算索引，但仅限于以下条件：

1. Newly inserted data is strictly in id order
2. No delete nor update on column id

1. 新插入的数据严格按照id顺序
2. 没有删除或更新列 id

Here's how You can do it :

你可以这样做：

1. Create a row position function

1. 创建行位置函数

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

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

1. Create a computed index on id_pos function

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

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

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

Here's how You call it (offset 3900000 limit 100):

这是您如何称呼它的（偏移 3900000 限制 100）：

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

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

This way, the query will not compute the 3900000 offset data, but only will compute the 100 data, making it much faster.

这样，查询将不会计算 3900000 个偏移量数据，而只会计算 100 个数据，从而使其更快。

Please note the 2 conditions where this approach can take place, or the position will change.

请注意可以发生这种方法的 2 个条件，否则位置将发生变化。

回答by Hirurg103

How about if paginate based on IDs instead of offset/limit?

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

The following query will give IDs which split all the records into chunks of size per_page. It doesn't depend on were records deleted or not

以下查询将给出将所有记录拆分为大小块的 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;

With these from_IDs you can add links to the page. Iterate over :from_ids with index and add the following link to the page:

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

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

When user visits the page retrieve records with ID which is greater than requested :from_id:

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

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

For the first page link with from_id=0will work

对于第一页链接from_id=0将起作用

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