Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What is going on with this article?
@nuko_yokohama

pg_store_plansを使ってみた(1)

More than 1 year has passed since last update.

はじめに

先日、pg_plan_advsrを使ってみた(1)という記事を書いたんだけど、そのpg_plan_advsrでは、pg_hint_planpg_store_plansという拡張機能を利用している。
pg_hint_planは(実案件ではないが)何回か使ったことはあるけど、もう一つのpg_store_plansはそういえば使ったことがなかった。依存している拡張機能を良くわからないままpg_plan_advsrを調べ続けるのも、ちょっと不安だったので、pg_plan_advsrの調査を続ける前に、pg_store_plansを使ってみようと思った。

pg_stat_statements自体は、以前、ちょっと使ってみたことはあるけど、pg_store_plansとの共通点も多く、またpg_store_plansとの併用もできるということで、改めて使ってみることにした。

インストール手順等はpg_plan_advsrを使ってみた(1)の記事と若干重複しているけどご容赦を。

pg_store_plans is 何?

  • すごく簡単に言うと、contrib/pg_stat_statementsによく似たモジュールである。contrib/pg_stat_statementsではSQL文とその統計情報を記録するが、pg_store_plansは、実行された「ステートメントSQL文の実行計画」とその統計情報を記録する。
  • PostgreSQL 9.4以降に対応。
  • 単体で使うだけでなく、他の拡張モジュールを補助するもの?
    • pg_statsinfoとか
    • pg_plan_advsrとか
  • contrib/auto_explainを包含しているともいえる。
  • 今のところ、AWS RDS PostgreSQLや、AWS Aurora PostgreSQL互換には対応していなかった(はず)。

pg_store_plansのインストール

パッケージの入手

pg_store_plansのページを見ると、ビルド用のソースとRPM作成用のSPECファイルが置いてある。RPMファイル自体は置いていない。

ビルドインストール

今回はソースをビルドしてインストールする。
まず、pg_store_plansのインストール前にPostgreSQL本体をインストールする。詳細な手順は省略。今回はPostgreSQL 11.2をソースビルドしてインストールしている。
次に、pg_store_plansのソースコードを入手する。
リリース用のページから、ソースのソースコードのアーカイブ(tar.gz)をDLするなり、リポジトリ自体をgit cloneするなりで入手する。今回は、pg_store_plans 1.3のソースアーカイブをwgetコマンドでDLして解凍する。

$ wget https://github.com/ossc-db/pg_store_plans/archive/1.3.tar.gz
$ xfz 1.3.tar.gz

解凍したら、解凍先のフォルダに移動して、make & make installするだけ。

$ cd pg_store_plans-1.3/
$ make USE_PGXS=1
$ make USE_PGXS=1 install

ドキュメント

pg_store_plansのドキュメントは、リポジトリ内のdocフォルダ内にある。
ドキュメントの形式はMarkdownではなく、HTML形式。ブラウザで開いてみると、PostgreSQL公式文書の形式になっていることがわかる。残念ながら日本語ドキュメントはない。

pg_store_plans_doc_image.png

ドキュメントの内容については後で説明する。

PostgreSQLサーバの環境設定

pg_store_plansを使う場合、postgresql.confのshared_preload_librariesにpg_stre_planasのモジュールを設定して、PostgreSQLサーバを起動する必要がある。

shared_preload_libraries = 'pg_store_plans'

データベースへの登録

このモジュールもshared_preload_librariesに登録するだけでなく、CREATE EXTENSIONコマンドで拡張機能をデータベースに登録する必要がある。
なお、pg_store_plans拡張機能は、データベースオーナーの権限では登録できず、特権ロールが必要になる(これは、pg_stat_statmentsも同様)。

$ psql -U user1 testdb -c "CREATE EXTENSION pg_store_plans"
ERROR:  permission denied to create extension "pg_store_plans"
HINT:  Must be superuser to create this extension.
$ psql -U postgres testdb -c "CREATE EXTENSION pg_store_plans"
CREATE EXTENSION
$

何ができるのか

ビューの提供

pg_stat_statementsと同様に、実行計画に関連する統計情報を表示するビュー(pg_store_plansビュー)を提供する。以下に、pg_store_plansビューの内容を示す。

列名 参照 説明
userid oid pg_authid.oid 文を実行したユーザのOID。
dbid oid pg_database.oid 文が実行されたデータベースのOID
queryid bigint 文のクエリ文字列から計算された内部ハッシュコード。
このqueryidと、pg_stat_statementsが提供するpg_stat_statementsビューのqueryidを使って結合可能。
planid bigint 文の解析木から計算された内部ハッシュコード
queryid_stat_statements bigint pg_stat_statementsのクエリハッシュコードのコピー。 これはpg_stat_statementsがインストールされている場合にのみ利用可能です。
plan text 代表的な実行計画のテキスト。 フォーマットは設定パラメータpg_store_plans.plan_formatで指定されます。
calls bigint 実行回数。
total_time double precision SQL文の処理に費やした総時間(ミリ秒単位)
rows bigint プランを使用している文によって取得または影響を受けた行の総数。
shared_blks_hit bigint プランを使用している文による共有ブロックキャッシュヒットの総数。
shared_blks_read bigint プランを使用している文によって読み取られた共有ブロックの合計数。
shared_blks_dirtied bigint プランを使用している文によってダーティにされた共有ブロックの合計数。
shared_blks_written bigint プランを使用している文によって書き込まれた共有ブロックの合計数。
local_blks_hit bigint プランを使用している文によるローカルブロックキャッシュヒットの総数。
local_blks_read bigint プランを使用している文によって読み取られたローカルブロックの総数。
local_blks_dirtied bigint プランを使用している文によってダーティにされたローカルブロックの総数。
local_blks_written bigint プランを使用している文によって書き込まれたローカルブロックの総数。
temp_blks_read bigint プランを使用している文によって読み取られた一時ブロックの合計数。
temp_blks_written bigint プランを使用している文によって書き込まれた一時ブロックの合計数。
blk_read_time double precision 計画を使用している文がブロックの読取りに費やした合計時間(ミリ秒単位)(track_io_timingが有効な場合。それ以外の場合はゼロ)。
blk_write_time double precision その計画を使用しているステートメントがブロックの書き込みに費やした合計時間(ミリ秒単位)(track_io_timingが有効な場合、それ以外の場合はゼロ)。
first_call timestamp with time zone このプランを使用した、最初のクエリの呼び出しのタイムスタンプ。
last_call timestamp with time zone このプランを使用しているクエリの最後の呼び出しのタイムスタンプ。

なお、このビューをpsql\d+メタコマンドで表示してみると、pg_store_plans()関数を検索した結果をビューにしていることがわかる(なので、当然ながらpg_store_plansビューに対して更新操作はできない)。

関数

関数名 引数 返却型 説明
pg_store_plans_reset なし void pg_store_plans_resetは、これまでにpg_store_plansによって収集されたすべての統計を破棄する。 デフォルトでは、スーパーユーザーだけがこの機能を実行できる。
pg_store_plans showtext boolean setof record pg_store_plansビューはpg_store_plansという名前の関数で定義されています。
pg_store_hash_query query text oid この関数はクエリテキストのハッシュ値を計算する。 pg_store_plansのqueryidの計算にも同じアルゴリズムが使用されているため、この関数はpg_store_plansと結合するのに使用できる。
pg_store_plans_textplan query text text この関数はpg_store_plansのplanの生の表現から通常のテキスト表現を生成する。これはpg_store_plans.plan_formats = 'raw'のときにそこに表示される。 結果計画テキストはjson表現から生成されるため、 EXPLAINコマンドから直接取得するものとは多少異なる場合がある。
pg_store_plans_jsonplan query text text この関数は、 "short format json plan"または "raw format"を通常のjson形式に変換します。 short format jsonはpg_store_plansのplanの内部形式である。pg_store_plans.plan_formats= 'json'のときに表示される。
pg_store_plans_xmlplan query text text この関数は、pg_store_plansのplanのraw表現からXML表現を生成する。これはpg_store_plans.plan_formats = 'xml'のときに表示さされる。
pg_store_plans_yamlplan query text text この関数はpg_store_plansのplanのraw表現からYAML表現を生成する。これはpg_store_plans.plan_formats = 'yaml'のときに表示される。

