为了分页，我需要使用LIMIT和OFFSET子句运行查询。但是我还需要该查询在没有LIMIT和OFFSET子句的情况下返回的行数的计数。
我想跑步:
SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?
And:
SELECT COUNT(*) FROM table WHERE /* whatever */
同时。有没有办法做到这一点，特别是让Postgres优化它的方法，使它比单独运行两个更快?
当前回答
编辑:这个答案在检索未过滤的表时有效。我会让它来帮助别人，但它可能不能完全回答最初的问题。
如果你需要一个准确的值，Erwin Brandstetter的答案是完美的。然而，对于大型表，您通常只需要一个相当好的近似值。Postgres给你的就是这些，它会快得多，因为它不需要计算每一行:
SELECT *
FROM (
SELECT *
FROM tbl
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;
实际上，我不确定将RIGHT JOIN外部化或将其作为标准查询是否有优势。它值得一些测试。
SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
其他回答
No.
从理论上讲，如果单独运行它们，在引擎盖下有足够复杂的机器，可能会获得一些小收益。但是，如果你想知道有多少行符合一个条件，你必须计算它们，而不仅仅是一个LIMITed子集。
编辑:这个答案在检索未过滤的表时有效。我会让它来帮助别人，但它可能不能完全回答最初的问题。
如果你需要一个准确的值，Erwin Brandstetter的答案是完美的。然而，对于大型表，您通常只需要一个相当好的近似值。Postgres给你的就是这些，它会快得多，因为它不需要计算每一行:
SELECT *
FROM (
SELECT *
FROM tbl
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;
实际上，我不确定将RIGHT JOIN外部化或将其作为标准查询是否有优势。它值得一些测试。
SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?
虽然Erwin Brandstetter的答案很有魅力，但它返回了每行中的行数，如下所示:
col1 - col2 - col3 - total
--------------------------
aaaa - aaaa - aaaa - count
bbbb - bbbb - bbbb - count
cccc - cccc - cccc - count
你可以考虑使用只返回一次total count的方法，如下所示:
total - rows
------------
count - [{col1: 'aaaa'},{col2: 'aaaa'},{col3: 'aaaa'}
{col1: 'bbbb'},{col2: 'bbbb'},{col3: 'bbbb'}
{col1: 'cccc'},{col2: 'cccc'},{col3: 'cccc'}]
SQL查询:
SELECT
(SELECT COUNT(*)
FROM table
WHERE /* sth */
) as count,
(SELECT json_agg(t.*) FROM (
SELECT * FROM table
WHERE /* sth */
ORDER BY col1
OFFSET ?
LIMIT ?
) AS t) AS rows
是的。用一个简单的窗口函数:
SELECT *, count(*) OVER() AS full_count
FROM tbl
WHERE /* whatever */
ORDER BY col1
OFFSET ?
LIMIT ?注意，这样做的成本将大大高于没有总数的情况，但通常仍然比两个单独的查询便宜。Postgres实际上必须以任何一种方式计算所有行，这将根据符合条件的行总数施加成本。细节:
在应用LIMIT之前获得结果计数的最佳方法
但是，正如Dani指出的那样，当OFFSET至少与基本查询返回的行数相同时，将不会返回任何行。所以我们也没有得到full_count。
如果这是不可接受的，一个可能的解决方案，总是返回完整的计数将使用CTE和OUTER JOIN:
WITH cte AS (
SELECT *
FROM tbl
WHERE /* whatever */
)
SELECT *
FROM (
TABLE cte
ORDER BY col1
LIMIT ?
OFFSET ?
) sub
RIGHT JOIN (SELECT count(*) FROM cte) c(full_count) ON true;
如果OFFSET太大，就会得到一行NULL值，并附加full_count。否则，它像第一个查询一样被追加到每一行。
如果全为NULL值的行是一个可能的有效结果，则必须检查偏移量>= full_count以消除空行的原点的歧义。
这仍然只执行一次基本查询。但是它会给查询增加更多的开销，并且只有在开销小于为计数重复基本查询时才会付费。
如果支持最终排序顺序的索引可用，则可能需要在CTE中包含order BY(冗余)。
