はじめに
この記事は NTTテクノクロス Advent Calendar 2019 の3日目の記事です。
こんにちは！NTTテクノクロスで PostgreSQLを中心としたお仕事 をしています 、中村です。
PostgreSQL10から、宣言型パーティショニング機能が導入され、パーティションの作成が容易になりましたね。
それから2年がたち、宣言的パーティショニングを用いるシステムも増えてきましたので、パーティションテーブルでヒント句を使用する際に留意するケースを確認していきたいと思います。
ヒント句を使えるようにするツール pg_hint_plan は「Amazon RDS for PostgreSQL」や「Amazon Aurora PostgreSQL互換エディション」でも利用できますし、Oracleでパーティションを使用していたシステムを PostgreSQL に移行する際の SQLチューニングにも役立ちますので、覚えておいて損はないですよ！
今回の記事の動作環境は以下の通りです。
・OS : CentOS 7.6
・PostgreSQL 11.6
・pg_hint_plan 1.3.4
＃PostgreSQL12 版の pg_hint_plan がまだリリースされていなかったので
それでは、行ってみましょう！
ケース１：パーティション子テーブルへのヒント句が効かない？（従来型、宣言的共通）
まずは基本から。
ヒント句では、テーブルのスキャン方式を指定できます。使用するインデックスの指定はよく使いますよね。
さて、パーティションテーブルの子テーブルに特定のインデックスを使用するには、どのようにヒント句を記述すればよいでしょうか？
パーティション子テーブルへのヒント句は、親テーブルに対して記述する！
パーティション子テーブルに対してインデックススキャンのヒント句を指定したいとき、全ての子テーブルのインデックスをヒント句に書くのは大変ですよね。
なんとか楽に指定する方法はないものか…！？
あるんです。
pg_hint_plan のドキュメントには以下のような記述があります。
継承テーブルにスキャン方式のヒント句を指定する場合は、オブジェクト名として親テーブルの名称または別名を指定してください。全ての子テーブルに同じスキャン方式を選択します。
つまり、子テーブルのスキャン方法をヒント句で指定したい場合は、親テーブルに対してヒント句を書けばいい、ということですね。
今回検証に使用したパーティションテーブルには、親テーブルと子テーブルの両方に同じカラム構成のインデックスがあるので、ヒント句に親テーブルのインデックスを指定すれば、子テーブルの実行計画にもヒント句が反映されそうです。
ヒント句は以下のように記述します。パーティション親テーブル「t_parent」のインデックス「t_parent_idx1」が使用されるように、ヒント句を記述します。
select /*+ IndexScan( t_parent t_parent_idx1 ) */ id, data
from t_parent
where dt >= '2019-11-11 00:00:00' AND
dt < '2019-11-11 00:05:00' AND
id between 2000 AND 2050;
パーティション親テーブルのインデックスをヒント句に指定したけど、指定したインデックスが子テーブルのスキャンに使われていない！？
さて、親テーブルに対してヒント句を指定すれば、子テーブルにもヒント句が適用されると聞いて、早速試してみることにしました。
その前に、今回の検証で用いたパーティションテーブルの構成を示しますね。
postgres=# \d+ t_parent
テーブル "public.t_parent"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計の対象 | 説明
------+--------------------------------+----------+---------------+------------+------------+------------+------
id | integer | | | | plain | |
dt | timestamp(0) without time zone | | | | plain | |
data | text | | | | extended | |
パーティションキー: RANGE (dt)
インデックス:
"t_parent_idx1" btree (dt)
パーティション: t_child_20191110 FOR VALUES FROM ('2019-11-10 00:00:00') TO ('2019-11-11 00:00:00'),
t_child_20191111 FOR VALUES FROM ('2019-11-11 00:00:00') TO ('2019-11-12 00:00:00'),
t_child_20191112 FOR VALUES FROM ('2019-11-12 00:00:00') TO ('2019-11-13 00:00:00'),
t_child_20191113 FOR VALUES FROM ('2019-11-13 00:00:00') TO ('2019-11-14 00:00:00'),
t_child_20191114 FOR VALUES FROM ('2019-11-14 00:00:00') TO ('2019-11-15 00:00:00')
子テーブルの構成は以下のようになっており、カラム「id」に対して主キー制約を付与しています。
postgres=# \d t_child_20191111
テーブル "public.t_child_20191111"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+--------------------------------+----------+---------------+------------
id | integer | | not null |
dt | timestamp(0) without time zone | | |
data | text | | |
パーティション: t_parent FOR VALUES FROM ('2019-11-11 00:00:00') TO ('2019-11-12 00:00:00')
インデックス:
"t_child_20191111_pk" PRIMARY KEY, btree (id)
"t_child_20191111_dt_idx" btree (dt)
それではまず、ヒント句を記述しない状態での実行計画を確認しましょう。
postgres=# explain analyze select id,data from t_parent t where dt >= '2019-11-11 00:00:00' AND dt < '2019-11-12 00:00:00' and id between 2000 and 2050;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..19.19 rows=51 width=9) (actual time=0.174..0.232 rows=51 loops=1)
-> Index Scan using t_child_20191111_pk on t_child_20191111 t (cost=0.29..18.94 rows=51 width=9) (actual time=0.172..0.217 rows=51 loops=1)
Index Cond: ((id >= 2000) AND (id <= 2050))
Filter: ((dt >= '2019-11-11 00:00:00'::timestamp without time zone) AND (dt < '2019-11-12 00:00:00'::timestamp without time zone))
Planning Time: 2.331 ms
Execution Time: 0.278 ms
(6 行)
この実行計画で使われているパーティション子テーブルのインデックスは「t_child_20191111_pk」。子テーブルの主キーインデックスです。
これを、今はFilter条件になっている「dt」日付カラムのインデックスを使用するように指定したいと思います。
まずは、動作確認のために、子テーブルに直接ヒント句を指定してみましょう。
ここでは、子テーブル「t_child_20191111」のインデックス「t_child_20191111_dt_idx」を指定したヒント句「/*+ IndexScan( t_child_20191111 t_child_20191111_dt_idx ) */」を記述すればよさそうですね。
postgres=# explain analyze select /*+ IndexScan( t_child_20191111 t_child_20191111_dt_idx ) */ id,data from t_parent t where dt >= '2019-11-11 00:00:00' AND dt < '2019-11-11 00:05:00' and id between 2000 and 2050;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..13.02 rows=1 width=9) (actual time=0.022..0.022 rows=0 loops=1)
-> Index Scan using t_child_20191111_dt_idx on t_child_20191111 t (cost=0.29..13.01 rows=1 width=9) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ((dt >= '2019-11-11 00:00:00'::timestamp without time zone) AND (dt < '2019-11-11 00:05:00'::timestamp without time zone))
Filter: ((id >= 2000) AND (id <= 2050))
Rows Removed by Filter: 149
Planning Time: 0.214 ms
Execution Time: 0.032 ms
(7 行)
期待通り、ヒント句に指定したインデックス「t_child_20191111_dt_idx」が使用されました！
では次に、「t_child_20191111_dt_idx」と同じカラム構成をもつ、親テーブルの「t_parent」のインデックス「t_parent_idx1」をヒント句に指定しましょう。
これで、子テーブルのインデックス「t_child_20191111_dt_idx」が選択されるはずですね。
postgres=# explain analyze select /*+ IndexScan( t_parent t_parent_idx1 ) */ id,data from t_parent t where dt >= '2019-11-11 00:00:00' AND dt < '2019-11-11 00:05:00' and id between 2000 and 2050;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..9.57 rows=1 width=9) (actual time=0.016..0.016 rows=0 loops=1)
-> Index Scan using t_child_20191111_pk on t_child_20191111 t (cost=0.29..9.56 rows=1 width=9) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((id >= 2000) AND (id <= 2050))
Filter: ((dt >= '2019-11-11 00:00:00'::timestamp without time zone) AND (dt < '2019-11-11 00:05:00'::timestamp without time zone))
Rows Removed by Filter: 51
Planning Time: 0.822 ms
Execution Time: 0.034 ms
(7 行)
あれ？期待したインデックス「t_child_20191111_dt_idx」が使用されていません！なぜでしょうか…？
ヒント句が効いていても、それよりコストが低いプランがある場合、他のスキャン方式が選択されることもあり得ますが、今回は違います。
原因は、SQLの中で親テーブル「t_parent」に別名「t」を付与しているからです！
pg_hint_plan のドキュメントには記載されていませんが、パーティションの親テーブルに別名を付与した場合は、必ずヒント句にも別名を指定しなければなりません。これは、PostgreSQL9.6以前の継承を用いたパーティションでも同じです。
これを踏まえて、ヒント句に別名「t」を指定してみます。インデックス名の記述は変えません。
postgres=# explain analyze select /*+ IndexScan( t t_parent_idx1 ) */ id, data from t_parent t where dt >= '2019-11-11 00:00:00' AND dt < '2019-11-11 00:05:00' and id between 2000 and 2050;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..13.02 rows=1 width=9) (actual time=0.022..0.022 rows=0 loops=1)
-> Index Scan using t_child_20191111_dt_idx on t_child_20191111 t (cost=0.29..13.01 rows=1 width=9) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: ((dt >= '2019-11-11 00:00:00'::timestamp without time zone) AND (dt < '2019-11-11 00:05:00'::timestamp without time zone))
Filter: ((id >= 2000) AND (id <= 2050))
Rows Removed by Filter: 149
Planning Time: 0.217 ms
Execution Time: 0.032 ms
(7 行)
GOOD！期待通り「t_child_20191111_dt_idx」のインデックスが使用されましたね！
パーティションではないテーブルの場合は、別名でなくてもヒント句が有効になりますが、「テーブルに別名を付与した場合は、ヒント句にも別名を記述する」というのを徹底しておくと間違いありません。
それでは、次に行ってみましょう！（＾ｖ＾）
ケース２：パーティション親テーブルに主キー制約が作れない？（宣言的パーティショニングの場合）
パーティション子テーブルで使用するインデックスをヒント句で指定するには、パーティション親テーブルに対してヒント句を書けばよい、ということがわかりました。
ということは、親テーブルにも、子テーブルと同じインデックス定義が必要になるということですよね？インデックスがなければヒント句に書けませんものね。
やることが分かればあとは実装するだけ！では早速、親テーブルにインデックスを作ってみましょう！
パーティション親テーブルに作成できる一意なインデックスはパーティション分割キーと同じカラムのみ！
パーティション子テーブルの主キーインデックスをヒント句に指定したいから、親テーブルにも同じカラムの主キーインデックスを作ろう！…としたところ、エラーになってしまいました。
出力されたエラーメッセージによると、カラム「id」はパーティション分割キーではないため、主キー制約に指定できないようです。パーティション分割キー以外のカラムに対して、主キー制約や一意インデックスを作成しようとするとエラーになります。
postgres=# alter table t_parent add constraint t_parent_pk primary key (id);
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "t_parent" lacks column "dt" which is part of the partition key.
一意キーインデックスも作成できませんでした。
postgres=# create unique index t_parent_uk on t_parent (id);
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "t_parent" lacks column "dt" which is part of the partition key.
とういうわけで、子テーブルの主キーをヒント句に指定したい場合は、親テーブルに同じ構成のカラムの「普通のインデックス」を作成して対処するしかないようですね。
＃インデックスのヒントを指定するだけなので、とりあえずあれば動く…
親テーブルに、子テーブルの主キーと同じインデックスを作成したら子テーブルにも作成されたのですが…
とりあえず作ってみた主キー制約と同じカラム(id)のインデックス。
これで子テーブルのインデックスにヒント句を指定できるようになったし、問題解決…と思ったら、親テーブルに作成したインデックスは子テーブルにも自動的に継承されてしまうんですよね。。
postgres=# \d t_parent
テーブル "public.t_parent"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+--------------------------------+----------+---------------+------------
id | integer | | |
dt | timestamp(0) without time zone | | |
data | text | | |
パーティションキー: RANGE (dt)
インデックス:
"t_parent_idx1" btree (dt)
"t_parent_pk" btree (id)
パーティション数: 5 (\d+ で一覧を表示)。
というわけで、子テーブル「t_child_20191111」には今後使われることがないけれど、メンテナンスはし続けなければならないという、余計なインデックスが増えることになってしまいました。
postgres=# \d t_child_20191111
テーブル "public.t_child_20191111"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+--------------------------------+----------+---------------+------------
id | integer | | not null |
dt | timestamp(0) without time zone | | |
data | text | | |
パーティション: t_parent FOR VALUES FROM ('2019-11-11 00:00:00') TO ('2019-11-12 00:00:00')
インデックス:
"t_child_20191111_pk" PRIMARY KEY, btree (id)
"t_child_20191111_dt_idx" btree (dt) ←親テーブルからの継承、削除できない。
"t_child_20191111_id_idx" btree (id) ←親テーブルからの継承、削除できない。しかも主キー制約と重複するので絶対に使われないインデックス
実はこれ、PostgreSQL9.6 までの従来型のパーティショニングでは考慮不要な問題なんです。
従来型パーティションでは、親テーブルにもパーティション分割キーと同じカラムの主キー制約が作成できるので、宣言型パーティショニングでヒント句を使用するためだけに作成したインデックスは不要です。
ここだけは、従来型パーティショニングのほうがよい点ですね。＾＾
以下に、従来型パーティショニングで作成した親テーブルと、子テーブルの定義を記載します。
postgres=# \d t2_parent
テーブル "public.t2_parent"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+--------------------------------+----------+---------------+------------
id | integer | | not null |
dt | timestamp(0) without time zone | | |
data | text | | |
インデックス:
"t2_parent_pk" PRIMARY KEY, btree (id)
"t2_parent_idx1" btree (dt)
トリガー:
insert_t2_parent_trigger BEFORE INSERT ON t2_parent FOR EACH ROW EXECUTE PROCEDURE t2_parent_insert_trigger()
update_t2_parent_trigger BEFORE UPDATE ON t2_parent FOR EACH ROW EXECUTE PROCEDURE t2_parent_update_trigger()
子テーブル数: 5 (\d+ で一覧を表示)
見ての通り、↑の親テーブルの主キー制約のカラム(id)と、↓の子テーブルのCheck制約＝パーティション分割キー(dt)が別でも問題ありません。
postgres=# \d t2_child_20191111
テーブル "public.t2_child_20191111"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------+--------------------------------+----------+---------------+------------
id | integer | | not null |
dt | timestamp(0) without time zone | | |
data | text | | |
インデックス:
"t2_child_20191111_pk" PRIMARY KEY, btree (id)
"t2_child_20191111_idx1" btree (dt)
Check 制約:
"t2_child_20191111_dt_check" CHECK (dt >= '2019-11-11 00:00:00'::timestamp without time zone AND dt < '2019-11-12'::date)
継承元: t2_parent
まとめ
SQLの実行計画を最適化しようとするとき、まず一番最初に行うのは、AUTOANALYZEや手動ANALYZEによる統計情報の更新を適宜行うことですね。
これによって、PostgreSQLが常に最適な実行計画を選択できれば、ヒント句を使う必要はありません。
ですが、それだけではうまくいかないことはよくあります。
例えばOracleですでにヒント句を使用して実行計画を制御していたSQLを移行した場合や、移行したSQLが複雑で業務要件を踏まえてのSQL書き換えによる対処が困難、といったケースもありえます。
また、大量データの削除・挿入が頻繁に行われるテーブルでは、一時的に統計情報が実態と乖離し、その影響で最適でないSQLの実行計画が選択される、そんなケースもあります。
最適な実行計画が事前に計画できる場合は、ヒント句を使うことで設計書に影響を与えずに性能改善を行うことも可能ですので、必要に応じて使えるようにしておくことは武器になると考えます。
かゆいところに手が届くヒント句。
適切なANALYZEと併用して、楽しくSQLチューニングをしていきましょう＾＾
それでは、NTTテクノクロス Advent Calendar 2019 ４日目も、引き続きお楽しみください！