総件数を同時に取得する

画面に表示すればいい件数は100件だが総件数も同時に表示なければいけない場合、COUNTを別途投げていてはパフォーマンスに悪影響が出る。データセットを取得するSQLの中で同時に総件数も取得するにはCOUNT(1) OVER()を利用する。

SELECT
    COUNT(1) OVER()
    , table_a.*
FROM table_a
LIMIT 100 OFFSET 0

ページング可能な最大件数を同時に取得する

もし総件数を表示するのでなく、ページング可能な最大件数を表示すればいいという要件の場合、さらに効率のいいSQLが書ける。

例えば、ある条件に合致するデータが50000件あり、ユーザが検索できるのはそのうち10000件とする。一画面に表示するのは100件で、LIMITとOFFSETを使ってページングを行う。

WITH core AS (
  SELECT * FROM main
  WHERE /*conditions*/
  ORDER BY /*order*/
  LIMIT 10000
)

SELECT
  COUNT(1) OVER()
  , core.*
FROM core
LEFT OUTER JOIN マスタ系テーブル ON ...
LIMIT 100 OFFSET 0

WHERE句での検査が最大10000件を満たすと止まってくれる。

WITH句のSQLでは取得件数に影響を及ぼさない結合等は行わず、最後のSQLで必要な結合を行う必要がある。
最後のSQLは100件に絞っているため、マスタ等を結合するとき、10000件分結合するのではなく100件分だけ結合すればよくなる。

ちなみに最後のCOUNT(1) OVER()を(SELECT COUNT(1) FROM core)としても10000件に絞っているのでパフォーマンス上ほとんど影響は出ない。