PolarDB:明示的なJOIN句でプランナーを制御する

明示的なJOIN構文を使用することで、クエリプランナーをある程度制御することができます。 これが重要な理由を確認するには、まず背景が必要です。

次のような単純な結合クエリでは、

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

プランナーは任意の順序で指定されたテーブルに自由に参加できます。 たとえば、WHERE条件a.id=B. idを使用してAをbに結合し、他のWHERE条件を使用してCをこの結合テーブルに結合するクエリプランを生成できます。 または、BをCに結合し、次にAをその結果に結合することができます。 または、AをCに結合してからBと結合することもできますが、AとCの完全なデカルト積を形成する必要があるため、非効率的です。結合の最適化を可能にするWHERE句に適用可能な条件はありません。。 (PostgreSQLexecutorのすべての結合は2つの入力テーブル間で行われるため、これらの方法のいずれかで結果を構築する必要があります。) 重要な点は、これらの異なる結合の可能性は意味的に同等の結果を与えるが、実行コストが大きく異なる可能性があるということです。 したがって、プランナーはそれらすべてを探索して、最も効率的なクエリプランを見つけようとします。

クエリに2つまたは3つのテーブルしか含まれない場合、心配する結合順序は多くありません。 しかし、可能な結合順序の数は、テーブルの数が増加するにつれて指数関数的に増加する。 10個ほどの入力テーブルを超えると、すべての可能性を徹底的に検索することはもはや実用的ではなく、6つまたは7つのテーブルの計画でも、厄介なほど長い時間がかかる可能性があります。 入力テーブルが多すぎる場合、PostgreSQLplannerは、限られた数の可能性を介して、徹底的な検索から遺伝的な確率的検索に切り替えます。 (切り替えしきい値は、geqo_thresholdランタイムパラメーターによって設定されます。) 遺伝子検索にかかる時間は短くなりますが、必ずしも可能な限り最良の計画を見つけることはできません。

クエリに外部結合が含まれる場合、プランナーの自由度は、プレーン (内部) 結合の場合よりも低くなります。 たとえば、次のことを考慮します。

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

このクエリの制限は表面的には前の例と同様ですが、BとCの結合に一致する行がないaの各行に対して行を発行する必要があるため、セマンティクスは異なります。したがって、プランナーはここでは結合順序を選択できません。BをCに結合し、次にAをその結果に結合する必要があります。 したがって、このクエリは、前のクエリよりも計画にかかる時間が短くなります。 他の場合には、プランナは、複数の結合順序が安全であると判断することができる。 例えば、与えられた:

SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);

最初にAをBまたはCに結合することが有効です。 現在、FULL JOINのみが結合順序を完全に制約しています。 LEFT JOINまたはRIGHT JOINを含む最も実用的なケースは、ある程度再配置できます。

明示的な内部結合構文 (inner join、CROSS JOIN、または装飾されていないJOIN) は、FROMに入力リレーションをリストするのと意味的に同じです。

ほとんどの種類のJOINは結合順序を完全に制約するわけではありませんが、PostgreSQLqueryプランナーに、すべてのjoin句を結合順序の制約として扱うように指示することは可能です。 たとえば、これらの3つのクエリは論理的に同等です。

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
    * クロス参加から選択bクロス参加c WHERE a.id = b.id AND b.ref = c.id;
    SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); 

しかし、プランナーにJOIN命令を尊重するように指示すると、2番目と3番目は最初のものよりも計画にかかる時間が短くなります。 この効果は、3つのテーブルだけで心配する価値はありませんが、多くのテーブルでは命の恩人になる可能性があります。

明示的なjoinによってレイアウトされた結合順序にプランナーを強制するには、join_collapse_limitランタイムパラメーターを1に設定します。 (他の可能な値は以下に説明する。)

単純なFROMリストの項目内でjoin演算子を使用しても問題ないため、検索時間を短縮するために結合順序を完全に制約する必要はありません。 たとえば、次のことを考慮します。

SELECT * FROM a CROSS JOIN b、c、d、e WHERE ...;

join_collapse_limit= 1の場合、プランナーはAをBに結合してから他のテーブルに結合しますが、それ以外の場合はその選択を制限しません。 この例では、可能な結合順序の数は、5分の1に低減される。

このようにプランナの検索を制約することは、計画時間を短縮するため、およびプランナを良好なクエリプランに導くための両方のための有用な技法である。 プランナーがデフォルトで悪い結合順序を選択した場合、join構文を使用してより良い順序を選択するように強制できます。 実験はお勧めです。

計画時間に影響を与える密接に関連する問題は、サブクエリが親クエリに折りたたまれることです。 たとえば、次のことを考慮します。

SELECT *
    x、yから、
        (SELECT * FROM a、b、cどこか何か) AS ss
    どこにsomethingelse; 

この状況は、結合を含むビューの使用から発生する可能性があります。ビュー参照の代わりにビューのSELECTルールが挿入され、上記のようなクエリが生成されます。 通常、プランナーはサブクエリを親に折りたたむことを試みます。

SELECT * FROM x、y、a、b、cどこに何かと何かがあります。

これは通常、サブクエリを個別に計画するよりも良い計画になります。 (例えば、外側のWHERE条件は、XをAに結合することが最初にAの多くの行を除去し、したがって、サブクエリの完全な論理出力を形成する必要性を回避するようなものであり得る。) しかし同時に、計画時間を増やしました。ここでは、2つの別々の3方向結合問題に代わる5方向結合問題があります。 可能性の数の指数関数的増加のために、これは大きな違いを生む。 プランナーは、from_collapse_limit FROMアイテムが親クエリになる場合、サブクエリを折りたたまないことで、大きな結合検索の問題にとらわれないようにします。 この実行時パラメータを上下に調整することで、計画時間と計画の質とをトレードオフすることができます。

from_collapse_limitとjoin_collapse_limitは、ほぼ同じことを行うため、同様の名前が付けられています。1つはプランナーがサブクエリを「フラットアウト」するときを制御し、もう1つは明示的な結合をフラットアウトするときを制御します。 通常、join_collapse_limitをfrom_collapse_limitに設定し (明示的結合とサブクエリが同様に動作するように) 、またはjoin_collapse_limitを1に設定します (明示的結合で結合順序を制御する場合) 。 ただし、計画時間と実行時間のトレードオフを微調整しようとすると、それらを別の方法で設定することができます。