🧒 👵🏼 💊 不可解な説明 ☄️ 🦋 😸

友人たち、PostgreSQLでの作業の最も多様な側面に関する興味深い資料を公開し続けています。 今日の翻訳では、 Hubert Lubaczewskiによる一連の記事が公開されています。

新しいデータベース管理者が最初に耳にすることの1つは、「EXPLAINを使用」です。そして、最初の試みで、彼は理解できないことに直面しています：

QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1) Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid)) Sort Method: quicksort Memory: 43kB -> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1) Hash Cond: (p.pronamespace = n.oid) -> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1) Filter: pg_function_is_visible(oid) -> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1) Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))

それはどういう意味ですか？

上記の説明をすぐに理解しようとするのは無益です。もっとシンプルなものから始めましょう。しかし、その前に、1つの重要なことを理解してほしい：

PostgreSQLは覚えています

これは、PostgreSQLがいくつかのメタ情報（情報に関する情報）を保存することを意味します。行数、異なる値の数、最も一般的な値など。大きなテーブルの場合、この情報はランダムサンプリングに基づいていますが、Postgres全体ではデータについて多くのことを本当に知っています。

それでは、簡単なクエリを見て説明しましょう。

 $ explain select * from test where i = 1; QUERY PLAN ------------------------------------------------------ Seq Scan on test (cost=0.00..40.00 rows=12 width=4) Filter: (i = 1) (2 rows)

リクエストは非常にシンプルで、私には、追加のコメントは必要ないと思われます。

Explainでは、最初の行と「->」で始まるすべての行が操作です。残りの行は、上記の操作に関する追加情報です。

私たちの場合、テストテーブルの順次スキャンという1つの操作しかありません。

フィルターに関する追加情報もあります。

順次スキャンとは、PostgreSQLがテーブルからデータを「開き」、読み取ることを意味します。理論的には、行をフィルタリング（削除）できますが、一般的には、テーブル全体を読み取って返す準備ができています。

なぜ準備ができているのですか？すぐに説明します。

したがって、Seqscan行は、シーケンシャルモードでテーブルをスキャンしていることを示しています。そして、このテーブルは「テスト」と呼ばれます（ただし、ここで最大の問題の1つは、回路が表示されないことであり、これが何度も注目されています）。

そして、手術後の括弧内のこれらの数字は何ですか？

質問したいです。次の表があります。

  Table "public.t" Column | Type | Modifiers -------------+---------+------------------------------------------------ id | integer | not null default nextval('t_id_seq'::regclass) some_column | integer | something | text | Indexes: "t_pkey" PRIMARY KEY, btree (id) "q" btree (some_column)

このテーブルとクエリの定義：

 SELECT * FROM t where some_column = 123;

このクエリを満たすための最良の方法は何だと思いますか？テーブルを順次スキャンするか、インデックスを使用しますか？

あなたの答え：もちろん、インデックスを使用し、この列にインデックスがあるので、このメソッドはより高速になります。次に、テーブルに行が1つしかなく、some_column値が123に等しい場合はどうでしょうか。

順次スキャンを実行するには、テーブルの1ページ（8192バイト）のみを読み取る必要があり、行を取得します。インデックスを使用するには、インデックスからページを読み取り、条件に一致する行がテーブルにあるかどうかを確認してから、テーブルからページを読み取る必要があります。

結果は2倍の仕事です！

「もちろんですが、私たちは非常に小さなテーブルについて話しているので、速度は重要ではありません。」いいね次に、100億行があり、各行にsome_column = 123があるテーブルを想像してみましょう。ここのインデックスは間違いなく役に立ちませんが、実際には状況を深刻に悪化させます。

もちろん、100万行あり、そのうちの1つだけがsome_column = 123である場合、インデックススキャンが最も正しいソリューションになります。

したがって、与えられたクエリがインデックスを使用するかどうか、そしてインデックスを使用する必要があるかどうかを言うことは不可能です（一般的なケースについて話している）。これを理解するには、さらに情報が必要です。そして、この事実は単純な結論につながります。状況に応じて、データを取得する1つの方法は別の方法よりも優れているか、悪いかです。

PostgreSQL（ある時点まで）は、考えられるすべてのシナリオをチェックします。彼は、あなたが持っている行数と、与えられた基準に該当する行数（ほとんどの場合）を知っているので、非常に賢明な決定を下すことができます。

しかし、これらの決定はどのように行われますか？これは、explainの最初の数字セットが示すものです。これはコストです。

一部の人々は、コストは数秒で推定されると考えています。そうではありません。測定単位は「1ページを順番に抽出する」です。つまり、時間とリソースの両方の使用が推定されます。

postgresql.confでは、次のパラメーターに気付くかもしれません。

 seq_page_cost = 1.0 # measured on an arbitrary scale random_page_cost = 4.0 # same scale as above cpu_tuple_cost = 0.01 # same scale as above cpu_index_tuple_cost = 0.005 # same scale as above cpu_operator_cost = 0.0025 # same scale as above

つまり、シーケンシャルページを読み取るコストを変更することさえできます。これらのパラメータは、同じクエリを実行するための異なるメソッドを実装するためにPostgreSQLが必要と想定するコストを設定します。

たとえば、テキストとインデックスを含む1000行の単純なテーブルを作成してみましょう。

 create table test (id serial primary key, some_text text); CREATE TABLE insert into test (some_text) select 'whatever' from generate_series(1,1000); INSERT 0 1000

idによる条件でExplainを実行すると、以下が生成されることがわかります。

 explain select * from test where id = 50; QUERY PLAN ----------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36) Index Cond: (id = 50) (2 rows)

しかし、どのような状況でもインデックススキャンを使用できないとpostgresに伝えた場合はどうでしょうか。

 explain select * from test where id = 50; QUERY PLAN ------------------------------------------------------------------------ Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13) Recheck Cond: (id = 50) -> Bitmap Index Scan on test_pkey (cost=0.00..4.28 rows=1 width=0) Index Cond: (id = 50) (4 rows)

そして、これもオフにしましょう：

 explain select * from test where id = 50; QUERY PLAN ------------------------------------------------------ Seq Scan on test (cost=0.00..18.50 rows=1 width=13) Filter: (id = 50) (2 rows)

OK、そしてそれらを隣同士に印刷しましょう：

 Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36) Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13) Seq Scan on test (cost=0.00..18.50 rows=1 width=13)

デフォルトでは、postgresはIndexScanを使用します。なんで？簡単です-この場合、最も安価な方法です。コストは8.29になりますが、ビットマップヒープスキャン（それが何であれ）には8.30がかかり、シーケンススキャンには18.5がかかります。

わかりましたが、オーバーヘッドは2つの数字で示されます：number..number。それは何ですか、そしてなぜ私は2番目の数字についてだけ話しているのですか？最初の数を考慮すると、勝者はseqスキャンになります。これは、この値がゼロに等しく、indexscanの場合は0.28であり、ビットマップヒープスキャンの場合は4.28であるためです。

コスト値は、範囲（数値..number）に表示されます。これは、操作の開始の行のコストと、すべての行を取得するためのコストを示しているためです（すべて、テーブル内のすべてではなく、この操作によって返されるものを意味します）。

初期費用はいくらですか？ seqscanには何もありません-ページを読んで行を返すだけです。それだけです。ただし、たとえば、データセットを並べ替えるには、すべてのデータを読み取って実際に並べ替えてから、最初の行を検討する必要があります。これは、以下の説明で明確に見られます。

  QUERY PLAN ------------------------------------------------------------------- Sort (cost=22.88..23.61 rows=292 width=202) Sort Key: relfilenode -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (3 rows)

ソートの初期コストは22.88ですが、合計コストは23.61に過ぎないことに注意してください。したがって、Sortから行を返すことはコストの点では重要ではありませんが、行を並べ替えることはできます。

Explainの次の情報は「行」です。これは、PostgreSQLが返すことができると考えるおおよその行数です（たとえば、LIMITがある場合、より少ない行を返すことができます）。これは、結合などの一部の操作でも非常に重要です（結合）合計20行の2つのテーブルの結合はさまざまな方法で実行できますが、一般的にはどちらを使用してもかまいませんが、100万行のテーブルと10億行のテーブルを組み合わせる場合は、行うことは非常に重要です（私は話している内部結合/左結合ではなく、ハッシュ結合、ネストされたループ、マージ結合に関するものです。その内容がわからない場合は心配しないで、後ですべて説明します）。

もちろん、この数はさまざまな理由で誤って推定される可能性があります。時々それは重要ではなく、時にはそれは重要です。しかし、誤った評価については後で説明します。

最後の情報は幅です。これは、この操作の一部として返される単一の行に含まれるバイト数の平均的なPostgreSQLの推定値です。例：

 explain select * from pg_class; QUERY PLAN ------------------------------------------------------------- Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (1 row) explain select relname, relkind from pg_class; QUERY PLAN ------------------------------------------------------------ Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65) (1 row)

ご覧のとおり、フィールド数の制限により幅が変更され、その結果、リクエストの実行を通過する必要があるデータの量が変更されました。

そして今、注意、最も重要な情報：説明は木です。最上位ノードには、その下のノードからのデータが必要です。

この計画を見てみましょう。

ソート、ハッシュ結合、seqスキャン、ハッシュ、そして再びseqスキャンの5つの操作があります。 PostgreSQLは最上位の操作を実行します-ソートは、その直下にある次の操作（ハッシュ結合）を実行し、そこからデータを受け取ります。ソートするデータを返すハッシュ結合では、seq scan（pg_procによる）とhash（＃4）を実行する必要があります。最後に、ハッシュは、データを返すために、pg_namespaceによってseq scanを実行する必要があります。

一部の操作では、データを即座に、またはさらに重要なこととして徐々に返すことができることを理解することが非常に重要です。たとえば、Seq Scan。そして、いくつかはできません。たとえば、ここでは、ハッシュ（＃4）の初期オーバーヘッドが「すべての行」の「サブオペレーション」シーケンススキャンと同じであることがわかります。つまり、ハッシュ操作を開始するには（少なくとも1行返すことができるように）、そのサブ操作からすべての行を読み取る必要があります。

緩やかな改行部分は、関数の作成を開始するときに特に重要になります。この関数を見てみましょう：

 CREATE OR REPLACE FUNCTION public.test() RETURNS SETOF integer LANGUAGE plpgsql AS $function$ declare i int4; begin for i in 1..3 loop return next i; perform pg_sleep(1); end loop; return; end; $function$;

何もわからなくても心配しないでください。この関数は3行を返します。各行には1、2、3の整数が含まれます。重要なことは、各行が戻ってから1秒間スリープ状態になることです。

これは、私がこれを好きなら：

 select * from test();

結果が出るまで3秒待たなければなりません。

しかし、この状況で復帰を待つのにどれくらい時間がかかりますか：

 select * from test() limit 1;

見てみましょう：

 \timing Timing is on. select * from test() limit 1; test ------ 1 (1 row) Time: 3005.334 ms

同じ3秒。なんで？ PL / pgSQL（およびすべてではないにしてもほとんどのPL / *言語）は部分的な結果を返すことができないためです。「次へ戻る」の助けを借りて-彼らはできるようですが、実際にはすべての結果はバッファに保存され、関数の実行が終了したときに一緒に返されます。

一方、「通常の」操作では通常、部分的なデータを返すことができます。これは、難しいテーブルでシーケンシャルスキャンなどの簡単な操作を実行した場合に見られます。

 create table t as select i as id, repeat('depesz', 100)::text as payload from generate_series(1,1000000) i;

この表はそれを示しています：

 explain analyze select * from t; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.015..232.380 rows=1000000 loops=1) Total runtime: 269.666 ms (2 rows) explain analyze select * from t limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.02 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=1) -> Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.003..0.003 rows=1 loops=1) Total runtime: 0.016 ms (3 rows)

（これまでは「合計ランタイム：..」のみをご覧ください）

ご覧のとおり、シーケンシャルスキャンは非常に迅速に終了しました-LIMIT食欲がちょうど1行で満たされるとすぐに。

