この記事を読むと理解できること
- クエリ実行計画の概要が理解できる
- クエリ実行計画の必要性が理解できる
- クエリ実行計画の見方が理解できる
- クエリ実行計画から、実際のクエリ改善の方法が理解できる
DB設計
- ユーザーとプロファイル (One-to-One)
users テーブル: ユーザー情報
profiles テーブル: 各ユーザーのプロファイル情報 - 商品とカテゴリー (Many-to-Many)
products テーブル: 商品情報
categories テーブル: カテゴリー情報
product_categories 交差テーブル: 商品とカテゴリーの多対多のリレーション - 注文とユーザー (Many-to-One)
orders テーブル: 注文情報
order_items テーブル: 各注文に関連する商品情報
事前準備
実際に手元で動作させたい場合は以下のテーブルとテストデータの投入を行うこと。※不要な場合は、「実行計画とは」のセクションまで飛ばすこと
データベースの作成
dockerコンテナを用意、docker compose up -dで起動
version: "3"
services:
db:
image: postgres:14
container_name: postgres
ports:
- 5432:5432
volumes:
- store:/var/lib/postgresql/data
environment:
POSTGRES_USER: "user"
POSTGRES_PASSWORD: "password"
volumes:
store:DBを作成
CREATE DATABASE sampleテーブル作成
-- users_pkeyのみindex
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
-- profiles_pkey, profiles_user_id_keyにindex
CREATE TABLE profiles (
id SERIAL PRIMARY KEY,
user_id INTEGER UNIQUE NOT NULL,
address TEXT,
phone_number VARCHAR(15),
FOREIGN KEY (user_id) REFERENCES users (id)
);
-- products_pkeyのみindex
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
-- categories_pkeyのみindex
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- product_categories_pkeyのみindex(product_id,category_idで複合index)
CREATE TABLE product_categories (
product_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES products (id),
FOREIGN KEY (category_id) REFERENCES categories (id)
);
-- orders_pkeyのみindex
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
order_date TIMESTAMP NOT NULL,
status VARCHAR(50) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id)
);
-- order_items_pkeyのみindex
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (id),
FOREIGN KEY (product_id) REFERENCES products (id)
);
-- order_id, product_idにindexを追加
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);サンプルデータ投入
各テーブルに10万件ずつレコードを挿入
users
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO users (username) VALUES (concat('user', i));
END LOOP;
END $$;profiles
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO profiles (user_id, address, phone_number) VALUES (i, concat('Address for user', i), concat('123-456-', i));
END LOOP;
END $$;products
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO products (name, price) VALUES (concat('product', i), (random() * 100)::DECIMAL(10, 2));
END LOOP;
END $$;categories
DO $$
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO categories (name) VALUES (concat('category', i));
END LOOP;
END $$;product_categories
交差テーブル
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO product_categories (product_id, category_id) VALUES (i, (i % 100) + 1);
END LOOP;
END $$;orders
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO orders (user_id, order_date, status) VALUES (i, NOW(), 'pending');
END LOOP;
END $$;order_items
DO $$
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (i, (i % 100000) + 1, (random() * 10)::INTEGER + 1, (random() * 100)::DECIMAL(10, 2));
END LOOP;
END $$;実行計画とは
- 実行計画は、SQLを実行するためにRDB が選択した手順を確認するもの
- テーブルをフルキャンしたのか、インデックスを使用したのか、結合時にはどのような条件を使用したのかなどを知ることができる
実行計画の表示方法
SQLの前に```EXPLAIN````とつけるだけで表示できる
EXPLAIN SELECT * FROM usersSeq Scan on users (cost=0.00..1541.00 rows=100000 width=13)ANALYZEオプションをつけると、EXPLAINコマンドでクエリの実行も行い、実行結果の統計情報を表示する
Seq Scan on users (cost=0.00..1541.00 rows=100000 width=13) (actual time=0.025..10.864 rows=100000 loops=1)
Planning Time: 0.145 ms
Execution Time: 16.458 msJoinやソートを含むクエリの実行計画の場合かなり複雑になる。たいてい、上からソート→結合系→スキャン系になる（絞り込み・結合・集計・ソート）
Sort (cost=5703.07..5703.07 rows=1 width=39) (actual time=54.669..54.674 rows=1 loops=1)
Sort Key: o.order_date DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=3690.96..5703.06 rows=1 width=39) (actual time=39.079..54.658 rows=1 loops=1)
-> Hash Join (cost=3690.54..5702.55 rows=1 width=31) (actual time=39.040..54.618 rows=1 loops=1)
Hash Cond: (oi.order_id = o.id)
-> Seq Scan on order_items oi (cost=0.00..1637.00 rows=100000 width=18) (actual time=0.016..6.634 rows=100000 loops=1)
-> Hash (cost=3690.52..3690.52 rows=1 width=21) (actual time=38.987..38.990 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1791.01..3690.52 rows=1 width=21) (actual time=21.448..38.981 rows=1 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..1637.00 rows=100000 width=16) (actual time=0.007..7.383 rows=100000 loops=1)
-> Hash (cost=1791.00..1791.00 rows=1 width=13) (actual time=21.403..21.404 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on users u (cost=0.00..1791.00 rows=1 width=13) (actual time=0.026..21.397 rows=1 loops=1)
Filter: ((username)::text = 'user95'::text)
Rows Removed by Filter: 99999
-> Index Scan using products_pkey on products p (cost=0.42..0.51 rows=1 width=16) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: (id = oi.product_id)
Planning Time: 2.592 ms
Execution Time: 54.743 ms実行計画を見るときのポイント
表示される項目が多いので、ポイントだけ抜粋する
スキャン演算子
-> Index Scan using products_pkey on products pSeq Scan シーケンシャルスキャン
- フルスキャンしていることを表す
- 大きなデータを持つテーブルで無駄なseq scanが生じていないか注意が必要
Index Scan（=Bツリーインデックス）
- インデックスを使用してスキャン
- カラムのカーディナリティが高い場合に有効（ usersテーブルのusernameカラムなど
- Index only scanは、クエリで取得するカラムがインデックスが存在するカラムのみで完結する場合のスキャン
Bitmap scan: ビットマップを使用してスキャン
- カラムのカーディナリティが低い場合に有効（性別カラムなど
- SQL実行時にワーキングメモリ内にビットマップ（0, 1)を作成して検索速度を向上させる
- インデックス作成時には指定することはできない
他にもあるが、頻度が低いので割愛
結合演算子
-> Nested Loop (cost=2.68..2229.39 ・・・駆動表は結合操作のループの外側に位置、内部表は結合操作のループの内側
Hash Join
- 一方のテーブルから作られたハッシュ表を元に結合。メモリーの容量に余裕がある場合に選択されやすい
- ハッシュを作成する分のオーバーヘッドが発生するものの、その後は高速に結合（Merge Joinと異なり、ソートのコストは不要
- 駆動表はハッシュを作成してメモリ上に収まる小さなテーブルで、内部表が大きなテーブルの場合に有効
Nested Loop
- 片方のテーブルから結果を取り出し、その結果をもう1つのテーブルの各行に対して問い合わせて、2つのテーブルを結合。データ量が少ない場合に良い
- 駆動表の1行ごとに内部表を全て走査して評価
- 駆動表の行数が少なく、内部表にインデックスを張っている場合には高速。逆に駆動表の行数が多いほどループの回数が増え効率が悪くなる
Merge Join
- ソートされた両方のテーブルをマージすることで結合を実現する
- 2つのテーブルをそれぞれ結合キーでソートしてから、順番に付き合わせて評価
- 大きなテーブル同士の結合で、ハッシュがメモリに収まらないような場合に特に有効
ソート演算子
Sort (cost=2279.22..2281.72 rows=1000 width=22) (actual time=24.799..24.865 rows=1000 loops=1)
Sort Key: p.price DESC
Sort Method: quicksort Memory: 87kB- ORDER BYやMerge Joinが選択された際にソートが行われる
- Sort Methodで「quicksort（メモリー上のソート）」となっていれば、メモリーで高速に処理されている
cost
-> Hash Join (cost=2.26..1718.89- 検索結果の1行目を返すまでにかかる準備のコスト...初期コストを含めた処理完了までにかかるコスト
- コストは秒数ではない
- シーケンシャルスキャンは事前準備が不要なので初期コストは0、インデックススキャンは事前にインデックスを検索する必要があり初期コストがかかる
- 他のノードと比較して、極端にコストが高い箇所は改善の余地あり
rows
Sort (cost=2279.22..2281.72 rows=1000- 処理対象のレコード数
- 実際のレコード数と大きく異なる場合、デッドタプルの可能性が高い
- デッドタプルが増えると、ディスクスペースの無駄になるだけでなく、テーブルのスキャン速度を低下させる原因になる。デッドタプルをクリーンアップするためのVACUUMについては記事の後半で紹介
actual time
Index Scan using products_pkey on products p (cost=0.42..0.51 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=1000)- クエリの実行にかかった時間
- 1件目のデータを取得するまでにかかった時間(ミリ秒)..全てのデータを取得するまでにかかった時間
実行計画からクエリ改善
- 一般的に、絞り込み（where）を行っているカラム・結合（on）を行っているカラムでインデックスの追加を検討するといい
- 以下は特定のユーザーが購入した商品の一覧を表示するためのクエリ。このクエリは、users、orders、order_items、および products テーブルを結合して、特定のユーザーが購入した商品の情報を取得
SELECT
u.id AS user_id,
u.username,
p.address,
p.phone_number,
o.id AS order_id,
o.order_date,
o.status,
oi.id AS order_item_id,
oi.quantity,
oi.price AS order_item_price,
pr.id AS product_id,
pr.name AS product_name,
pr.price AS product_price,
c.id AS category_id,
c.name AS category_name
FROM
users u
JOIN
profiles p ON u.id = p.user_id
JOIN
orders o ON u.id = o.user_id
JOIN
order_items oi ON o.id = oi.order_id
JOIN
products pr ON oi.product_id = pr.id
JOIN
product_categories pc ON pr.id = pc.product_id
JOIN
categories c ON pc.category_id = c.id
WHERE
u.username = 'user57'
ORDER BY
o.order_date DESC,
pr.name ASC;
-- 結果
57 user57 Address for user57 123-456-57 57 2024-11-05 02:11:17.274628 pending 57 2 34.69 58 product58 53.07 59 category59現時点では、ordersとusersをuser.idで結合するためのordersのスキャン時、usesのスキャン時にシーケンシャルスキャンが使われてしまっている箇所が2つほどある。
Sort (cost=3692.10..3692.10 rows=1 width=117) (actual time=31.921..31.925 rows=1 loops=1)
Sort Key: o.order_date DESC, pr.name
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=1792.33..3692.09 rows=1 width=117) (actual time=13.159..31.917 rows=1 loops=1)
-> Nested Loop (cost=1792.18..3691.93 rows=1 width=107) (actual time=13.154..31.912 rows=1 loops=1)
-> Nested Loop (cost=1791.89..3691.60 rows=1 width=107) (actual time=13.148..31.904 rows=1 loops=1)
-> Nested Loop (cost=1791.60..3691.25 rows=1 width=85) (actual time=13.141..31.896 rows=1 loops=1)
-> Nested Loop (cost=1791.31..3690.89 rows=1 width=67) (actual time=13.134..31.889 rows=1 loops=1)
Join Filter: (u.id = p.user_id)
-> Hash Join (cost=1791.01..3690.52 rows=1 width=37) (actual time=13.118..31.871 rows=1 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..1637.00 rows=100000 width=24) (actual time=0.006..7.514 rows=100000 loops=1)
-> Hash (cost=1791.00..1791.00 rows=1 width=13) (actual time=13.094..13.095 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on users u (cost=0.00..1791.00 rows=1 width=13) (actual time=0.020..13.092 rows=1 loops=1)
Filter: ((username)::text = 'user57'::text)
Rows Removed by Filter: 99999
-> Index Scan using profiles_user_id_key on profiles p (cost=0.29..0.36 rows=1 width=38) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (user_id = o.user_id)
-> Index Scan using idx_order_items_order_id on order_items oi (cost=0.29..0.35 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (order_id = o.id)
-> Index Scan using products_pkey on products pr (cost=0.29..0.35 rows=1 width=22) (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (id = oi.product_id)
-> Index Only Scan using product_categories_pkey on product_categories pc (cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)
Index Cond: (product_id = oi.product_id)
Heap Fetches: 0
-> Index Scan using categories_pkey on categories c (cost=0.14..0.16 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = pc.category_id)
Planning Time: 1.785 ms
Execution Time: 31.973 msordersとusersの結合に使用されているorders.user_idにインデックスを付与し、実行。この状態だと全体の実行時間が3分の1くらいに改善されていることがわかる
CREATE INDEX idx_orders_user_id ON orders(user_id);Execution Time: 31.973 ms → Execution Time: 7.866 ms
Sort (cost=1803.96..1803.97 rows=1 width=117) (actual time=7.465..7.469 rows=1 loops=1)
Sort Key: o.order_date DESC, pr.name
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=1.46..1803.95 rows=1 width=117) (actual time=0.060..7.457 rows=1 loops=1)
Join Filter: (pc.category_id = c.id)
Rows Removed by Join Filter: 58
-> Nested Loop (cost=1.46..1800.70 rows=1 width=107) (actual time=0.050..7.446 rows=1 loops=1)
-> Nested Loop (cost=1.17..1800.37 rows=1 width=107) (actual time=0.046..7.441 rows=1 loops=1)
-> Nested Loop (cost=0.88..1800.03 rows=1 width=85) (actual time=0.042..7.436 rows=1 loops=1)
-> Nested Loop (cost=0.58..1799.67 rows=1 width=67) (actual time=0.036..7.430 rows=1 loops=1)
Join Filter: (u.id = o.user_id)
-> Nested Loop (cost=0.29..1799.31 rows=1 width=51) (actual time=0.032..7.425 rows=1 loops=1)
-> Seq Scan on users u (cost=0.00..1791.00 rows=1 width=13) (actual time=0.020..7.411 rows=1 loops=1)
Filter: ((username)::text = 'user57'::text)
Rows Removed by Filter: 99999
-> Index Scan using profiles_user_id_key on profiles p (cost=0.29..8.31 rows=1 width=38) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (user_id = u.id)
-> Index Scan using user_id_1730690854884_index on orders o (cost=0.29..0.35 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (user_id = p.user_id)
-> Index Scan using idx_order_items_order_id on order_items oi (cost=0.29..0.35 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (order_id = o.id)
-> Index Scan using products_pkey on products pr (cost=0.29..0.35 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = oi.product_id)
-> Index Only Scan using product_categories_pkey on product_categories pc (cost=0.29..0.32 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
Index Cond: (product_id = oi.product_id)
Heap Fetches: 0
-> Seq Scan on categories c (cost=0.00..2.00 rows=100 width=14) (actual time=0.003..0.006 rows=59 loops=1)
Planning Time: 2.255 ms
Execution Time: 7.528 msまた、シーケンシャルスキャンではなく、インデックススキャンが使用されるようになったことがわかる
> Index Scan using idx_orders_user_id on orders o (cost=0.29..0.35 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=1)さらに、where句に指定されているusers.usernameにインデックスを追加すると、実行時間がさらに半分程度に改善されたことがわかる
CREATE INDEX idx_users_username ON users(username);Execution Time: 7.866 ms → Execution Time: 0.102 ms
Sort (cost=18.31..18.31 rows=1 width=117) (actual time=0.052..0.053 rows=1 loops=1)
Sort Key: o.order_date DESC, pr.name
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=2.02..18.30 rows=1 width=117) (actual time=0.042..0.045 rows=1 loops=1)
-> Nested Loop (cost=1.88..18.14 rows=1 width=107) (actual time=0.039..0.042 rows=1 loops=1)
-> Nested Loop (cost=1.59..17.81 rows=1 width=107) (actual time=0.035..0.037 rows=1 loops=1)
-> Nested Loop (cost=1.29..17.46 rows=1 width=85) (actual time=0.031..0.033 rows=1 loops=1)
-> Nested Loop (cost=1.00..17.10 rows=1 width=67) (actual time=0.028..0.029 rows=1 loops=1)
Join Filter: (u.id = o.user_id)
-> Nested Loop (cost=0.71..16.75 rows=1 width=51) (actual time=0.022..0.023 rows=1 loops=1)
-> Index Scan using idx_users_username on users u (cost=0.42..8.44 rows=1 width=13) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: ((username)::text = 'user57'::text)
-> Index Scan using profiles_user_id_key on profiles p (cost=0.29..8.31 rows=1 width=38) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (user_id = u.id)
-> Index Scan using idx_orders_user_id on orders o (cost=0.29..0.35 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (user_id = p.user_id)
-> Index Scan using idx_order_items_order_id on order_items oi (cost=0.29..0.35 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (order_id = o.id)
-> Index Scan using products_pkey on products pr (cost=0.29..0.35 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = oi.product_id)
-> Index Only Scan using product_categories_pkey on product_categories pc (cost=0.29..0.32 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
Index Cond: (product_id = oi.product_id)
Heap Fetches: 0
-> Index Scan using categories_pkey on categories c (cost=0.14..0.16 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = pc.category_id)
Planning Time: 5.698 ms
Execution Time: 0.102 msまた、シーケンシャルスキャンではなく、インデックススキャンが使用されるようになったことがわかる
-> Index Scan using idx_users_username on users u (cost=0.42..8.44 rows=1 width=13) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: ((username)::text = 'user57'::text)統計情報の更新
前述の通り、デッドタプルの存在は、ディスクスペースの無駄になるだけでなく、テーブルのスキャン速度を低下させる原因になるため、以下のように統計情報を作成し直す必要がある
VACUUMコマンドを実行してデッドタプルを削除し、そのスペースを再利用可能にすることができる
VACUUM table_name;デッドタプル削除後、ANALYZEで統計情報を再生成することが可能
ANALYZE <schema_name>.<table_name>AUTOVACUUM
データベースが自動的に定期的にVACUUMとANALYZEを実行する機能。以下でautovacuumが有効になっているか確認できる
SELECT name, setting FROM pg_settings;まとめ
いかがでしたでしょうか。本記事では、SQLのパフォーマンスチューニングをするために欠かせないPostgreSQLのクエリ実行計画の見方と、具体的なパフォーマンス改善の実践方法について紹介しています。SQLのチューニングには実行計画を読み取れるかがポイントになりますので、ぜひ参考にしてみてください。