🔖
実行計画を可視化する「pev2」を使ってパフォーマンスチューニングを始めてみよう！
はじめに
インデックスはクエリ性能を改善する手段の1つですが、その効果は実行計画で確認する必要があります。ただし、複数テーブルのJOINやパーティションが絡むクエリでは、実行計画を読み解くコストが高くなりがちです。
そこで、本記事では Postgres Explain Visualizer 2 (以降「pev2」)を用いてSQLの実行計画を可視化する方法を簡潔に紹介します。
また、動作確認済みのソースコードは以下に配置しています。
ぜひお手元で動かしながら読み進めていただければと思います！
pev2 について
pev2は、PostgreSQLの EXPLAIN 結果をグラフィカルに可視化するオープンソースツールです。
Dalibo社がホスティングしている実行計画可視化サービス「explain.dalibo.com」の中身であり、PostgreSQLライセンスで提供されています。
pev2の特徴は以下の通りです(本記事と関連する内容を抜粋)。
- ツリー構造でのクエリの表示
- 実行計画の各ノード（Seq Scan / Index Scan など）がツリー構造で表示され、クエリの処理の流れを直感的に把握できる
- Web版とローカル完結版を利用可能
ブラウザ版と、「pev2.html」単体で動作するローカル版が提供されている- 本記事ではローカル版の使用方法を紹介
次節以降、pev2を用いて実際に実行計画を読み解き、インデックスを用いてクエリ性能を改善していきます。
導入方法
pev2のGitHubページ からHTMLファイル(pev2.html)をダウンロードし開きます。
次節以降で実行したEXPLAINの結果を貼り付けSubmitすることで、実行計画を可視化します。
では、次節にてpev2を用いて、実際に実行計画を可視化していきます。
前提条件
本記事の内容は以下のソフトウェアスタックで動作することを確認しています。
Dockerエンジン (Docker Desktopなど)は事前にインストールください。
- macOS（Windowsの場合はWSL2）
- Docker Desktop
また、検証端末は以下を使用しています。
- M3 MacBook Pro (メモリ: 32GB)
検証手順
1. 検証環境の構築
1.1. 検証環境のディレクトリ構成
「1. 検証環境の構築」では、以下のディレクトリ構成に従います。
.
├── compose.yml
└── docker/
└── postgres/
└── initdb/
└── 01-DDL-init.sql
└── 02-DML-init.sql
2. 検証環境構築の作業手順
2-1. PostgreSQLコンテナ用のcompose.ymlを作成
以下のcompose.ymlを作成します。
compose.yml(展開してください)
compose.yml
services:
db:
image: postgres:17.7
container_name: postgres17.7
environment:
POSTGRES_PASSWORD: apppass
POSTGRES_DB: appdb
TZ: Asia/Tokyo
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
- ./docker/postgres/initdb:/docker-entrypoint-initdb.d
volumes:
pgdata:
2-2. DDLの作成
01-DDL-init.sqlとして以下のSQLを作成してください。
※検証用のテーブルであるため、簡易的なテーブル構造としています。
01-DDL-init.sql(展開してください)
01-DDL-init.sql
-- 書籍マスタ
CREATE TABLE book (
book_id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
category TEXT
);
-- 会員マスタ
CREATE TABLE member (
member_id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL
);
-- 貸出履歴
-- ※あえて、インデックスは主キーのみとする
CREATE TABLE rental (
rental_id BIGSERIAL PRIMARY KEY,
book_id BIGINT NOT NULL REFERENCES book(book_id),
member_id BIGINT NOT NULL REFERENCES member(member_id),
rented_at TIMESTAMPTZ NOT NULL -- 貸出日時
);
2-3. DMLの作成
02-DML-init.sqlとして以下のSQLを作成してください。
大量データをINSERTするDMLを愚直に記載すると長くなるため、プロシージャとして作成しました。
実際に性能検証する際は、実態に即したデータをINSERTください。
02-DML-init.sql(展開してください)
02-DML-init.sql
CREATE OR REPLACE PROCEDURE seed_book_rental_data(
IN p_member_count INT DEFAULT 10000, -- 会員数（例: 1万）
IN p_book_count INT DEFAULT 1000, -- 書籍数（例: 1000冊）
IN p_months INT DEFAULT 24 -- データを作る月数（例: 24ヶ月＝2年）
)
LANGUAGE plpgsql
AS $$
DECLARE
v_start_month DATE := (
date_trunc('month', CURRENT_DATE)
- make_interval(months := p_months)
)::date;
v_month DATE;
v_rentals_per_month INT;
BEGIN
-- 書籍データ投入（p_book_count 冊）
INSERT INTO book (title, category)
SELECT
format('Book %s', gs) AS title,
CASE ((gs - 1) % 5)
WHEN 0 THEN 'Business'
WHEN 1 THEN 'Technology'
WHEN 2 THEN 'Novel'
WHEN 3 THEN 'Comic'
ELSE 'Others'
END AS category
FROM generate_series(1, p_book_count) AS gs;
-- 会員データ投入（p_member_count 人）
INSERT INTO member (name)
SELECT format('Member %s', gs) AS name
FROM generate_series(1, p_member_count) AS gs;
-- 貸出履歴データ投入
v_month := v_start_month;
v_rentals_per_month := (p_member_count / 2) * 8;
WHILE v_month < date_trunc('month', CURRENT_DATE)::date LOOP
INSERT INTO rental (book_id, member_id, rented_at)
SELECT
(1 + floor(random() * p_book_count))::bigint AS book_id,
(1 + floor(random() * p_member_count))::bigint AS member_id,
v_month
+ (floor(random() * 28))::int
+ make_interval(hours := floor(random() * 24)::int) AS rented_at
FROM generate_series(1, v_rentals_per_month) AS gs;
v_month := (v_month + INTERVAL '1 month')::date;
END LOOP;
END;
$$;
CALL seed_book_rental_data();
3. 実際に pev2 を用いてEXPLAIN結果を可視化する
3-1. コンテナを起動し、データベースに接続する
2の作業を実施後、以下のコマンドでPostgreSQLコンテナを起動ください。
docker compose up -d
今回は簡易的にPostgreSQLのクライアントは用いず、以下のコマンドを順に実行することで、作成したコンテナのコンテナIDを用いてコンテナに入り、PostgreSQLに接続します。
# 1. コンテナIDの取得
docker ps | grep postgres17.7
# 2. コンテナに接続
docker exec -it {コンテナID} /bin/bash
# 3. PostgreSQLコンテナ内のpsqlクライアントを用いてDBに接続
psql -h localhost -p 5432 -U postgres -d appdb
3-2. INSERTしたデータの確認
PostgreSQLコンテナ起動時の初期処理として、プロシージャで3テーブルにデータをINSERTしました。
最もデータ量が多い rental テーブルは、以下のSQLでレコード数を確認しておきます。
SELECT relname, reltuples FROM pg_class WHERE relname = 'rental';
relname | reltuples
---------+-----------
rental | 960000
(1 row)
[気になる方向け]SELECT COUNT(*) from rental;で直接テーブルをSELECTしない理由(展開してください)
3-3. 統計情報を最新化する
プランナはSQLをどのように実行するか決める仕組みです。具体的には、統計情報に基づき、どのインデックスを使用するかなどを決定します。
ここではプランナについて深く言及しませんが、PostgreSQLユーザー会が公開している以下の動画が分かりやすいので、ぜひご覧ください。
INSERT直後は統計情報が最新化されていないため、ANALYZEコマンドで更新します。
ANALYZE book;
ANALYZE member;
ANALYZE rental;
3-4. EXPLAINを実行する
EXPLAIN結果を見やすくするために、以下のコマンドを実行し、ページングの設定をオフにします。
\pset pager off
以下のコマンドを用いて、EXPLAINを実行ください。
EXPLAIN (ANALYZE, VERBOSE, WAL, BUFFERS, SETTINGS, MEMORY ) SELECT
b.book_id,
b.title,
COUNT(*) AS rental_count
FROM
rental r
JOIN book b ON b.book_id = r.book_id
WHERE
r.rented_at >= date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
AND r.rented_at < date_trunc('month', CURRENT_DATE)
GROUP BY
b.book_id,
b.title
ORDER BY
rental_count DESC,
b.book_id
LIMIT 10;
私の環境の場合、以下の実行計画が得られました。
実行計画(展開してください)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20548.16..20548.18 rows=10 width=24) (actual time=155.326..159.853 rows=10 loops=1)
Output: b.book_id, b.title, (count(*))
Buffers: shared hit=220 read=6882
-> Sort (cost=20548.16..20550.66 rows=1000 width=24) (actual time=155.323..159.849 rows=10 loops=1)
Output: b.book_id, b.title, (count(*))
Sort Key: (count(*)) DESC, b.book_id
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=220 read=6882
-> Finalize GroupAggregate (cost=20273.20..20526.55 rows=1000 width=24) (actual time=153.932..159.518 rows=1000 loops=1)
Output: b.book_id, b.title, count(*)
Group Key: b.book_id
Buffers: shared hit=220 read=6882
-> Gather Merge (cost=20273.20..20506.55 rows=2000 width=24) (actual time=153.926..158.944 rows=3000 loops=1)
Output: b.book_id, b.title, (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=220 read=6882
-> Sort (cost=19273.18..19275.68 rows=1000 width=24) (actual time=140.572..140.632 rows=1000 loops=3)
Output: b.book_id, b.title, (PARTIAL count(*))
Sort Key: b.book_id
Sort Method: quicksort Memory: 71kB
Buffers: shared hit=220 read=6882
Worker 0: actual time=134.915..134.967 rows=1000 loops=1
Sort Method: quicksort Memory: 71kB
Buffers: shared hit=74 read=2287
Worker 1: actual time=134.952..135.005 rows=1000 loops=1
Sort Method: quicksort Memory: 71kB
Buffers: shared hit=79 read=2223
-> Partial HashAggregate (cost=19213.35..19223.35 rows=1000 width=24) (actual time=140.070..140.207 rows=1000 loops=3)
Output: b.book_id, b.title, PARTIAL count(*)
Group Key: b.book_id
Batches: 1 Memory Usage: 193kB
Buffers: shared hit=204 read=6882
Worker 0: actual time=134.186..134.318 rows=1000 loops=1
Batches: 1 Memory Usage: 193kB
Buffers: shared hit=66 read=2287
Worker 1: actual time=134.554..134.702 rows=1000 loops=1
Batches: 1 Memory Usage: 193kB
Buffers: shared hit=71 read=2223
-> Hash Join (cost=29.50..19131.60 rows=16350 width=16) (actual time=125.009..137.101 rows=13333 loops=3)
Output: b.book_id, b.title
Inner Unique: true
Hash Cond: (r.book_id = b.book_id)
Buffers: shared hit=204 read=6882
Worker 0: actual time=119.123..131.205 rows=12920 loops=1
Buffers: shared hit=66 read=2287
Worker 1: actual time=119.539..131.380 rows=13600 loops=1
Buffers: shared hit=71 read=2223
-> Parallel Seq Scan on public.rental r (cost=0.00..19059.00 rows=16350 width=8) (actual time=124.515..133.208 rows=13333 loops=3)
Output: r.rental_id, r.book_id, r.member_id, r.rented_at
Filter: ((r.rented_at >= date_trunc('month'::text, (CURRENT_DATE - '1 mon'::interval))) AND (r.rented_at < date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone)))
Rows Removed by Filter: 306667
Buffers: shared hit=192 read=6867
Worker 0: actual time=118.642..127.372 rows=12920 loops=1
Buffers: shared hit=61 read=2282
Worker 1: actual time=119.028..127.661 rows=13600 loops=1
Buffers: shared hit=66 read=2218
-> Hash (cost=17.00..17.00 rows=1000 width=16) (actual time=0.439..0.439 rows=1000 loops=3)
Output: b.book_id, b.title
Buckets: 1024 Batches: 1 Memory Usage: 56kB
Buffers: shared hit=6 read=15
Worker 0: actual time=0.420..0.420 rows=1000 loops=1
Buffers: shared hit=2 read=5
Worker 1: actual time=0.446..0.446 rows=1000 loops=1
Buffers: shared hit=2 read=5
-> Seq Scan on public.book b (cost=0.00..17.00 rows=1000 width=16) (actual time=0.027..0.268 rows=1000 loops=3)
Output: b.book_id, b.title
Buffers: shared hit=6 read=15
Worker 0: actual time=0.026..0.259 rows=1000 loops=1
Buffers: shared hit=2 read=5
Worker 1: actual time=0.041..0.284 rows=1000 loops=1
Buffers: shared hit=2 read=5
Planning:
Buffers: shared hit=115 read=10 dirtied=1
Memory: used=52kB allocated=64kB
Planning Time: 3.333 ms
Execution Time: 160.063 ms
(77 rows)
次節でこの結果を可視化します。
3-5. EXPLAIN結果を pev2 で可視化し考察する。
以下のキャプチャのように結果を貼り付け、Submitすることで可視化されます。
また、結果は名前をつけることが可能です。
pev2の設定画面
可視化結果は以下の通りです。
線の太さは、取得したレコード量を模しています。
可視化された実行計画の全体像
また、トグルを展開することで詳細も確認可能です。
可視化された実行計画の詳細
可視化された実行計画を見ると、rental テーブルに対するクエリのコストが高いことが分かります( $ マークが赤色になっている)。
上記の原因は、当初 rental テーブルに作成したインデックスがPKのみであるためです(※ユニーク制約によるユニークインデックスは付与しておらず、テーブル個別にインデックスも作成していない)。
具体的には、以下のJOINとWHERE句において、 rentalテーブルの rented_at, book_id に対して、インデックスが作成されていないため、Seq Scan(厳密には Parallel Seq Scan)になっています。
そこで、先ほど作成した 01-DDL-init.sql 末尾に以下のインデックスを追加し改善を試みます。
01-DDL-init.sql
~CREATE TABLEは省略~
+ -- パフォーマンス改善用インデックス1
+ CREATE INDEX idx_rental_1
+ ON rental (rented_at, book_id);
既存のコンテナを停止しDDLを変更後、再度コンテナを立ち上げ同様にEXPLAINを実行します。
※docker compose down -vでボリュームの削除も実施しないとDDLが流れないので注意
結果を確認すると以下の通りでした。
実行計画(展開してください)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1929.84..1929.86 rows=10 width=24) (actual time=15.611..15.612 rows=10 loops=1)
Output: b.book_id, b.title, (count(*))
Buffers: shared hit=14 read=199
-> Sort (cost=1929.84..1932.34 rows=1000 width=24) (actual time=15.610..15.611 rows=10 loops=1)
Output: b.book_id, b.title, (count(*))
Sort Key: (count(*)) DESC, b.book_id
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=14 read=199
-> HashAggregate (cost=1898.23..1908.23 rows=1000 width=24) (actual time=15.433..15.505 rows=1000 loops=1)
Output: b.book_id, b.title, count(*)
Group Key: b.book_id
Batches: 1 Memory Usage: 193kB
Buffers: shared hit=14 read=199
-> Hash Join (cost=29.94..1703.50 rows=38945 width=16) (actual time=0.481..10.893 rows=40000 loops=1)
Output: b.book_id, b.title
Inner Unique: true
Hash Cond: (r.book_id = b.book_id)
Buffers: shared hit=14 read=199
-> Index Only Scan using idx_rental_1 on public.rental r (cost=0.44..1571.34 rows=38945 width=8) (actual time=0.081..5.615 rows=40000 loops=1)
Output: r.rented_at, r.book_id
Index Cond: ((r.rented_at >= date_trunc('month'::text, (CURRENT_DATE - '1 mon'::interval))) AND (r.rented_at < date_trunc('month'::text, (CURRENT_DATE)::timestamp with time zone)))
Heap Fetches: 0
Buffers: shared hit=7 read=199
-> Hash (cost=17.00..17.00 rows=1000 width=16) (actual time=0.392..0.392 rows=1000 loops=1)
Output: b.book_id, b.title
Buckets: 1024 Batches: 1 Memory Usage: 56kB
Buffers: shared hit=7
-> Seq Scan on public.book b (cost=0.00..17.00 rows=1000 width=16) (actual time=0.009..0.169 rows=1000 loops=1)
Output: b.book_id, b.title
Buffers: shared hit=7
Planning:
Buffers: shared hit=85 read=11 dirtied=1
Memory: used=58kB allocated=128kB
Planning Time: 1.289 ms
Execution Time: 15.821 ms
(35 rows)
可視化した結果は以下のキャプチャの通りです。
改善された実行計画の詳細
rental テーブルに対する読み取りはIndex Only Scanになり、実行時間、コストをそれぞれ以下の通り低減することができました。
(※今回 book テーブルは1000レコードしか入っておらずコストも低かったため、インデックスは付与しなかった)
| 項目 | インデックス付与前 | インデックス付与後 |
|---|---|---|
| Execution Time | 68.2ms | 1.96.8ms |
| Planning time | 3.33ms | 1.29ms |
| rental のスキャン方式 | Parallel Seq Scan | Index Only Scan |
まとめ
pev2 を用いることで、実行計画のボトルネックを視覚的に把握できることを説明してきました。
実行計画の細かな分析では、実行計画を直接読むことは不可欠です。その前段となる全体像の理解に pev2 は役立つと思います。
是非複雑なクエリの性能改善に、pev2を活用してみてください。
ここまで読んでいただき、ありがとうございました！
Discussion