PostgreSQLでSQLクエリを評価

目次
1. はじめに
2. テーブルの作成
3. データの挿入
4. SQLクエリの評価
6. EXPLAINの実行結果の解釈
7. まとめ

1. はじめに

この記事では、PostgreSQLで実際にテーブルを作成し、データを挿入して、SQLクエリの評価を行います。
これにより、実行計画の見方の基本を学びましょう。

2. テーブルの作成

まず、PostgreSQLで使用するテーブルを作成します。今回は簡単な注文管理システムを例にします。
以下のSQLを実行して、customersテーブルとordersテーブルを作成します。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

3. データの挿入

次に、これらのテーブルにデータを挿入します。

INSERT INTO customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie');

INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2024-01-01', 100.00),
(2, '2024-01-02', 200.00),
(3, '2024-01-03', 300.00),
(1, '2024-01-04', 150.00),
(2, '2024-01-05', 250.00);

4. SQLクエリの評価

ここで、SQLを実行して評価します。
以下は特定の顧客の注文を取得するクエリです。

SELECT * FROM orders WHERE customer_id = 1;

EXPLAINコマンドの使用方法
次に、このクエリの実行計画を確認するために、EXPLAINコマンドを使用します。

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

このコマンドを実行すると、PostgreSQLはクエリを実行するための計画を表示します。
さらに詳細な情報を得るためには、EXPLAIN ANALYZEを使用します。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;

6. EXPLAINの実行結果の解釈

EXPLAINコマンドの実行結果は以下のようになります。

Seq Scan on orders  (cost=0.00..12.75 rows=3 width=24)
  Filter: (customer_id = 1)

この結果の各部分の意味は次の通りです：

Seq Scan on orders: このクエリはordersテーブルに対してシーケンシャルスキャンを行っています。つまり、テーブル全体を順番にスキャンしています。
cost=0.00..12.75: このクエリの実行には、スタートアップコスト0.00、合計コスト12.75がかかります。
rows=3: このクエリは3行の結果を返すと予測されています。
width=24: 各行の平均サイズは24バイトです。
Filter: (customer_id = 1): このフィルター条件に一致する行だけが返されます。

次に、EXPLAIN ANALYZEを使用した場合の結果は以下のようになります。

Seq Scan on orders  (cost=0.00..12.75 rows=3 width=24) (actual time=0.012..0.013 rows=2 loops=1)
  Filter: (customer_id = 1)
  Rows Removed by Filter: 3
Planning Time: 0.078 ms
Execution Time: 0.045 ms

EXPLAIN ANALYZEの結果には、以下の追加情報が含まれています：

actual time=0.012..0.013: 実際のスキャンの開始から終了までの時間（ミリ秒）
rows=2: 実際に返された行数
loops=1: このスキャンが実行された回数
Rows Removed by Filter: 3: フィルター条件によって除外された行数
Planning Time: 0.078 ms: クエリの計画にかかった時間
Execution Time: 0.045 ms: クエリの実行にかかった時間

これらの情報を使用して、クエリのパフォーマンスを分析し、必要に応じて最適化することができます。
例えば、Seq Scanがパフォーマンスに悪影響を与えている場合、適切なインデックスを作成してIndex Scanを使用するように調整することが考えられます。

7. まとめ

この記事では、PostgreSQLでテーブルを作成し、データを挿入して、SQLクエリの評価を行う方法を解説しました。
EXPLAINコマンドを使用して実行計画を確認することで、クエリのパフォーマンスを最適化するための手がかりを得ることができます。
次回は、実行結果の解釈についてもう少し詳しく解説できればと思います。