4.4.1 SQLチューニング

EXPLAINの使用

アプリケーションが中央管理ノードに接続し、データノード上のテーブルにアクセスするSQLを実行した場合、中央管理ノードのpostgres_scaleout_fdwは、データノードに対して、必要なデータを取得するSQLを内部的に実行します。postgres_scaleout_fdwが内部的に実行しているSQLは、EXPLAINコマンドやEXPLAIN ANALYZEコマンドにVERBOSEオプションをつけることで出力できます。下記は、データノード1上のテーブルpart1とデータノード2上のテーブルpart2を結合した場合の例です。

postgres=#  EXPLAIN VERBOSE SELECT * FROM part1, part2 WHERE part1.id = part2.id AND part1.id < 10;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Join  (cost=241.85..405.75 rows=3105 width=72)
   Output: part1.id, part1.value, part2.id, part2.value
   Hash Cond: (part2.id = part1.id)
   ->  Foreign Scan on public.part2  (cost=100.00..150.95 rows=1365 width=36)
         Output: part2.id, part2.value
         Remote SQL: SELECT id, value FROM public.part2
   ->  Hash  (cost=136.16..136.16 rows=455 width=36)
         Output: part1.id, part1.value
         ->  Foreign Scan on public.part1  (cost=100.00..136.16 rows=455 width=36)
               Output: part1.id, part1.value
               Remote SQL: SELECT id, value FROM public.part1 WHERE ((id < 10))
(11 行)

Remote SQLの箇所に、それぞれのテーブルのデータを取得するために内部的に実行されたSQLが表示されます。上記の場合、part1のデータを取得する際にはid < 10の条件が付与されていますが、part2のデータを取得する際には条件が付与されておらず、全てのデータを取得していることがわかります。ここで、part1.id = part2.idの条件があることからpart1.id < 10であれば、part2.id < 10であることがわかります。そのため、下記のようにpart2.id < 10の条件もSQLに追加することで、part2のデータを取得する際にも全ての行を取得する必要がなくなります。

postgres=#  EXPLAIN VERBOSE SELECT * FROM part1, part2 WHERE part1.id = part2.id AND part1.id < 10 AND part2.id < 10;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Hash Join  (cost=241.85..315.66 rows=1035 width=72)
   Output: part1.id, part1.value, part2.id, part2.value
   Hash Cond: (part1.id = part2.id)
   ->  Foreign Scan on public.part1  (cost=100.00..136.16 rows=455 width=36)
         Output: part1.id, part1.value
         Remote SQL: SELECT id, value FROM public.part1 WHERE ((id < 10))
   ->  Hash  (cost=136.16..136.16 rows=455 width=36)
         Output: part2.id, part2.value
         ->  Foreign Scan on public.part2  (cost=100.00..136.16 rows=455 width=36)
               Output: part2.id, part2.value
               Remote SQL: SELECT id, value FROM public.part2 WHERE ((id < 10))
(11 行)

auto_explainの使用

上記のようにEXPLAINを使用することで、中央管理ノードからデータノードに内部的に実行されるSQLを出力できますが、そのSQLがデータノードで実行される際の実行計画は出力できません。

auto_explainを使用することで、データノードで実行される際の実行計画を出力できます。下記は、auto_explainにより実行計画を出力する例です。

業務1で使用しているapp1userでauto_explainの設定をします。この例では、app1userを使用している他のセッションにも影響があるため、業務実施中に設定する場合には、業務で使用しているユーザーと別のユーザーで実施するなど、業務への性能影響に注意してください。
```
postgres=# SET pgx_ddl_target_node = 'ALLNODES';
SET
postgres=# ALTER USER app1user SET session_preload_libraries = 'auto_explain';
ALTER ROLE
postgres=# ALTER USER app1user SET auto_explain.log_min_duration = 0;
ALTER ROLE
postgres=# RESET pgx_ddl_target_node;
RESET
```

中央管理ノードでEXPLAIN ANALYZEを実行します。なお、EXPLAIN ANALYZEは、実際にSQLが実行されるため、必要に応じてROLLBACKしてください。

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM part1, part2 WHERE part1.id = part2.id AND part1.id < 10 AND part2.id < 10;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=241.85..451.58 rows=1 width=72) (actual time=1.760..1.862 rows=9 loops=1)
   Output: part1.id, part1.value, part2.id, part2.value
   Hash Cond: (part1.id = part2.id)
   ->  Foreign Scan on public.part1  (cost=100.00..309.18 rows=9 width=36) (actual time=0.769..0.797 rows=9 loops=1)
         Output: part1.id, part1.value
         Remote SQL: SELECT id, value FROM public.part1 WHERE ((id < 10))
   ->  Hash  (cost=136.16..136.16 rows=455 width=36) (actual time=0.947..0.956 rows=9 loops=1)
         Output: part2.id, part2.value
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Foreign Scan on public.part2  (cost=100.00..136.16 rows=455 width=36) (actual time=0.878..0.906 rows=9 loops=1)
               Output: part2.id, part2.value
               Remote SQL: SELECT id, value FROM public.part2 WHERE ((id < 10))
 Planning Time: 0.336 ms
 Execution Time: 4.332 ms
(14 行)

データノードでのログを確認します。下記では、Index Scanが実施されていることがわかります。

00000: 2022-01-24 15:39:39 JST [3885757]: [2-1] user = app1user,db =postgres,remote = ::1(56188) app = 3885754_for_psql LOG:  duration: 0.029 ms  plan:
        Query Text: DECLARE c1 CURSOR FOR
        SELECT id, value FROM public.part1 WHERE ((id < 10))
        Index Scan using part1_pkey on part1  (cost=0.29..8.48 rows=9 width=36)
          Index Cond: (id < 10)

pg_hint_planの使用

pg_hint_planでは、ヒントの指定方法として、下記の2つがあります。

コメントでの指定
テーブルでの指定

これらのヒントは、ノード内の実行計画にのみ有効であり、中央管理ノードからデータノードに内部的に実行されるSQLの実行計画には効果がないことに注意してください。

中央管理ノードからデータノードに内部的に実行されるSQLの実行計画を指定する場合には、“EXPLAINの使用”を参照して内部的に実行されるSQLを調査し、そのSQLに対するヒントを、データノードのヒント用のテーブルに登録してください。

データの配置について

“EXPLAINの使用”で説明したSQLの実行計画のように、データノード1のテーブルとデータノード2のテーブルを結合する場合、それぞれのデータノードのデータを中央管理ノード上に収集したのちに、中央管理ノード上で結合が実施されます。このように、データノードをまたいだSQLは、1つのノード内でのSQLに比べて、通信によるオーバーヘッドが発生します。

そのため、システム設計の段階で、なるべくノードをまたがないように設計したうえで、ノード間ネットワークは高速なネットワークを利用するようにしてください。