[解決済み] LIMIT/OFFSETを指定してクエリを実行し、総行数を取得することも可能

質問

ページネーション用に LIMIT と OFFSET 節があります。しかし、このクエリで返される行数のカウントも必要です。 LIMIT と OFFSET 節があります。

走りたい。

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

そして

SELECT COUNT(*) FROM table WHERE /* whatever */

同時にです。 特にPostgresに最適化させて、両方を個別に実行するよりも速くなるような方法はありますか？

どのように解決するのですか？

はい、そうです。 簡単なウィンドウ機能で

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

コストは総数なしの場合よりも大幅に高くなりますが、通常、2つの別々のクエリよりも安くなることに注意してください。Postgresは実際に すべての行を数える どちらの方法でも、対象となる行の総数に応じてコストが発生します。詳細はこちら

• LIMIT適用前の結果カウントを取得する最適な方法

しかし , ダニさんご指摘の通り は、いつ 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;

で1行のNULL値を取得します。 full_count が追加された場合 OFFSET が大きすぎる。そうでなければ、最初のクエリのように、すべての行に追加されます。

すべてのNULL値を持つ行が有効な結果となる可能性がある場合、以下のようにチェックする必要があります。 offset >= full_count を使用して、空の行の起源を曖昧にすることができます。

これでも、基本クエリは一度だけ実行されます。しかし、これはクエリに多くのオーバーヘッドを追加し、それがカウントのために基本クエリを繰り返すよりも少ない場合にのみ支払われるようにします。

最終的なソート順をサポートするインデックスが利用できる場合は ORDER BY をCTEで（冗長的に）使用します。