PostgreSQL インデックス最適化 | パフォーマンス改善

はじめに

この実験（Lab）では、インデックスを使用して PostgreSQL データベースのパフォーマンスを最適化する方法を学びます。まず、サンプル users テーブルを作成し、データを投入します。次に、単一カラムのインデックスを作成し、EXPLAIN を使用してクエリプランを分析し、複数カラムのインデックスを構築します。最後に、データベースの効率を維持するために、未使用のインデックスを削除する方法を学びます。この実践的な経験を通して、PostgreSQL のインデックス管理に関する実用的なスキルを習得できます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") postgresql/PostgreSQLGroup -.-> postgresql/idx_simple("Create Simple Index") postgresql/PostgreSQLGroup -.-> postgresql/idx_drop("Drop Existing Index") subgraph Lab Skills postgresql/table_init -.-> lab-550955{{"PostgreSQL インデックス最適化"}} postgresql/row_add -.-> lab-550955{{"PostgreSQL インデックス最適化"}} postgresql/data_where -.-> lab-550955{{"PostgreSQL インデックス最適化"}} postgresql/idx_simple -.-> lab-550955{{"PostgreSQL インデックス最適化"}} postgresql/idx_drop -.-> lab-550955{{"PostgreSQL インデックス最適化"}} end

単一カラムのインデックスを作成する

このステップでは、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', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('jane_smith', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('peter_jones', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('mary_brown', '[email protected]', NOW());

これで、users テーブルに 4 行のデータが挿入されました。

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 = '[email protected]';

このコマンドは、SELECT ステートメントのクエリプランを表示します。出力には、PostgreSQL がクエリをどのように実行しようとしているか（インデックスを使用するかどうかなど）が示されます。

コストなどの詳細情報を取得するには、EXPLAIN ANALYZE を使用できます。ただし、この基本的な例では、EXPLAIN で十分です。

次に、インデックスを使用しない可能性のあるクエリを分析してみましょう。次のコマンドを実行します。

EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

出力には、users テーブルに対する "Seq Scan" (Sequential Scan、シーケンシャルスキャン) が表示される可能性があります。これは、PostgreSQL が一致する行を見つけるためにテーブル全体をスキャンしていることを意味します。これは、インデックスを使用するよりも効率が低くなります。

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 = 'john_doe' AND email = '[email protected]';

出力には、PostgreSQL が idx_users_username_email インデックスを使用してクエリを実行していることが示されるはずです。

最後に、次のコマンドを入力して 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 の統計コレクタを使用して、インデックスの使用状況に関する情報を収集できます。ただし、これらの統計を有効にして分析することは、この実験（Lab）の範囲を超えています。実際のシナリオでは、インデックスを削除する前に、一定期間にわたってインデックスの使用状況を監視します。

誤ったインデックスを削除すると、クエリのパフォーマンスに悪影響を与える可能性があります。したがって、注意を払い、インデックスを削除する前にクエリパターンを徹底的に分析してください。

最後に、次のコマンドを入力して psql シェルを終了します。

\q

まとめ

この実験（Lab）では、クエリのパフォーマンスを向上させるために、PostgreSQL で単一カラムおよび複数カラムのインデックスを作成する方法を学びました。また、EXPLAIN を使用してクエリプランを分析し、インデックスが効果的に使用されているかどうかを判断する方法も学びました。最後に、データベースの効率を維持するために、使用されていないインデックスを削除する方法を学びました。これらのスキルは、PostgreSQL データベースのパフォーマンスを最適化するために不可欠です。