はじめに
この実験では、インデックスを使用して PostgreSQL データベースのパフォーマンスを最適化する方法を学びます。まず、サンプル users テーブルを作成し、データを投入します。次に、単一列インデックスを作成し、EXPLAIN を使用してクエリプランを分析し、複数列インデックスを構築し、最後に、データベースの効率を維持するために使用されていないインデックスを削除する方法を学びます。この実践的な経験により、PostgreSQL のインデックス管理に関する実践的なスキルを習得できます。
単一カラムインデックスの作成
このステップでは、users という名前のサンプルテーブルを作成し、次に email 列に単一列インデックスを作成します。インデックスは、特に大規模なテーブルを扱う場合に、データベースクエリのパフォーマンスを向上させるために不可欠です。
まず、postgres ユーザーとして PostgreSQL データベースに接続します。
sudo -u postgres psql
次に、users テーブルを作成します。以下の SQL コマンドを実行してください。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP
);
このコマンドは、id、username、email、created_at の列を持つ users という名前のテーブルを作成します。id 列は主キーであり、自動的にインクリメントされます。
次に、users テーブルにサンプルデータを挿入します。以下の SQL コマンドを実行してください。
INSERT INTO users (username, email, created_at) VALUES
('john_doe', 'john.doe@example.com', NOW()),
('jane_smith', 'jane.smith@example.com', NOW()),
('peter_jones', 'peter.jones@example.com', NOW()),
('mary_brown', 'mary.brown@example.com', NOW()),
('alice_wilson', 'alice.wilson@example.com', NOW()),
('bob_davis', 'bob.davis@example.com', NOW()),
('carol_garcia', 'carol.garcia@example.com', NOW()),
('david_martinez', 'david.martinez@example.com', NOW()),
('emma_anderson', 'emma.anderson@example.com', NOW()),
('frank_taylor', 'frank.taylor@example.com', NOW());
-- インデックスの使用に十分な大きさにするために、追加の行を挿入します
INSERT INTO users (username, email, created_at)
SELECT
'user_' || generate_series(1, 1000),
'user' || generate_series(1, 1000) || '@example.com',
NOW();
これで、users テーブルに 1000 行以上のデータが挿入されました。PostgreSQL は通常、テーブル全体をスキャンするよりもパフォーマンス上の利点がある場合にインデックスを使用するため、このより大きなデータセットはインデックスの使用をより効果的に示すのに役立ちます。
email 列に基づくクエリを高速化するために、email 列にインデックスを作成します。以下の SQL コマンドを実行してください。
CREATE INDEX idx_users_email ON users (email);
このコマンドは、users テーブルの email 列に idx_users_email という名前のインデックスを作成します。
インデックスが作成されたことを確認するには、psql で \di コマンドを使用できます。以下のコマンドを実行してください。
\di
出力に idx_users_email インデックスが表示されるはずです。
最後に、psql シェルを終了するには、次のように入力します。
\q
EXPLAIN を使用してクエリプランを分析する
このステップでは、PostgreSQL の EXPLAIN コマンドを使用してクエリプランを分析する方法を学びます。クエリプランを理解することは、データベースクエリの最適化と効率的なパフォーマンスの確保に不可欠です。
まず、postgres ユーザーとして PostgreSQL データベースに接続します。
sudo -u postgres psql
次に、EXPLAIN コマンドを使用して簡単なクエリを分析しましょう。以下のコマンドを実行してください。
EXPLAIN SELECT * FROM users WHERE email = 'jane.smith@example.com';
このコマンドは、SELECT ステートメントのクエリプランを表示します。データセットが大きいため、出力に「Index Scan」または「Bitmap Index Scan」が表示されるはずです。これは、PostgreSQL が idx_users_email インデックスを使用して、特定のメールアドレスを持つ行を効率的に見つけていることを示しています。
より詳細な情報（コストなど）を取得するには、EXPLAIN ANALYZE を使用できます。ただし、この基本的な例では EXPLAIN で十分です。
次に、メールインデックスを使用しないクエリを分析しましょう。以下のコマンドを実行してください。
EXPLAIN SELECT * FROM users WHERE username LIKE 'user_%';
出力には users テーブルに対する「Seq Scan」（シーケンシャルスキャン）が表示されます。これは、PostgreSQL が一致する行を見つけるためにテーブル全体をスキャンしていることを意味します。これは、以下の理由によります。
username列にインデックスがない- 末尾にワイルドカードがある
LIKE演算子はインデックスから恩恵を受けることができますが、インデックスがない場合は PostgreSQL はすべての行をスキャンする必要があります。
これは、WHERE 句で頻繁に使用される列にインデックスを作成することの重要性を示しています。
EXPLAIN でクエリプランを分析することにより、潜在的なパフォーマンスのボトルネックを特定し、インデックスが効果的に使用されているかどうかを判断できます。
最後に、psql シェルを終了するには、次のように入力します。
\q
複数カラムインデックスの構築
このステップでは、PostgreSQL でマルチカラムインデックスを作成する方法を学びます。マルチカラムインデックスとは、2 つ以上の列を含むインデックスのことです。クエリが複数の列で同時にフィルタリングまたはソートする場合、クエリパフォーマンスを大幅に向上させることができます。
まず、postgres ユーザーとして PostgreSQL データベースに接続します。
sudo -u postgres psql
users テーブルに対して、username と email の両方の列に基づいてクエリを実行することが多いと仮定します。これらのクエリを最適化するために、これらの 2 つの列にマルチカラムインデックスを作成できます。以下の SQL コマンドを実行してください。
CREATE INDEX idx_users_username_email ON users (username, email);
このコマンドは、users テーブルの username 列と email 列に idx_users_username_email という名前のインデックスを作成します。インデックス定義における列の順序は重要です。クエリがインデックスに表示される順序と同じ順序で列をフィルタリングする場合、インデックスは最も効果的です。
インデックスが作成されたことを確認するには、psql で \di コマンドを使用できます。以下のコマンドを実行してください。
\di
出力に idx_users_username_email インデックスが表示されるはずです。
次に、このマルチカラムインデックスから恩恵を受けることができるクエリを分析しましょう。以下のコマンドを実行してください。
EXPLAIN SELECT * FROM users WHERE username = 'user_1' AND email = 'user1@example.com';
出力では、PostgreSQL が idx_users_username_email インデックスを使用してクエリを効率的に実行していることが示されるはずです。「Index Scan」または「Bitmap Index Scan」がクエリプランに表示され、マルチカラムインデックスが利用されていることを示しています。
最後に、psql シェルを終了するには、次のように入力します。
\q
未使用インデックスの削除
このステップでは、PostgreSQL で未使用のインデックスを削除する方法を学びます。インデックスはクエリパフォーマンスを向上させますが、ストレージスペースを消費し、書き込み操作（挿入、更新、削除）を遅くする可能性もあります。したがって、使用されなくなったインデックスを特定して削除することが重要です。
まず、postgres ユーザーとして PostgreSQL データベースに接続します。
sudo -u postgres psql
クエリパターンを分析した結果、idx_users_email インデックスがほとんど使用されていないと判断したと仮定します。このインデックスを削除するには、以下の SQL コマンドを実行します。
DROP INDEX idx_users_email;
このコマンドは、データベースから idx_users_email という名前のインデックスを削除します。
インデックスが削除されたことを確認するには、psql で \di コマンドを使用できます。以下のコマンドを実行してください。
\di
出力に idx_users_email インデックスが表示されなくなるはずです。
インデックスを削除する前に、それが本当に未使用であることを確認することが重要です。PostgreSQL の統計コレクターを使用して、インデックスの使用状況に関する情報を収集できます。ただし、これらの統計の有効化と分析は、この実験の範囲外です。実際のシナリオでは、インデックスを削除する前に、一定期間インデックスの使用状況を監視することになります。
注意: PostgreSQL のクエリオプティマイザはインテリジェントであり、パフォーマンス上のメリットがある場合にのみインデックスを使用します。非常に小さなテーブル（通常は数百行未満）の場合、インデックスを使用するオーバーヘッドがメリットを上回るため、PostgreSQL はインデックススキャンよりもシーケンシャルスキャンを選択する場合があります。そのため、現実的なインデックス使用シナリオを実証するために、users テーブルに多くの行を追加しました。
間違ったインデックスを削除すると、クエリパフォーマンスに悪影響を与える可能性があります。したがって、注意を払い、インデックスを削除する前にクエリパターンを十分に分析してください。
最後に、psql シェルを終了するには、次のように入力します。
\q
まとめ
この実験では、PostgreSQL でクエリパフォーマンスを向上させるために、単一カラムおよびマルチカラムインデックスを作成する方法を学びました。また、EXPLAIN を使用してクエリプランを分析し、インデックスが効果的に使用されているかどうかを判断する方法も学びました。主なポイントは以下の通りです。
- PostgreSQL のクエリオプティマイザは、テーブルサイズとクエリの特性に基づいて、インデックスを使用するかどうかをインテリジェントに選択します。
- インデックスは、インデックス検索のコストが行全体の走査よりも小さい、より大きなテーブルで最も有益です。
- マルチカラムインデックスは、複数のカラムでフィルタリングするクエリのパフォーマンスを大幅に向上させることができます。
- 未使用のインデックスを定期的に監視および削除することは、最適なデータベースパフォーマンスを維持するのに役立ちます。
これらのスキルは、実際のアプリケーションで PostgreSQL データベースのパフォーマンスを最適化するために不可欠です。
