快速在大表上使用LIMIT和OFFSET进行SELECT [英] Make SELECT with LIMIT and OFFSET on big table fast

问题描述

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

I have more than 10 million records in a table.

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

在EXPLAIN ANALYZE 的输出define.depesz.com/s/9iee rel = nofollow> explain.depesz.com 。

执行上述查询大约需要10秒钟。我怎样才能使其更快？

Output of EXPLAIN ANALYZE on explain.depesz.com.
Executing the above query takes about 10 seconds. How can I make this faster?

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

The execution time is reduced half by using a subquery:

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

EXPLAIN ANALYZE 的输出http://explain.depesz.com/s/ibV rel = nofollow> explain.depesz.com 。

Output of EXPLAIN ANALYZE on explain.depesz.com.

推荐答案

您需要在 ORDER BY 中使用的列上创建索引。理想情况下，排序顺序是相同的，但是PostgreSQL可以以几乎相同的速度向后扫描索引。

You need to create an index on the column used in ORDER BY. Ideally in the same sort order, but PostgreSQL can scan indexes backwards at almost the same speed.

CREATE INDEX tbl_datecol_idx ON tbl (datecol DESC);

更多关于索引和 创建索引 。

使用 EXPLAIN ANALYZE 以获得除查询计划之外的实际时间。

More about indexes and CREATE INDEX in the current manual.
Test with EXPLAIN ANALYZE to get actual times in addition to the query plan.

当然，所有有关性能优化的常用建议也适用。

这篇关于快速在大表上使用LIMIT和OFFSET进行SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！