不可解なことを説明する。 パート4

PG Day'16カンファレンスは日々近づいてきており、 Hubert Lubaczewskiによる一連の記事の分析とその主要な操作についての公開を続けています。



これで、 シリーズの 最後から2番目の 投稿で、explain出力で見つけることができる残りの最も一般的な操作についてお話したいと思います。

ユニーク

操作の名前はそれ自身を表しています-重複データを削除します。



これは、たとえば次のことを行うときに発生する可能性があります。

select distinct field from table
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Postgresの最新バージョンでは、このリクエストはHashAggregateを使用して実装されます。



Uniqueの問題は、そのデータをソートする必要があることです。 この操作には特定の順序のデータが必要なためではなく、同じ値を持つすべての行が「一緒に」なるようにするためです。



これにより、Uniqueは実際にメモリを必要としないため、（使用できる場合に）本当にクールな操作になります。 前の行の値と現在の値を単純に比較し、同じ場合は破棄します。 以上です。



したがって、データを事前に並べ替えることで、その使用を促進できます。

 $ explain select distinct relkind from (select relkind from pg_class order by relkind) as x; QUERY PLAN ----------------------------------------------------------------------- Unique (cost=22.88..27.26 rows=4 width=1) -> Sort (cost=22.88..23.61 rows=292 width=1) Sort Key: pg_class.relkind -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1) (4 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

追記

このプランは、単純に多くのサブオペレーションを起動し、それらによって返されるすべての行を一般的な結果として返します。



これは、UNION / UNION ALLクエリで使用されます。

 $ explain select oid from pg_class union all select oid from pg_proc union all select oid from pg_database; QUERY PLAN ----------------------------------------------------------------- Append (cost=0.00..104.43 rows=2943 width=4) -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4) -> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4) -> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4) (4 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここでは、appendが3つのテーブルで3つのスキャンを実行し、すべての行をまとめて返す方法を示しています。



UNION ALLを使用したことに注意してください。 UNIONを使用すると、次の結果が得られます。

 $ explain select oid from pg_class union select oid from pg_proc union select oid from pg_database; QUERY PLAN ----------------------------------------------------------------------- HashAggregate (cost=141.22..170.65 rows=2943 width=4) -> Append (cost=0.00..133.86 rows=2943 width=4) -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4) -> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4) -> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4) (5 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは、UNIONが重複行を削除するためです。この場合、HashAggregate操作によって行われました。

結果

結果は主に非常に単純なテストクエリに表示されます。 この操作は、クエリがいくつかの定数値を選択するときに使用されます：

 $ explain select 1, 2; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリのテストに加えて、「挿入しますが、これがデータの複製ではない場合のみ」のようなことを行うクエリで見つけることができます。

 $ explain insert into t (i) select 1 where not exists (select * from t where i = 1); QUERY PLAN --------------------------------------------------------------------- Insert on t (cost=3.33..3.35 rows=1 width=4) -> Result (cost=3.33..3.34 rows=1 width=0) One-Time Filter: (NOT $0) InitPlan 1 (returns $0) -> Seq Scan on t t_1 (cost=0.00..40.00 rows=12 width=0) Filter: (i = 1) (6 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

値スキャン

結果と同様に、Values Scanはクエリに入力された単純なデータを返すために使用されますが、この場合はVALUES（）機能に基づいたレコードのセット全体である場合があります。



突然最新ではない場合は、次の例のように、VALUES構文を使用するだけで、テーブルなしで多くの行と列を選択できます。

 $ select * from ( values (1, 'hubert'), (2, 'depesz'), (3, 'lubaczewski') ) as t (a,b); a | b ---+------------- 1 | hubert 2 | depesz 3 | lubaczewski (3 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このような要求の計画は次のとおりです。

  QUERY PLAN -------------------------------------------------------------- Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (1 row)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この操作は通常INSERTで使用されますが、 カスタムソートなどの他の用途もあります。

GroupAggregate

この操作は、前に説明したHashAggregateに似ています。



違いは、GroupAggregateが機能するために、GROUP BY句で使用した1つまたは複数の列を使用してデータを並べ替える必要があることです。



Uniqueと同様に、GroupAggregateはメモリをほとんど使用しませんが、データの合理化が必要です。



例：

 $ explain select relkind, count(*) from (select relkind from pg_class order by relkind) x group by relkind; QUERY PLAN ----------------------------------------------------------------------- GroupAggregate (cost=22.88..28.03 rows=4 width=1) -> Sort (cost=22.88..23.61 rows=292 width=1) Sort Key: pg_class.relkind -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1) (4 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ハッシュセトップ

この操作は、INTERSECT / EXCEPT操作で使用されます（オプションの修飾子「ALL」を使用）。



次のように機能します。サブクエリのペアに対してサブオペレーションを追加し、結果とオプションのALL修飾子に基づいて、返す行を決定します。 私はソースコードに深く入りませんでしたので、それがどのように機能するかを正確に言うことはできませんが、名前に基づいて、操作はカウンターに基づく単純なソリューションに似ています。



UNIONとは異なり、これらの操作は2つのデータソースで機能することがわかります。

 $ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y; QUERY PLAN ------------------------------------------------------------------------------------------------------------- HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4) -> Append (cost=0.15..163.36 rows=2941 width=4) -> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4) -> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4) -> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4) -> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4) (6 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、3つのソースを使用して、より複雑なツリーを取得します。

 $ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y intersect all select * from (Select oid from pg_database order by oid) as w; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- HashSetOp Intersect All (cost=1.03..172.53 rows=2 width=4) -> Append (cost=1.03..171.79 rows=294 width=4) -> Subquery Scan on "*SELECT* 3" (cost=1.03..1.07 rows=2 width=4) -> Sort (cost=1.03..1.03 rows=2 width=4) Sort Key: pg_database.oid -> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4) -> Result (cost=0.15..170.72 rows=292 width=4) -> HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4) -> Append (cost=0.15..163.36 rows=2941 width=4) -> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4) -> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4) -> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4) -> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4) (13 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Cteスキャン

この操作は、 すでに説明したマテリアライズ操作に似ています。 要求の一部を起動し、その出力を保存して、要求の他の部分で使用できるようにします。



例：

 $ explain analyze with x as (select relname, relkind from pg_class) select relkind, count(*), (select count(*) from x) from x group by relkind; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- HashAggregate (cost=24.80..26.80 rows=200 width=1) (actual time=0.466..0.468 rows=6 loops=1) CTE x -> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65) (actual time=0.009..0.127 rows=295 loops=1) InitPlan 2 (returns $1) -> Aggregate (cost=6.57..6.58 rows=1 width=0) (actual time=0.085..0.085 rows=1 loops=1) -> CTE Scan on x x_1 (cost=0.00..5.84 rows=292 width=0) (actual time=0.000..0.055 rows=295 loops=1) -> CTE Scan on x (cost=0.00..5.84 rows=292 width=1) (actual time=0.012..0.277 rows=295 loops=1) Total runtime: 0.524 ms (8 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

pg_classは1回だけスキャンされることに注意してください-6行目。 ただし、その結果は「x」に保存され、2回スキャンされます-集計内（行番号9）とHashAggregate操作（10）内。



マテリアライズとの違いは何ですか？ この質問に対する詳細な回答を得るには、ソースコードを詳しく調べる必要がありますが、違いは、CTEがユーザー定義であるという単純な事実に基づいていると言えます。一方、Materializeは、Postgresが適切と判断したときに使用することを決定する補助操作です。 。



重要な注意：CTEは常に指示どおりに開始されます。 したがって、スケジューラーが実装できる最適な最適化ではなく、回避するために使用できます。

初期計画

この計画は、他のすべての前に計算できる（またはする必要がある）リクエストの一部があるたびに発生し、リクエストの残りの部分に依存しません。



このクエリが必要だとしましょう：

 $ explain select * from pg_class where relkind = (select relkind from pg_class order by random() limit 1); QUERY PLAN ------------------------------------------------------------------------------------------ Seq Scan on pg_class (cost=13.11..24.76 rows=73 width=203) Filter: (relkind = $0) InitPlan 1 (returns $0) -> Limit (cost=13.11..13.11 rows=1 width=1) -> Sort (cost=13.11..13.84 rows=292 width=1) Sort Key: (random()) -> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1) (7 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この場合、Postgresはrelkindの値とサブクエリによって返された値を比較する必要があるため、pg_classを使用して通常の順次スキャンの前にlimit / sort / seq-scanを実行する必要があります。



一方、次のように書くこともできます。

 $ explain select *, (select length('depesz')) from pg_class; QUERY PLAN ------------------------------------------------------------- Seq Scan on pg_class (cost=0.01..10.93 rows=292 width=203) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=0) (3 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Postgresは、副選択列がpg_classテーブルのデータに依存しないことを正しく認識しているため、一度実行すれば行ごとに長さを再計算する必要はありません。



もちろん、次のように、多くの単一の計画（初期計画）を持つことができます。

 $ explain select *, (select length('depesz')) from pg_class where relkind = (select relkind from pg_class order by random() limit 1); QUERY PLAN ------------------------------------------------------------------------------------------ Seq Scan on pg_class (cost=13.12..24.77 rows=73 width=203) Filter: (relkind = $1) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=0) InitPlan 2 (returns $1) -> Limit (cost=13.11..13.11 rows=1 width=1) -> Sort (cost=13.11..13.84 rows=292 width=1) Sort Key: (random()) -> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1) (9 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、1つの詳細を検討する価値があります。1つのリクエスト内の初期化プランには、運用ではなく「グローバル」に番号が付けられます。

サブプラン

SubPlanは、NestedLoopに多少似ています。 何度も呼び出せるという意味で。



SubPlanは、現在の行に実際に依存するサブクエリからデータを計算するために呼び出されます。



例：

 $ explain analyze select c.relname, c.relkind, (Select count(*) from pg_Class x where c.relkind = x.relkind) from pg_Class c; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Seq Scan on pg_class c (cost=0.00..3468.93 rows=292 width=65) (actual time=0.135..26.717 rows=295 loops=1) SubPlan 1 -> Aggregate (cost=11.83..11.84 rows=1 width=0) (actual time=0.090..0.090 rows=1 loops=295) -> Seq Scan on pg_class x (cost=0.00..11.65 rows=73 width=0) (actual time=0.010..0.081 rows=93 loops=295) Filter: (c.relkind = relkind) Rows Removed by Filter: 202 Total runtime: 26.783 ms (7 rows)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

「pg_class as c」をスキャンして返された各行に対して、PostgresはSubPlanを起動し、pg_classの行数がrelkind列の値（処理されたばかりの行）と同じかどうかを確認します。



「pg_class xでのSeqスキャン」行の「loops = 295」と、ノード「pg_class cでのSeqスキャン」の対応する値「rows = 295」に注意してください。

その他？

はい、他の操作があります。 それらのいくつかは私たちの注目に値するにはあまりにもまれです（特にあなたが素晴らしい知識のソースを持っていると考える場合-ソースコード）、そしていくつかは新しいノードの古いバージョンです（私は疑うように）。



私が説明しなかった操作の計画があり、理解できない場合は、説明にリンクを記述して、 explain.depesz.com 、操作の名前、およびあなたが会ったPostgresのバージョンを説明してください。 そのような場合に考えられるすべての情報を見つけて、詳細な回答をお届けします。