制御パラメータ

パラメータ名の名前空間(pg_store_plans)は省略しているので注意。

パラメータ名 デフォルト値 context 説明
max integer 1000 postmaster pg_store_plansモジュールによってトレースされる計画の最大数(すなわち、pg_store_plansビュー内の最大行数)。 それを超えた場合、最も頻度の低い実行計画に関する情報は破棄される。 デフォルト値は1000です。このパラメータはサーバ起動時にのみ設定可能。
pg_stat_statementsの同種のパラメータのデフォルトは5000なので、少し異なることに注意。
track enum top superuser pg_stat_statementsと同様に、pg_store_plans.trackはどの文がモジュールによってカウントされるかを制御する。トップレベルの文(クライアントによって直接発行されたもの)を追跡するにはtopを、関数内で呼び出された文などのネストされた文も追跡するにはallを指定し、ステートメント統計収集を無効にするにはnoneを指定する。 デフォルト値はtopである。 この設定を変更できるのはスーパーユーザーだけである。
plan_format enum text user pg_store_plansが保存している実行計画のフォーマットを制御する。 textがデフォルト値で、通常のテキスト表現で表示される。json, xml, yamlは対応するフォーマットで表示される。 rawpg_store_plans_*関数に渡す内部表現(シンプルなJSON形式)を示す。
min_duration integer 0 superuser 文のプランがログに記録される最小の実行時間(ミリ秒)。 これを0 (デフォルト)に設定すると、すべての実行計画がログに記録される。 この設定を変更できるのは、スーパーユーザーのみ。
log_analyze bool off superuser 単なるEXPLAIN出力ではなく、EXPLAIN ANALYZE出力を(保存される)実行計画に含めるようにする。 このパラメータはデフォルトではoff
log_buffers bool off superuser 単なるEXPLAIN出力ではなく、EXPLAIN (ANALYZE、BUFFERS) 出力を実行計画に含めるようにする。 このパラメータはデフォルトではoff
log_timing bool on superuser offに設定すると、実際のタイミングを記録できなくなる。 システムクロックを繰り返し読み取ることによるオーバーヘッドは、システムによってはクエリを大幅に遅くする可能性があるため、各実行ノードの正確な実行時間ではなく実際の行数のみが必要な場合は、このパラメータをoffに設定すると便利である。 pg_store_plans.log_analyzeがonの場合、ステートメント全体の実行時間は常に測定される。 デフォルトはon
log_triggers bool off superuser トリガー実行の統計を記録済み実行計画に含めるようする。 pg_store_plans.log_analyzeがオンになっていない限り、このパラメータは効果はない。デフォルトはoff
verbose bool off superuser 単なるEXPLAIN出力ではなく、EXPLAIN VERBOSE出力が実行計画に含まれる。 このパラメータはデフォルトではoff
save bool on sighup サーバーのシャットダウン後に計画統計を保存するかどうかを指定する。offの場合、統計はシャットダウン時に保存されず、サーバー起動時に再ロードされません。 デフォルト値はon。 このパラメータはpostgresql.confファイルまたはサーバーのコマンドラインでのみ設定できる。

使ってみた

とりあえず、最初はデフォルト設定のままで動かしてみる。

testdb=# SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_store_plans.%';
            name             | setting
-----------------------------+---------
 pg_store_plans.log_analyze  | off
 pg_store_plans.log_buffers  | off
 pg_store_plans.log_timing   | on
 pg_store_plans.log_triggers | off
 pg_store_plans.log_verbose  | off
 pg_store_plans.max          | 1000
 pg_store_plans.min_duration | 0
 pg_store_plans.plan_format  | text
 pg_store_plans.save         | on
 pg_store_plans.track        | top
(10 rows)

こういうパラメータ確認のSQLや、psql内部で発行されるキーワード/データベースオブジェクト名の補完のためのSQLも当然ながら、pg_store_plansに記録されてしまうので、一旦、pg_store_plans_reset();関数を発行してリセットしておく。

testdb=# SELECT pg_store_plans_reset();
 pg_store_plans_reset
----------------------

(1 row)

この状態で、pgbenchを動かして、ますスキーマの生成とデータベースの初期化を行う。

$ pgbench -U user1 -i -s 10 --unlogged-tables testdb -q
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
1000000 of 1000000 tuples (100%) done (elapsed 0.70 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

pgbenchの初期化が終わったあとでpg_store_plansビューのうち、userid, dbid, queryid, planidのみを表示してみる。


testdb=# SELECT userid, dbid, queryid, planid FROM ``pg_store_plans``;
 userid | dbid  |  queryid   |   planid
--------+-------+------------+------------
  30351 | 30352 | 3126036813 | 4099339529
  30351 | 30352 | 1446640908 | 1861260675
     10 | 30352 | 3137955951 | 2881624998
(3 rows)

この状態で、では次にqueryidに条件をつけて検索してみる。


testdb=# SELECT queryid, plan FROM pg_store_pans WHERE queryid IN (3126036813, 1446640908);
  queryid   |                             plan
------------+---------------------------------------------------------------
 3126036813 | Insert on pgbench_tellers  (cost=0.00..0.01 rows=1 width=44) +
            |   ->  Result  (cost=0.00..0.01 rows=1 width=44)
 1446640908 | Insert on pgbench_branches  (cost=0.00..0.01 rows=1 width=40)+
            |   ->  Result  (cost=0.00..0.01 rows=1 width=40)
(2 rows)

それぞれ、pgbench_tellerspgbench_branchesへのINSERT文の実行計画が表示される。そう、一番巨大なpgbench_accountsCOPYはDMLではないので、記録対象外となる。

今度はpgbenchによる更新/参照を行う。pgbenchのデフォルトトランザクションが、どういったSQLを発行するのかは、pgbenchの公式ドキュメントに記載されているが、ざっと抜き出すと、

  • 3種類のUPDATE
  • 1種類のSELECT
  • 1種類のDELETE

がpg_store_plansビューに記録されるはずである。

BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;

では、一旦、記録された実行計画を削除し、その後にpgbenchを使って、上記のトランザクションを1000回実行してみる。

$ pgbench -U user1 -c 1 -t 1000 testdb
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
latency average = 0.909 ms
tps = 1100.143956 (including connections establishing)
tps = 1101.778674 (excluding connections establishing)

この状態で、pg_store_plansのqueryidとcallsとplanを参照する。

testdb=# SELECT queryid, calls, plan FROM pg_store_plans;
  queryid   | calls |                                                 plan
------------+-------+------------------------------------------------------------------------------------------------------
 1445217560 |  1000 | Update on pgbench_tellers  (cost=0.00..2.25 rows=1 width=106)                                       +
            |       |   ->  Seq Scan on pgbench_tellers  (cost=0.00..2.25 rows=1 width=106)                               +
            |       |         Filter: (tid = 38)
  796100257 |  1000 | Update on pgbench_accounts  (cost=0.42..8.45 rows=1 width=103)                                      +
            |       |   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..8.45 rows=1 width=103)+
            |       |         Index Cond: (aid = 983785)
 2767262120 |  1000 | Insert on pgbench_history  (cost=0.00..0.01 rows=1 width=56)                                        +
            |       |   ->  Result  (cost=0.00..0.01 rows=1 width=56)
  570324514 |     1 | Aggregate  (cost=1.12..1.14 rows=1 width=8)                                                         +
            |       |   ->  Seq Scan on pgbench_branches  (cost=0.00..1.10 rows=10 width=0)
 2032872830 |  1000 | Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..8.44 rows=1 width=4)        +
            |       |   Index Cond: (aid = 983785)
 1437714141 |  1000 | Update on pgbench_branches  (cost=0.00..1.13 rows=1 width=106)                                      +
            |       |   ->  Seq Scan on pgbench_branches  (cost=0.00..1.13 rows=1 width=106)                              +
            |       |         Filter: (bid = 6)
 3137955951 |     1 | Result  (cost=0.00..0.01 rows=1 width=4)
 4194727582 |     1 | Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=40)
