Clever Geek Handbook

PostgreSQLで見つかったレコードの数を数える

新しいプロジェクトでの作業では、PostgreSQL DBMSが使用されます。 私はこれまでMySQLを扱ってきたので、今度はPostgriを調べて発見する必要があります。 私が興味を持った最初の問題は、マッスルSQL_CALC_FOUND_ROWSの置き換えでした。 MySQLでこの定数を使用すると、「重い」クエリが使用されている場合、検索結果のページごとの表示に制限のあるクエリが不可欠であっても、クエリで見つかったすべてのレコードの数を取得できます。

すぐに解決策が見つかりませんでした。 フォーラムは、PostgriにはSQL_CALC_FOUND_ROWSがないと単純に述べています。 カウント（*）を使用する必要があると書いた人もいました。 そしてこれ以上の情報はありません。 しかし、MySQLからでさえ、count（）で検索することは知っていました-クエリは、SQL_CALC_FOUND_ROWSの場合よりもほぼ2倍遅くなります。 私はPostgreSQLを使用している人と1日グーグルを苦しめた人たちに相談した結果、PostgreSQLでSQL_CALC_FOUND_ROWSを置き換えるための4つのオプションがありました。



そのため、ここで説明する4つのオプションをすぐに紹介します。 ターゲットクエリは、 `text`フィールドにadfテキストを含むテーブルのレコードを探します。 180,000から始まる20レコードのIDと、見つかった合計数を選択します。

オプション1 phpPgAdminから取得。 PostgreSQL用のこのクライアントのコードを調べ、テーブルデータを表示するときにそれらがどのようにカウントされるかを調べました。 サブクエリでは2つのクエリが使用されます。 便利なのは、元のリクエストを解析して変更する必要がなく、それによって見つかったレコードの数をカウントすることです。 

 サブとして（ '％adf％'のようなテキストのテストからidを選択して）カウント（id）を選択します。
選択* from（ '％adf％'のようなテキストのテストからidを選択）副制限20オフセット180000


オプション2 最も簡単なオプション。通常は、MySQL、Postgres、およびその他のDBMSの両方の初心者が使用します。 2リクエスト。 

  '％adf％'のようなテキストのテストからカウント（id）を選択します。
 '％adf％'などのテキストが20オフセット180000を制限するテストからIDを選択します


オプション3 max_posedon 。 これは、Postgresの筋肉質のSQL_CALC_FOUND_ROWSをロジックによってエミュレートする試みです。 Trueは、IDでソートされている場合にのみ機能します（この場合）。 ここでは、選択の最後のレコードのIDが置き換えられます。 レコード番号180,000 + 20。 

  '％adf％'などのテキストが20オフセット180000を制限するテストからIDを選択します。
 '％adf％'およびid> 132629などのテキストのテストからカウント（id）を選択します。


オプション4 irc.freenode.orgのユーザーのアドバイス、再びmax_posedon 、およびGoogleの奥深くに隠されていたPostgreSQLフォーラムでのこの回答 。 カーソルが使用されます。 

  '％adf％'のようなテキストをテストすることから、選択IDのカーソルを宣言します。
 cursで180,000前進します。
 cursから20を取得します。
すべてをcursで前方に移動します。


+関数PQcmdTuples（）Postgres API（または$ count = pg_cmdtuples（$ result）; PHPの場合）。

4つのクエリオプションはすべて1つのトランザクションで実行する必要があるため、より高速に動作することに注意してください 。 1つのトランザクションを使用しない場合、4番目のオプションはまったく機能しません。カーソルが失われます。

さて、速度について 。 これら4つのオプションの速度をテストしました。 一般に、テストにより期待が裏付けられました。 しかし、重要な事実に注意します。 すべてのクエリはデフォルトのPostgreSQL構成で実行されましたが、パフォーマンスは最適化されていません。 最適化されたサーバーが手元にありませんでした。 そのため、「良い」構成を使用して、起動時に数値をわずかに調整できます。 しかし、本質は変わりません。

テストの実行は、各オプションに対して20回、PHPで2回、2回実行されました。 テストを実行するphpスクリプトが利用可能です。 気にする人は、Excel のサンプルの完全な統計があります。 ここでは、サマリーテーブルのみを公開します。

Var 1 Var 2 Var 3 Var 4
水 時間（ミリ秒） 647.41 648.25 450.64 370.67
Var 4に対する態度 1.75 1.75 1.22 -


比較のために、トランザクションを使用しないクエリ時間：



結果 。 カーソルを使用した最速のオプション4。 その速度は、「重い」検索クエリが1回だけ実行されるという事実によるものです。 次に、カーソルを使用した操作が実行されます。 MySQLのSQL_CALC_FOUND_ROWSも同様に機能します。 オプション3は20％遅れています-PostgreSQLでSQL_CALC_FOUND_ROWSの操作をエミュレートする試みです。 オプション1と2はほぼ同じ速度で動作し、75％高速（2/3以上！）カーソルを使用したクエリの速度に劣ります。

pgの第一人者のためのPS。 第一に、この情報があなたにとって明白であると思われるなら、私を信じてください-初心者にとって、PostgreSQLは全く明白ではなく、この情報を見つけることはそれほど簡単ではありません。 第二に、パフォーマンスまたはあなたの経験について調整されたpostgri構成のテスト、およびその他の計算オプションについてのコメントをお待ちしています。


More articles:


All Articles