ここで、オーバーヘッド（クエリを比較するための最良の基準ではない）でさえ、最上位ノード（最初のクエリでのseqスキャンと2番目の制限）がすべての行を返すための値が非常に異なることを示していることに注意してください-185834.82対0.02

したがって、任意の操作の最初の4つの数値（2つのコスト見積もり、行数と幅）は近似値です。それらは正しい場合とそうでない場合があります。

「EXPLAIN ANALYZEクエリ」または「EXPLAIN（ANALYZE on）クエリ」を実行すると得られる他の4つの数値は、実際の結果を示しています。

時間はまだ範囲で示されますが、今回はリアルタイムです。これは、PostgreSQLがこの操作の処理に費やした時間です（同じ操作を何度も実行する可能性があるため）。コストと同様に、時間は範囲で表されます。操作を開始する時間とすべてのデータを返す時間です。この計画を確認しましょう：

 $ explain analyze select * from t limit 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..9.33 rows=100 width=608) (actual time=0.008..0.152 rows=100 loops=1) -> Seq Scan on t (cost=0.00..93333.86 rows=999986 width=608) (actual time=0.007..0.133 rows=100 loops=1) Total runtime: 0.181 ms (3 rows)

ご覧のとおり、Limitの操作開始時間は0.008です（ここでの測定単位はミリ秒です）。これは、Seqスキャン（データを取得するためにLimitが呼び出す）が最初の行を返すのに0.007msかかり、その後、さらに0.001msが制限内で処理を開始したために発生します。

さらに（最初の行が戻った後）、100行を受信するまで、制限はSeqスキャンからデータを受信し続けました。その後、彼はシーケンシャルスキャンを停止し（これはリクエストの開始から0.133ms後に起こりました）、さらに0.019ms後に終了しました。

名前が示すように、実際の行数は、この操作で返された（平均）行数を示します。ループは、この操作が実行された回数を示します。

どの場合、操作は複数回呼び出されますか？たとえば、場合によっては、結合クエリまたはネストされたクエリを使用します。この計画のようになります。

3番目の操作では2サイクルしかないことに注意してください。つまり、このseqスキャンは2回実行され、平均で1行が返され、平均で0.160msが必要でした。したがって、この特定の操作に費やされた合計時間：2 * 0.160ms = 0.32ms（explain.depesz.comの排他的/包括的列に示されているように）。

非常に多くの場合、クエリのパフォーマンスが低いのは、何らかのサブクエリを何度も繰り返す必要があるためです。たとえば、次のようになります。

（もちろん、これは、そのような計画を選んだpostgresがすべてを責めることを意味するものではありません。おそらく、他に選択肢がなかったか、さらにコストが高いことが判明したのでしょう）。

上記の例では、操作3の実際の時間はわずか0.003msですが、この操作は26,000回以上実行され、最終的にほぼ79msの時間が費やされるという事実に注意する価値があります。

これは、explain'ovを読むのに必要な理論的な情報だと思います。ほとんどの場合、操作やその他の情報の意味はまだわかりませんが、少なくとも数字の意味と説明の違いはわかります（ランダムな概算に基づいて抽象的な測定単位でコストが表示されます）（例）および分析の説明（実際の時間、行数、実行時間を測定単位で表示し、異なるクエリを比較できるようにします）。

いつものように、重要なことをたくさん逃したのではないかと心配していますが、目を引くことはできませんでした。または（さらに悪いことに）それらを「自明」だと考えました。何か不足していると思われる場合はお知らせください。すみやかにギャップを埋めようとします。

ただし、このテキストをさらに2〜3冊の出版物で開発する予定であり、さらに詳しく説明します。

操作が何であるか、どのように機能するか、Explain出力でそれらを見るときに何を期待するか
統計とは、Pgがそれらを取得する方法、それらを表示する方法、およびそれらを最大限に活用する方法です。

この記事がお役に立てば幸いです。 購読し、出版物に従ってください。 すぐにブログで次のエピソードの翻訳があります！ いつものように、フィードバックや提案をお気軽にお寄せください。 最も興味深いのは、次のPG Day'16ロシアのプログラムに含まれます！ :)

不可解な説明

More articles: