大表的高效分页

Question

使用PostgreSQL 10.5。我正在尝试创建一个分页系统，用户可以在各种结果之间来回切换。

为了避免使用OFFSET，我将上一页中最后一行的id作为参数p（prevId）传递。然后，我选择第一个id大于p参数值的三行数据。（如this article所述）

例如，如果上一页中最后一行的id是5，我会选择id大于5的前3行：

SELECT 
  id, 
  firstname, 
  lastname 
FROM 
  people 
WHERE 
  firstname = 'John'
  AND id > 5 
ORDER BY 
  ID ASC 
LIMIT 
  3;

这个很好用，而且时间也不算太糟糕。

Limit  (cost=0.00..3.37 rows=3 width=17) (actual time=0.046..0.117 rows=3 loops=1)
   ->  Seq Scan on people  (cost=0.00..4494.15 rows=4000 width=17) (actual time=0.044..0.114 rows=3 loops=1)
         Filter: ((id > 5) AND (firstname = 'John'::text))
         Rows Removed by Filter: 384
 Planning time: 0.148 ms
 Execution time: 0.147 ms

然而，如果用户想要返回到上一页，情况就有些不同：

首先，我会传递第一行的id，然后在其前面加上减号，以表示我应该选择id小于（一个正数）p参数的行。换句话说，如果第一行的id是6，那么p参数将为-6。类似地，我的查询将如下所示：

SELECT 
  * 
FROM 
  (
    SELECT 
      id, 
      firstname, 
      lastname 
    FROM 
      people 
    WHERE 
      firstname = 'John' 
      AND id < 6 
    ORDER BY 
      id DESC 
    LIMIT 
      3
  ) as d 
ORDER BY 
  id ASC;

在上面的查询中，我首先选择了最后3行，这些行的id小于6，然后将它们反转，以便以与开始时描述的第一个查询相同的方式呈现。 这样做是有效的，但由于数据库需要遍历几乎所有的行，性能受到了影响。

Sort  (cost=4252.75..4252.76 rows=1 width=17) (actual time=194.464..194.464 rows=0 loops=1)
   Sort Key: people.id
   Sort Method: quicksort  Memory: 25kB
   ->  Limit  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.460..194.460 rows=0 loops=1)
         ->  Sort  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.459..194.459 rows=0 loops=1)
               Sort Key: people.id DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Gather  (cost=1000.00..4252.72 rows=1 width=17) (actual time=194.448..212.010 rows=0 loops=1)
                     Workers Planned: 1
                     Workers Launched: 1
                     ->  Parallel Seq Scan on people  (cost=0.00..3252.62 rows=1 width=17) (actual time=18.132..18.132 rows=0 loops=2)
                           Filter: ((id < 13) AND (firstname = 'John'::text))
                           Rows Removed by Filter: 100505
Planning time: 0.116 ms
Execution time: 212.057 ms

尽管如此，我很感激您花时间阅读到这里，我的问题是：如何使分页更有效率？

- David

---

你的第一个查询不能得出你展示的第一个解释。请再仔细检查一下。 - jjanes

第二个例子与解释不符。 - Erwin Brandstetter

Answer 1

性能的关键是匹配的多列索引，其形式为：

CREATE UNIQUE INDEX ON people (firstname, id);

唯一，因为没有它，排序顺序可能会变得模糊不清。 唯一或主键 约束也同样适用。

虽然第一列的检查方式与您的示例相同（或按查询的相同方向进行排序），但此索引对于上下分页非常好，尽管对于上分页来说更好一些。

如果表中已经创建了索引（并在表上运行了ANALYZE），你将不再看到连续的扫描操作（除非你的表很小）。数据库不再“遍历几乎所有行”。

阅读您链接到的Markus Winand的演示文稿。

如果要跨多个firstname进行分页，请使用ROW值。下面是一个向下分页的示例：

SELECT *
FROM  (
   SELECT id, firstname, lastname 
   FROM   people
   WHERE  (firstname, id) < ('John', 6)  -- ROW values!
   ORDER  BY firstname DESC, id DESC 
   LIMIT  3
   ) d 
ORDER BY firstname, id;

相关：

• 优化大表上的OFFSET查询

如果 SELECT 列表只添加了像你的示例中的 lastname，你可以尝试将该列添加到索引中，以便从中获得 仅索引扫描 的效果：

CREATE UNIQUE INDEX ON people (firstname, id, lastname);

按照这个顺序索引表达式。

在Postgres 11中，考虑使用INCLUDE列，使索引稍微变小并且性能更好，在更多情况下适用。例如：

CREATE UNIQUE INDEX ON people (firstname, id) INCLUDE (lastname);

- Erwin Brandstetter