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

はじめに

この記事では、

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

実行計画の読み方、

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

特に、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

そのため、

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

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

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

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

ただ、

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

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

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

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

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

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

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

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

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

2023年5月19日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

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