PostgreSQLの説明-クエリコストとは

Postgres説明コストを理解する

EXPLAIN Postgresクエリのパフォーマンスを理解するのにとても便利です.指定したステートメントのPostgreSQLクエリプランナによって生成された実行計画を返します.The EXPLAIN コマンドは、インデックススキャンまたはシーケンシャルスキャンを使用してステートメントで参照されるテーブルを検索するかどうかを指定します.
いくつかの場合は、出力の見直しを通知する最初のものEXPLAIN コマンドはコスト統計であるので、彼らが意味するもの、彼らがどのように計算されるか、そして、彼らがどのように使用されるかについて疑問に思うのは当然です.
要するに、PostgreSQLの問い合わせプランナーは、クエリがどのくらいの時間(任意の単位で)、起動コストと各オペレーションの総コストの両方を推定することです.後でそれ以上.それはクエリを実行するための複数のオプションを持っている場合、それは、これらのコストを使用して、最も安価な、それゆえ、最速、オプションを選択します.

どのような単位でのコストですか？

コストは任意の単位です.一般的な誤解は、彼らが時間のミリ秒か他の単位であるということです、しかし、それはケースでありません.
コスト単位は、1つのシーケンシャルページに固定されます.seq_page_cost ). 処理された各行は0.01 (cpu_tuple_cost ), そして、各非連続ページ読み込みは4.0を加えますrandom_page_cost ). このような多くの定数は、すべての設定可能です.その最後のものは、少なくとも現代のハードウェアで、特に一般的な候補です.もう少し詳しく調べましょう.

スタートアップコスト

あなたが後に見る最初の数cost= 「スタートアップコスト」として知られています.これは、最初の行を取得するにはどのくらいかかるの見積もりです.このように、操作のスタートアップコストはその子供のコストを含む.
シーケンシャルスキャンの場合、スタートアップコストは一般的にゼロに近くなります.並べ替え操作では、行が返される前に大きな仕事の割合が必要になるので、それはより高いでしょう.
例を見るには、1000個のユーザ名を持つ簡単なテストテーブルを作成しましょう.

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username text NOT NULL);
INSERT INTO users (username)
SELECT 'person' || n
FROM generate_series(1, 1000) AS n;
ANALYZE users;

簡単なクエリプランを見てみましょう.

EXPLAIN SELECT * FROM users ORDER BY username;

QUERY PLAN                                                    |
--------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17)                  |
  Sort Key: username                                          |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

上記の問い合わせ計画では、予想通り、見積もり計算実行コストSeq Scan is 0.00 , とSort is 66.83 .

総費用

第2のコスト統計は、スタートアップコストと2つの点の後、「トータルコスト」として知られています.これは、すべての行を返すのにどれくらいかかるかの見積もりです.
もう一度その例の問い合わせ計画を見てみましょう.

QUERY PLAN                                                    |
--------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17)                  |
  Sort Key: username                                          |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

我々は、全体のコストはSeq Scan 操作は17.00 . のためにSort オペレーションは69.33であり、起動コスト(予想通り)ではない.
総費用は通常、それらの前のオペレーションのコストを含みます.例えば、ソート演算の総コストはSEQスキャンの合計コストである.
重要な例外はLIMIT これは、プランナーが初期のアボートを行うかどうかを推定するための節です.それが一般的である行の少ない数を必要とするだけであるならば、より単純な走査選択がより安い(より速い可能性がある)ことを計算するかもしれません.
例えば、

EXPLAIN SELECT * FROM users LIMIT 1;

QUERY PLAN                                                    |
--------------------------------------------------------------+
Limit  (cost=0.00..0.02 rows=1 width=17)                      |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17)|

ご覧のようにSEQスキャンノードで報告された総コストは17.00ですが、リミット動作の完全なコストは0.02であると報告されています.これは、プランナーが1000行から1行を処理しなければならないと予想しているため、この場合のコストは、合計で1000分の1と見積もられる.

費用の計算方法

これらのコストを計算するために、Postgresクエリプランナーは、データベースの内容についての定数(既に見ているもの)とメタデータを使用します.メタデータはしばしば「統計」と呼ばれる.
統計は ANALYZE …と混同しないEXPLAIN と同じ pg_statistic . また、一部のように自動的に更新されますautovacuum .
これらの統計には、多くの非常に有用なものが含まれています.これは、それぞれのテーブルが持っている行の数や、それぞれの列の中で最も一般的な値がどのようになっているかなどです.
単純な例を見てみましょう.

EXPLAIN SELECT count(*) FROM users;

QUERY PLAN                                                   |
-------------------------------------------------------------+
Aggregate  (cost=19.50..19.51 rows=1 width=8)                |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=0)|

私たちのケースでは、プランナーの統計は、テーブルのデータを7ページ(またはブロック)内に格納され、1000行が返されることを示唆した.コストパラメータseq_page_cost , cpu_tuple_cost , and cpu_operator_cost のデフォルトは1 , 0.01 , and 0.0025 それぞれ.
このようにSEQスキャントータルコストは以下のように計算された.

Total cost of Seq Scan
= (estimated sequential page reads * seq_page_cost) + (estimated rows returned * cpu_tuple_cost)
= (7 * 1) + (1000 * 0.01)
= 7 + 10.00
= 17.00

として集計します.

Total cost of Aggregate
= (cost of Seq Scan) + (estimated rows processed * cpu_operator_cost) + (estimated rows returned * cpu_tuple_cost)
= (17.00) + (1000 * 0.0025) + (1 * 0.01) 
= 17.00 + 2.50 + 0.01
= 19.51 

プランナーはコストをどのように使うか

Postgresは、最低総コストでクエリープランを選択することを知っているので、その選択を理解しようとすることができます.たとえば、クエリがインデックスを使用していない場合は、次のような設定を使用できます enable_seqscan 特定のクエリプランの選択を大規模に阻止します.この時点では、コストを増やすことによって、この作品のような設定を聞いて驚いてはいけない!
行番号はコスト見積りの非常に重要な部分です.それらは異なった結合順序、結合アルゴリズム、走査型、およびより多くのための見積もりを計算するために使用されます.ロットコスト見積もりは、多くの費用見積もりにつながることができますが、最終的には、準最適な計画の選択になる可能性があります.

クエリプランを使用してクエリプランを取得する

PostgreSQLでSQL文を書くときはANALYZE コマンドは、クエリを最適化するキーです.クエリプランとPostgreSQLの見積もりを表示するに加えてEXPLAIN ANALYZE オプションはクエリを実行するUPDATE and DELETE !), 実行プロセスの各ステップの実際の実行時間と行カウント数を示します.これはSQLパフォーマンスの監視に必要です.
使えますEXPLAIN ANALYZE それぞれの操作によって返される実際の行で、推定された行数を比較する.
例を見てみましょう.

QUERY PLAN                                                                                                 |
-----------------------------------------------------------------------------------------------------------+
Sort  (cost=66.83..69.33 rows=1000 width=17) (actual time=20.569..20.684 rows=1000 loops=1)                |
  Sort Key: username                                                                                       |
  Sort Method: quicksort  Memory: 102kB                                                                    |
  ->  Seq Scan on users  (cost=0.00..17.00 rows=1000 width=17) (actual time=0.048..0.596 rows=1000 loops=1)|
Planning Time: 0.171 ms                                                                                    |
Execution Time: 20.793 ms                                                                                  |

全体の実行コストは69.33であり、その大半はソート演算であり、17.00はシーケンシャルスキャンから来る.クエリ実行時間は21 ms以下であることに注意してください.

シーケンシャルスキャン対インデックススキャン

では、テーブル全体の高価なソートを避けるためにインデックスを追加しましょう.

​​CREATE INDEX people_username_idx ON users (username);

EXPLAIN ANALYZE SELECT * FROM users ORDER BY username;

QUERY PLAN                                                                                                                       |
---------------------------------------------------------------------------------------------------------------------------------+
Index Scan using people_username_idx on users  (cost=0.28..28.27 rows=1000 width=17) (actual time=0.052..1.494 rows=1000 loops=1)|
Planning Time: 0.186 ms                                                                                                          |
Execution Time: 1.686 ms                                                                                                         |

あなたが見ることができるように、その計画の総コストは28.27(69.33よりも低い)ので、クエリプランナーは今、インデックススキャンを選択しています.クエリ実行時間がちょうど2 ms未満であるので、インデックススキャンがシーケンシャルスキャンより効率的であるように見えます.

プランナーの見積もりをより正確に支援する

プランナーの見積もりは、2つの方法でより正確に見積もることができます.

より良い統計情報を集める

チューン定数は、計算のために使用する

統計は、テーブル内のデータに大きな変更の後、特に悪いことができます.このように、多くのデータをテーブルにロードするとき、マニュアルを実行することによってPostgresを助けることができますANALYZE その上に統計も主要なバージョンアップグレードの上で持続しないので、それはこれをするもう一つの重要な時間です.
当然、テーブルも時間とともに変化するtuning the autovacuum settings それが頻繁にあなたのワークロードのために十分に実行することを確認するには非常に役立つことができます.
あなたが歪んだ分布でコラムのために悪い見積もりで苦労しているならば、あなたは ALTER TABLE SET STATISTICS コマンド、あるいは default_statistics_target を返します.
悪い見積もりのもう一つの一般的な原因は、デフォルトで、Postgresは2つのコラムが独立していると仮定するということです.これを修正するには、同じテーブルから2つの列に対して相互関係データを収集しますextended statistics .
定数調整フロントでは、あなたのハードウェアに合わせて調整できるパラメータがたくさんあります.SSDで動作していると仮定すると、最低でもrandom_page_cost . これは4よりも4 x高価ですseq_page_cost 私たちはもっと早く見た.この比率はディスクを回転させることに意味を持ちましたが、SSDSではランダムI/Oをあまりにも優先しがちです.このような設定が1に近いとして、または1と2の間で、より意味があるかもしれません.ScaleGridではデフォルト値は1です.

クエリ計画からコストを削除できますか？

上記の理由の多くのために、大部分の人々は、走るとき、コストを残しますEXPLAIN . しかし、あなたが望む必要があります、あなたはCOSTS パラメータ

EXPLAIN (COSTS OFF) SELECT * FROM users LIMIT 1;

QUERY PLAN             |
-----------------------+
Limit                  |
  ->  Seq Scan on users|

結論

re - capには、クエリ計画におけるコストは、PostgreresのSQLクエリがどれくらいの長さになるか見積もられます.
これは、いくつかの設定可能な定数といくつかの統計情報に基づいて、全体的なコストが低いとの計画を選ぶ.
それがより正確にこれらのコストを見積もるのを助けることは、それが良い選択をするのを援助するのに非常に重要です、そして、あなたの質問を実行してください.