在大表上使用LIMIT和OFFSET使SELECT查询更快

Question

我有一个表格中超过一千万条记录。

SELECT * FROM tbl ORDER BY datecol DESC
LIMIT 10
OFFSET 999990

在explain.depesz.com上，EXPLAIN ANALYZE的输出结果如下。
执行以上查询大约需要10秒钟。我该如何使它运行更快？

更新

通过使用子查询，执行时间减少了一半。

SELECT * FROM tbl where id in 
(SELECT id FROM tbl ORDER BY datecol DESC LIMIT 10 OFFSET 999990)

EXPLAIN ANALYZE 在 explain.depesz.com 上的输出结果。

- Sumither S

---

DATECOLUMN 上有索引吗？高偏移量只是分页的示例，还是可以用另一种方式实现？ - deceze

没有在DATECOLUMN上创建索引，我正在使用此查询来进行网格操作，因此我需要分页。还有其他方法吗？ - Sumither S

在这个列上加一个索引。 - deceze

请查看http://stackoverflow.com/tags/postgresql/info和http://stackoverflow.com/tags/postgresql-performance/info，特别是有关所需详细信息的建议。 - Craig Ringer

MySQL和Postgresql在这个问题上非常相似。请参见https://dev59.com/8G855IYBdhLWcg3wMRRW - Zags

Answer 1

您需要在用于 ORDER BY 的列上创建索引。最好按相同的排序顺序，但 PostgreSQL 可以以几乎相同的速度向后扫描索引。

CREATE INDEX tbl_datecol_idx ON tbl (datecol DESC);

在当前手册中了解更多关于索引的信息和CREATE INDEX。
使用EXPLAIN ANALYZE进行测试，以获取实际时间和查询计划。

当然，所有关于性能优化的常规建议同样适用。

- Erwin Brandstetter

Answer 2

我曾试图使用一个非常大的表（>100m条记录）来做类似的事情，发现使用Offset/Limit会影响性能。 使用Limit 1的前10m条记录的Offset需要约1.5分钟才能检索到，而且随着记录数量的增加呈指数级增长。 当记录达到50m时，即使使用子查询，每个选择也需要3分钟。

我看到了一篇文章这里详细介绍了有用的替代方法。

我稍微修改了一下以适应我的需求，并想出了一种给我带来相当快速结果的方法。

CREATE TEMPORARY TABLE 
just_index AS SELECT ROW_NUMBER() 
OVER (ORDER BY [VALUE-You-need]), [VALUE-You-need] 
FROM [your-table-name];

这是一次性的 - 大约花了4分钟，但我得到了我想要的所有值。 接下来是创建一个函数，该函数将循环使用我需要的“偏移量”：

create or replace
function GetOffsets () 
returns void as $$ 
declare 
-- For this part of the function I only wanted values after 90 million up to 120 million
counter bigint := 90000000;
maxRows bigInt := 120000000;
begin
drop table if exists OffsetValues;
create temp table OffsetValues
(
    offset_myValue bigint
);

while counter <= maxRows loop 
    insert into OffsetValues(offset_myValue)
    select [VALUE-You-need] from just_index where row_number > counter
    limit 1;

    -- here I'm looping every 500,000 records - this is my 'Offset'
    counter := counter + 500000 ;
end loop ;
end ;$$ LANGUAGE plpgsql;

然后运行该函数：

select GetOffsets();

再次提醒，一次性的时间（我从获取一个偏移值需要约3分钟的时间，变成了现在只需要3毫秒）。 然后从临时表中进行选择：

select * from OffsetValues;

这对我的性能表现非常好 - 如果可以的话，我想我以后不会再使用偏移量了。

希望这能提高您的大型表的性能。

- John Youngtree