Postgresql 中文操作指南

7.6. LIMIT and OFFSET #

LIMIT 和 OFFSET 允许您仅检索查询其余部分产生的部分行：

LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query:

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

如果给出限制计数，将返回不超过该数量的行（但可能会更少，如果查询本身产生的行更少）。LIMIT ALL 与省略 LIMIT 子句相同，而 LIMIT 是 NULL 参数。

If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument.

OFFSET 表示在开始返回行之前跳过那么多行。OFFSET 0 与省略 OFFSET 子句相同，而 OFFSET 是 NULL 参数。

OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause, as is OFFSET with a NULL argument.

如果 OFFSET 和 LIMIT 都出现，那么在开始计算返回的 LIMIT 行之前，将跳过 OFFSET 行。

If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

使用 LIMIT 时，重要的是使用 ORDER BY 子句来将结果行约束到唯一顺序。否则，您将获得查询行的不可预测子集。您可能会要求第十到第二十行，但在什么顺序排列下是第十到第二十行？除非您指定 ORDER BY，否则排序未知。

When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query’s rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

查询优化器在生成查询计划时考虑了 LIMIT，因此根据您为 LIMIT 和 OFFSET 提供的内容，您很可能会获得不同的计划（产生不同的行顺序）。因此，使用不同的 LIMIT/OFFSET 值选择查询结果 will give inconsistent results 的不同子集，除非您使用 ORDER BY 强制可预测的结果排序。这不是一个 bug；这是以下事实的固有结果：除非使用 ORDER BY 约束顺序，否则 SQL 并不保证按任何特定顺序提供查询结果。

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

由 OFFSET 子句跳过的行仍然必须在服务器内计算；因此，一个大的 OFFSET 可能是低效的。

The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.