(8 rows)

上記の結果をざっと見てみると・・・

  • queryidが、1445217560, 796100257, 2767262120, 2032872830, 1437714141のものについては想定どおり、pgbenchで発行されるDMLの実行計画が出力されている。また、実行回数もcallsに示された回数と一致している。
  • queryidが570324514のものは、おそらくpgbenchの測定前にスケールファクタを算出するために、pgbench_branchesの行数を取得しているのだろう。
  • queryidが3137955951のものは、これだけでは正直言ってわからん。クエリ内容を見たいなら、pg_store_plansではなく、pg_stat_statementsを組み込んで、そっちのビューを見る必要がありそうだ。

その他

pg_store_plansビューへのアクセス権限

pg_store_plansビューのうち、いくつかの列は特定の権限がないと参照することはできない。
たとえば、さっきのようにuser1というロールで実行されたDMLの実行権限は参照することができるが、それ以外のロールによって実行された場合は、一部の列が0(queryid)と表示されたり、<insufficient privilege>のようにマスクされた文字列になったりする。

$ psql -U user1 testdb
psql (11.2)
Type "help" for help.

testdb=> SELECT queryid, calls, plan FROM pg_store_plans;
  queryid   | calls |                                                 plan
------------+-------+------------------------------------------------------------------------------------------------------
 1445217560 |  1000 | Update on pgbench_tellers  (cost=0.00..2.25 rows=1 width=106)                                       +
            |       |   ->  Seq Scan on pgbench_tellers  (cost=0.00..2.25 rows=1 width=106)                               +
            |       |         Filter: (tid = 38)
  796100257 |  1000 | Update on pgbench_accounts  (cost=0.42..8.45 rows=1 width=103)                                      +
            |       |   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..8.45 rows=1 width=103)+
            |       |         Index Cond: (aid = 983785)
 2767262120 |  1000 | Insert on pgbench_history  (cost=0.00..0.01 rows=1 width=56)                                        +
            |       |   ->  Result  (cost=0.00..0.01 rows=1 width=56)
  570324514 |     1 | Aggregate  (cost=1.12..1.14 rows=1 width=8)                                                         +
            |       |   ->  Seq Scan on pgbench_branches  (cost=0.00..1.10 rows=10 width=0)
 2032872830 |  1000 | Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..8.44 rows=1 width=4)        +
            |       |   Index Cond: (aid = 983785)
 1437714141 |  1000 | Update on pgbench_branches  (cost=0.00..1.13 rows=1 width=106)                                      +
            |       |   ->  Seq Scan on pgbench_branches  (cost=0.00..1.13 rows=1 width=106)                              +
            |       |         Filter: (bid = 6)
          0 |     1 | <insufficient privilege>
          0 |     1 | <insufficient privilege>
          0 |     1 | <insufficient privilege>
(9 rows)

これらの列を表示するためには、特権ロールが必要になる(PostgreSQL 10以降に導入された、pg_read_all_statsあるいはpg_monitorロールを付与して参照できるか実験してみたがダメった・・・)。

おわりに

今回は、とりあえずインストールして動かしてみるところまでやってみた。
次回は、pg_store_plansビューの他の列の内容、pg_store_plansモジュールが提供する関数、表示フォーマットの違いなど、もう少し細かい部分を調べてまとめる予定。

2
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
nuko_yokohama
ぬこ@横浜です/ にゃーん / 趣味でポスグレをやってる者だ/ 名もなく 貧しく 太ましく

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
2
Help us understand the problem. What is going on with this article?