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

質問

DBから送られてくるデータをページングする場合、ページジャンプコントロールをレンダリングするために何ページあるのかを知る必要があります。

現在、私はクエリを2回実行し、1回は count() でラップして全体の結果を決定し、2回目は制限を適用して現在のページに必要な結果だけを取得します。

これは非効率的に思えます。の前に返されたであろう結果の数を決定する、より良い方法はないでしょうか？ LIMIT が適用される前に返されたであろう結果の数を決定する良い方法はありますか？

PHPとPostgresを使用しています。

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

純粋なSQL

2008年から変わりました。あなたは ウィンドウ機能 を使用して、完全なカウントを取得することができます。 と を取得することができます。で導入された 2009年のPostgreSQL 8.4で導入されました。 .

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

なお、この は、総数を指定しない場合よりもかなり高価になる可能性があります。 . 全ての行を数える必要があり、一致するインデックスから上位の行だけを取るというショートカットが可能ですが、これはもう役に立ちません。

小さなテーブルや full_count とか。 OFFSET + LIMIT . 実質的に大きな full_count .

コーナーケース : 次のような場合 OFFSET が少なくともベースクエリからの行の数と同じであるとき。 行なし が返されます。そのため full_count . 可能な代替案

• LIMIT/OFFSETでクエリを実行し、総行数を取得する。

のイベントのシーケンス SELECT クエリ

( 0. CTEは別々に評価され、マテリアライズされます。Postgres 12以降では、プランナは作業に入る前にそれらをサブクエリのようにインライン化することができます)。ここではありません。

1. WHERE 節（および JOIN 条件、ただしあなたの例ではなし) は、ベース・テーブルから修飾された行をフィルタリングします。 残りはフィルタリングされたサブセットに基づいています。

( 2. GROUP BY と集計関数がここに入るでしょう) ここはダメです。

( 3.その他 SELECT リスト式は、グループ化/集約されたカラムに基づいて、評価されます)。ここではありません。

1. に応じてウィンドウの機能が適用されます。 OVER 節と関数のフレーム指定によって適用されます。単純な count(*) OVER() は、すべての修飾された行に基づいています。

2. ORDER BY

( 6. DISTINCT または DISTINCT ON はここに入るだろう) ここはダメです。

1. LIMIT / OFFSET は、返すべき行を選択するために確立された順序に基づいて適用されます。

LIMIT OFFSET は、テーブルの行数が増えるにつれてますます非効率的になります。より良いパフォーマンスが必要な場合は、別のアプローチを検討してください。

• ラージテーブルでOFFSETを使用したクエリの最適化

最終的なカウントを取得するための代替手段

影響を受ける行の数を得るには、まったく異なるアプローチがあります ( ではない 前のフルカウント OFFSET &です。 LIMIT が適用されました)。Postgresは、最後のSQLコマンドによって影響を受けた行の数を内部的に記録しています。いくつかのクライアントはその情報にアクセスしたり、（psqlのように）自分自身で行を数えたりすることができます。

例えば、影響を受けた行の数を取得するためには plpgsql でSQLコマンドを実行した直後に取得できます。

GET DIAGNOSTICS integer_var = ROW_COUNT;

詳細はマニュアルで。

または pg_num_rows で PHP . または他のクライアントの同様の機能。

関連する

• PostgreSQLのバッチクエリによって影響を受ける行数を計算する。