PostgreSQLの実行計画の推定行数と実行数の乖離改善の考え方

はじめに

以前こんなツイートをしました。

すると、リプライで色々とコメントを頂きました。（疑問を投げかけたら答えてくれる方々、本当にいつもありがたいです🙇‍♂️）

ということで、本記事では推定行数と実際の行数の乖離を減らすために何をやったのかを備忘として書きます。
ただ、実際のSQLや実行計画を書くことはできないので、あくまでどんな考え方をしたのか、ということを書きます。

対処法①（対象のテーブルのautovacuum頻度を変更）

対象のテーブルはかなり更新の激しいテーブルだと聞いていたので、まずは統計情報が最新化されているかを考えました。
更新が激しくてautovacuum時の自動ANALYZEが追い付いていないんじゃないかと考え、対象のテーブルだけ自動ANALYZEの頻度が上がるように設定を変更しました。

PostgreSQLの設定パラメータは基本的にはpostgresql.confで設定しますが、今回は更新の激しいテーブルにだけ設定をしたいので、対象のテーブルの以下の設定のみ変更しました。

• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor

=# ALTER TABLE table_name SET (autovacuum_analyze_threshold = int, autovacuum_analyze_scale_factor = int);

ちなみにテーブル毎に設定可能なパラメータはこのドキュメントに書いています。
https://www.postgresql.jp/document/current/html/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

対処法②（統計情報対象を拡大）

次に、ANALYZEはちゃんと実行されているけど、サンプリング数が少なくて有効な統計情報が作成されていないんじゃないかと考えました。

ということで、またまた設定パラメータを変更してみます。

PostgreSQLサーバ全体のANALYZEのサンプリング数はdefault_statistics_targetで設定できますが、今回は対象のテーブルだけのサンプリング数を増やしたいため、テーブル毎の設定を行いました。

具体的には、以下のコマンドで列ごとのサンプリング数を設定できるので、いったん全列に対してサンプリング数を増加させました。

=# ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS int;

対処法③（拡張統計情報を使用）

次に、ANALYZEも適切に実行されていて、有効な統計情報が取られているけど、そもそもPostgreSQLが苦手な条件が指定されているんじゃないかと考えました。

例えば以下のようなとき、PostgreSQLのプランナは2つの条件を独立したものとみなしてしまい、行数を正確に見積もることができません。

• テーブルAに2つの列a1, a2がある
• a1, a2に関数従属がある（a1=a2, a1=a2*2など）
• WHERE句にa1, a2の条件が指定

そこで、拡張統計情報を使うことを考えました。以下のようにテーブルAのa1, a2に対して拡張統計情報を定義します。このとき、statistics_kindで統計種別（ndistinct、dependencies、mcv）を指定できますが、省略すればすべての統計種別が取得されるので、まずは指定しませんでした。

=# CREATE STATISTICS statistics_name ON a1, a2 FROM A;

詳しくはドキュメントを参照してください。
https://www.postgresql.jp/document/current/html/sql-createstatistics.html

対処法④（ヒント句を使用）

最後に、どうしても上手くいかないならpg_hint_planのヒント句で解決しようとなりました。
https://github.com/ossc-db/pg_hint_plan

まとめ

いかがでしたでしょうか？
ひとまず自分の知識でどのように考えたかを書いてみました。参考になれば幸いです。

参考

https://www.amazon.co.jp/［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則-Software-Design-plus-上原/dp/4297132060