ページ処理しながら全件数を一撃で得るSELECT文

はじめに

みなさん、ページ処理してますか？
アプリケーション仕様でよくある「総件数」「ページ数」、曲者ですよね？

ええ、そうです。
一言で表現されがちですが、絞り込み条件・並び替え等々データベースから部分データを取得するのにダイナミックに SELECT クエリーを組み立てる必要が出てくる部分です。

文字列連結多発地帯です。

そこに加えてページ絞り込み前の総件数（レコード数）のカウントを求められます。
そっくりなクエリーを何回も実行しやすい部分です。

文字列連結はやむなしとしても、クエリー回数を減らしてパフォーマンスを良くしたいところ。
PostgreSQL でクエリー1回で総件数とページ処理をした状態を得れる方法を紹介です。

例題

具体的な検索要件・状況を出してクエリーを書いてみます。

• メールアドレスが @example.com で終わるレコードで絞る。
• 宛名の昇順にした一覧とする。
• 3 ページ目を取得する。
• なお 10件/1ページ とし、絞り込み後の総件数も得ること。

ページ → 取得レコード範囲化

0 始まりとして3ページ目は「20～29」の範囲を得れば良いですね。
得られるレコード数は最大で10個、最小で0個が予想されます。

WITH を活用する

絞り込み前の状態を WITH 問い合わせにして共通テーブル式 (CTE:Common Table Expressions) 化して、後から範囲絞り・カウント用に利用します。

【日本語訳】7.8. WITH問い合わせ（共通テーブル式）

WITH cte AS (
    SELECT id, user_name, email
    FROM address_book
    WHERE email LIKE '[e-mail]'
    ORDER BY user_name ASC
)
SELECT *, (SELECT COUNT(*) FROM cte) AS total_count
FROM cte
OFFSET 20 LIMIT 10

並び替えも WITH の中で行います。

実行例

ここでは23件ヒットし、3ページ目が最後のページだったとしましょう。こんな感じでレコードを取得できます。

id	user_name	email	total_count
1001	くまだ	[e-mail]	23
1006	さわだ	[e-mail]	23
1002	はまだ	[e-mail]	23

total_count としてOFFSET/LIMITによる絞り込み前のレコード数が各レコードに追加されています。WITH 句内の結果がレコード絞り込み・カウントで共有されます。

1レコードごとにカウントされない？

(SELECT COUNT(*) FROM cte) AS total_count の部分が毎回カウントされそうに感じますが、実際のところは1回だけ計算され、それが再利用されるようです。

EXPLAIN を付けて実行計画を見る事で確認がとれます。

冗長じゃない？

はい。1byteでも通信量を削りたい党からすれば明らかに冗長ですが…。
通信量とドライバー処理などの＋αなオーバーヘッドより、重たいクエリー実行が1回で済むトータルの処理時間を短縮できるという割り切りにしています。

弱点と対策

指定するオフセット値が総レコード数 (total_count) 以降となる範囲外を指定すると、総レコード数が不明になります。返すレコード数が0になるためです。

対策として、範囲外となった時の挙動をアプリケーション仕様として事前じ決めておく必要があります。単に見つからないとするか、1ページ目に誘導する等のUI/UXが絡む部分になります。

おわりに

あるある実装だと SELECT count(*) AS total_count FROM address_bokk WHERE ～ のように条件絞りで総件数を求める＋OFFSET/LIMITのページ処理したレコードを取得する2ステップのパターンかなと思います。

実務で取り入れて、2ステップだった所が1ステップになって2倍近い速度改善につながりました。
ページ処理に悩まれてる方の参考になれば幸いです。
それではまた！