PostgreSQLの内部構造とクエリチューニングのポイントについて図で解説

はじめに

この記事では、

クエリ実行時のPostgreSQLの内部動作イメージを図で解説し、

実行計画の読み方、

クエリチューニングやボトルネック箇所特定のポイントについて解説します。


特に、

アプリケーションエンジニアも理解しておくと役に立つ部分に絞って、

PostgreSQLの内部構造について図を多めに使ってまとめました。




PostgreSQLを普段を使ってて内部構造に興味がある方、

リリース間際に性能問題が発覚してパフォーマンスチューニングすることになった方、

データベースが好きな方に参考にして頂けると幸いです。




僕は今までデータベース関連だと以下のようなことをしてきましたが、

  • データベーススペシャリスト、Oracle Master Blonze 12cの資格取得
  • 数億規模のテーブルを持つアプリケーションのクエリチューニング(SQL Server)
  • 5TPS→100TPSほどのパフォーマンスチューニング(クエリチューニングやパラメータ設定変更など)
  • SQL Serverのデータベース本番運用
  • 現場で10年ほどテーブル設計

その辺りの経験も踏まえつつ、

今までの経験からして重要そうなところだけに絞ってまとめてみました。




PostgreSQLでクエリ実行時の内部の動き

PostgreSQLでselect文などのクエリを実行した際の内部の動きはざっくりこのようになります。

クエリ実行時の内部の動作イメージ


テーブルの統計情報を使ってプランナが実行計画を作り、

それを元にエグゼキュータがデータにアクセスして取得して結果を返します。




アプリケーションエンジニアは、

特にこの統計情報をプランナが利用して実行計画を作る。

この流れはイメージを持っておけると性能問題が発生した際、

原因の切り分けなどがしやすいと思います。




もう少しだけプランナが統計情報を利用して実行計画を作る部分について解説すると、

プランナは統計情報の以下のような情報を利用します。

  • 列のデータのばらつき(分布)、列の最大・最小値
  • 行数、行データの平均サイズ
  • インデックスサイズ


例えば行数が小さいからインデックスを利用しない方が

データにアクセスする際のコストは小さくて良いとプランナが判断して、

実行計画を作ったりします。




そのため、

プランナが正しく判断できるように統計情報の精度を保つ必要があります。

実は1,000,000行あるのに、100行しかないと統計情報で記録していると、

インデックスを利用すべきなのにインデックスを利用しないと、

プランナが判断し、最適なデータアクセスにならない場合もあります。




ただ、

統計情報の精度を上げるためには、

サンプリング数を増やす必要があり、

サンプリング数を増やすと統計情報生成にかかる負荷が高くなったり、

統計情報生成にかかる時間は長くなるのでそこにも注意が必要です。




また、統計情報が更新されるタイミングは、

自動バキュームプロセスが起動し、

テーブルの空き領域を再利用可能な状態に更新した直後に、

ANALYZEを実行するタイミングで更新されます。


自動バキュームプロセスについては以下の記事にまとめているので参考にしてください。

PostgreSQLの内部で動作しているプロセス|パフォーマンスチューニング時に役立つポイントに絞って解説



遅いクエリの実行計画を見る場合、

イメージしているインデックスが利用されない時、

実行計画の見積もり行数が実態とかけ離れていないかのチェックと、

かけ離れている場合は適切な統計情報の精度(サンプリング数)

になっているか確認するという観点を持っておくといいと思います。




これは余談ですが。。。

昔僕が駆け出しプログラマーの頃、

見積もり行数が実態とかけ離れていて最適なインデックスが利用されなかったため、

クエリのwhere句に1=1をand条件で入れて見積もり行数を実態と合わせたり、

hint句でオプティマイザに強制的にインデックスを利用させたり

そうやってクエリチューニングしているのを見かけたことがあります。




リリース間際で時間がない場合は、

統計情報のサンプリング数を更新して、

他への影響を確認する余裕もなく、

小手先だけでなんとかしているケースもありますが、

きちんと正攻法も抑えておきましょう!!




実行計画の読み方の基本とクエリチューニングのポイント解説

ここまで、

PostgreSQLでクエリを実行した時の内部の動きについて見てきました。

ここからは特にアプリケーションエンジニアが

クエリチューニングする際に知っておくべき

実行計画の読み方の基本と、

クエリチューニングする際のポイントについて解説します。




前提として以下テーブルとクエリ、実行計画を利用して解説します。

テーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE purchases (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(5,2),
    purchase_date DATE
);

 

クエリ
EXPLAIN ANALYZE
SELECT u.name, p.amount
FROM users u
JOIN purchases p ON u.id = p.user_id
WHERE p.amount >= 100
ORDER BY u.name, p.amount;

 

実行計画
Sort  (cost=1000.00..1000.00 rows=1 width=100) (actual time=5.123..5.789 rows=1000 loops=1)
  Sort Key: u.name, p.amount
  Sort Method: quicksort  Memory: 103kB
  ->  Hash Join  (cost=100.00..900.00 rows=1 width=100) (actual time=0.123..3.789 rows=1000 loops=1)
        Hash Cond: (u.id = p.user_id)
        ->  Seq Scan on users u  (cost=0.00..10.00 rows=1000 width=50) (actual time=0.009..0.789 rows=1000 loops=1)
        ->  Hash  (cost=50.00..50.00 rows=1000 width=50) (actual time=1.234..1.234 rows=1000 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 49kB
              ->  Index Scan using idx_purchases_amount on purchases p  (cost=0.00..50.00 rows=1000 width=50) (actual time=0.123..0.789 rows=1000 loops=1)
                    Index Cond: (amount >= 100.00)
Planning time: 0.123 ms
Execution time: 6.789 ms




実行計画は以下のようなイメージで読み進めていきます。

実行計画の読み方イメージ


実行計画の読み方イメージの通り、実行計画は下から読んでいきます。

下から読んでいって、各ノードの基本的なもの(Scan、Sort、Joinあたり)

を抑えればなんとなくは読めると思います!!




自分が実行計画を読む際に意識している基本的なポイントはこのあたりです。

  • Scanノードで想定通りのインデックスを想定通り利用しているか??
  • 各ノードのrows(見積もり行数)が実態と乖離していないか
  • 各ノードのMemoryの利用が想定内か??メモリが足りずディスクI/Oが発生していないか??
  • Sortノードでexternal merge Disk(外部ソート)が発生していないか??


まとめ


今回は

クエリ実行時のPostgreSQLの内部動作イメージを確認し、

具体的な実行計画の基本的な読み方とクエリチューニング時のポイントについて解説しました。




今回紹介した実行計画を読む際の基本的な観点を持ちつつ、

問題となっているスロークエリの実行計画を読み、

初めて出てきたノードは調べつつをやっていくうちに

クエリチューニングや原因切り分けができるようになると思います!!




参考になれば幸いです。

データベース関連の現場で役立つまとめ記事を作ったので、

ぜひこちらも参考にしてみてください。

現場で役立つデータベースの基礎知識まとめ


参考文献

 

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください