在交互式应用程序中处理大结果集时，您希望对结果集进行分页，即逐页显示。从第一次网络搜索开始，每个人都熟悉这一点。您还会得到一个滚动到下一页的按钮，并且您会得到一个总结果数。本文展示了结果集分页的各种选项及其性能。它还讨论了总结果计数的问题。
演示分页技术的示例
让我们创建一个包含足够时间序列数据的表：
CREATE UNLOGGED TABLE data (
id bigint GENERATED ALWAYS AS IDENTITY,
value double precision NOT NULL,
created timestamp with time zone NOT NULL
);
/* make the example repeatable */
SELECT setseed(0.2740184);
INSERT INTO data (value, created)
SELECT random() * 1000, d
FROM generate_series(
TIMESTAMP '2022-01-01 00:00:00 UTC',
TIMESTAMP '2022-12-31 00:00:00 UTC',
INTERVAL '1 second'
) AS d(d);
/* add a primary key */
ALTER TABLE data ADD PRIMARY KEY (id);
/* set hint bits, create visibility map, gather statistics */
VACUUM (ANALYZE) data;
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;
CREATE INDEX data_created_value_idx ON data
(created, value);
简单分页：OFFSET和LIMIT
/* the first page */
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
LIMIT 50;
/* page number 100 */
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
OFFSET 4950 LIMIT 50;
当您获取后面的页面时，性能会变得更差。要获得页码 100，PostgreSQL 必须计算结果集的前 5000 行，只丢弃前 4950 行。因此，当您翻阅结果集时，处理速度会变慢，我们会做很多不必要的工作。如果你知道用户只会看前几个结果，你就可以避开这个问题。
并发数据修改可能会导致问题。如果有人在我们查看第一页时向表中插入一个新行，那么该行可能会出现在第一页上。现在如果我们获取第二页，整个结果集将移动一个位置，第二页将从第一页的最后一行开始。更糟糕的是，如果并发语句删除一行，当我们移动到第二页时，可能会跳过结果集中的一行。
OFFSET和LIMIT的优势
WITH HOLD使用游标分页WITH HOLD使用游标分页
但是你可以使用WITH HOLD游标。这样的游标在事务结束时获取完整的结果集并将其具体化在服务器上：
START TRANSACTION;
/*
* Don't use SCROLL unless you need to scroll backwards,
* for example to get the total count (see below).
*/
DECLARE c SCROLL CURSOR WITH HOLD FOR
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;
/* this will calculate the whole result set */
COMMIT;
现在获取任意页面很简单：
/* get page number 100 */
MOVE ABSOLUTE 4950 IN c;
FETCH 50 FROM c;
完成后不要忘记关闭光标：
CLOSE c;
WITH HOLD游标的优点
WITH HOLD游标的缺点
完成后一定不要忘记关闭游标，否则结果集将保留在服务器上，直到数据库会话结束
事务级连接池不适用于WITH HOLD游标，因为它们绑定到数据库连接
整个结果集是在初始事务结束时计算的，因此获取第一页可能会很慢（但后续页面会很快）
如果光标保持打开的时间较长，结果将变得“陈旧”
键集分页
这是对结果集进行分页的最先进的方法。它也要求我们获取该id列。第一页是通过以下方式获取的：
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created, id
LIMIT 50;
我们必须记住页面最后id一行created的值。然后我们可以获取下一页
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
AND (created, id) > ('2022-01-01 01:27:35+01', 5256)
ORDER BY created, id
LIMIT 50;
我们从上一页中记住的值是2022-01-01 01:27:35+01和5256。我们需要id作为分界符，以防有多行具有相同的created值。
为了使键集分页高效，我们需要创建一个支持第二个WHERE条件的特殊索引：
CREATE INDEX data_keyset_idx ON data
(created, id, value);
如果value不包含在索引中，键集分页就足够快了，但我添加它以获得index-only扫描，因此与其他方法的性能比较是公平的。
键集分页的优点
键集分页的缺点
需要专门为查询设计的特殊索引
只有事先知道确切的查询才有用
比较分页方法的性能
以下结果是在开始时针对示例测得的。我通过本地连接测量了客户端的响应时间（五次运行的平均值）。此外，我还用于EXPLAIN
(ANALYZE, BUFFERS)确定查询必须处理多少个缓冲区。所有数据都缓存在共享缓冲区中，“ random_page_cost”设置为
1.1。
第一页的分页OFFSET和LIMIT获胜（可能是因为它少获取一列），但是如果您必须获取更多页面，则键集分页会更好。光标分页只能在您COMMIT异步进行时竞争，而用户正在观看第一页。但是，对于后续页面，它比其他方法更快，因为结果集已经计算好了。
结果总数有什么问题？
到目前为止，我们已经看到有一些聪明的方法可以对查询结果进行分页。但是，没有聪明的方法来获得总结果数。您必须计算完整的结果集，然后对其进行计数。其影响因所选的分页方法而异：
使用OFFSET和键集分页，需要额外的昂贵查询。如果仅检索前几页，则计算结果集总数的查询通常比所有其他查询加在一起的开销更大。
使用WITH HOLD游标分页，整个结果集都是在开始时计算的，因此结果集计数是免费的：
/* the result will be "MOVE 314711" */
MOVE ALL IN c;
不管你怎么做，计算整个结果集都很慢。我见过许多情况，其中count(*)计算总结果集计数的查询是应用程序工作负载中最昂贵的查询。因此，我的建议是：
最简单的解决方案就是不显示结果集计数。把事情简单化！
下一个最佳解决方案是显示近似的结果集计数。如果您运行“ EXPLAIN (FORMAT JSON) SELECT 1 FROM / your query /”而不是“ SELECT count() FROM / your query */”，这很容易。EXPLAIN然后您可以从输出中提取行计数估计值。有关详细信息，请参阅我关于行计数的文章。请记住，Google 也不会显示确切的计数！
如果您确实需要准确的结果集计数，请不要立即显示它。相反，将其设置为最初禁用的选项，例如按钮“获取精确计数”。然后用户只需在他或她认为有必要时付款。
无论做什么，只在显示第一页时才计算或估计计数。不要陷入为用户显示的每个结果集页面重复查询的陷阱。
结论
对结果进行分页时，您可以在OFFSET/ LIMIT、WITH HOLD游标和键集分页之间进行选择。这些方法中的每一种都有其优点和缺点，您必须决定哪种方法最适合您的情况。考虑到显示总结果集数是非常昂贵的，所以尽可能避免它。
点击此处阅读原文
↓↓↓
