PostgreSQL – インデックスを利用したパフォーマンス改善方法

Home Avintonアカデミー PostgreSQL – インデックスを利用したパフォーマンス改善方法

PostgreSQL – インデックスを利用したパフォーマンス改善方法

データベースのパフォーマンス改善において、クエリの実行プランを最適化することは非常に重要です。
インデックスを活用することでデータの検索速度を大幅に向上させることができます。
本記事では、PostgreSQLのEXPLAINコマンドとANALYZEオプションを用いたクエリプランの確認方法や、インデックスを適用した際のパフォーマンス改善効果について詳しく解説します。
PostgreSQLのセットアップ

スキーマ、テーブルの作成

データベースにスキーマを設定し、必要なテーブルを作成することが最初のステップです。
ここでは、地理情報を管理するテーブルを例にします。

CREATE SCHEMA test;

1	CREATE SCHEMA test;

CREATE TABLE test.geographical_data(
  id serial,
  pref CHAR(1),
  city CHAR(1),
  latitude FLOAT,
  longitude FLOAT
);
INSERT INTO test.geographical_data (pref, city, latitude, longitude)
SELECT
  CHR(ASCII('A') + FLOOR(random() * 5)::int) AS pref,
  CHR(ASCII('a') + FLOOR(random() * 26)::int) AS city,
  ROUND(CAST(random() AS NUMERIC), 3) AS latitude,
  ROUND(CAST(random() AS NUMERIC), 3) AS longitude
FROM generate_series(1, 10000000);

CREATE TABLE test.geographical_data(

id serial,

pref CHAR(1),

city CHAR(1),

latitude FLOAT,

longitude FLOAT

);

INSERT INTO test.geographical_data (pref, city, latitude, longitude)

SELECT

CHR(ASCII('A') + FLOOR(random() * 5)::int) AS pref,

CHR(ASCII('a') + FLOOR(random() * 26)::int) AS city,

ROUND(CAST(random() AS NUMERIC), 3) AS latitude,

ROUND(CAST(random() AS NUMERIC), 3) AS longitude

FROM generate_series(1, 10000000);

以下のようなテーブルデータが作成されます。

postgres=# SELECT * FROM test.geographical_data LIMIT 10;
 id | pref | city | latitude | longitude
----+------+------+----------+-----------
  1 | E    | r    |    0.248 |     0.722
  2 | D    | p    |    0.614 |     0.874
  3 | B    | d    |    0.908 |     0.002
  4 | E    | f    |    0.746 |     0.219
  5 | D    | b    |    0.875 |     0.204
  6 | D    | x    |    0.182 |      0.12
  7 | D    | h    |    0.235 |     0.152
  8 | A    | z    |    0.269 |     0.981
  9 | D    | b    |    0.947 |     0.659
 10 | E    | z    |    0.533 |     0.426
(10 rows)

postgres=# SELECT * FROM test.geographical_data LIMIT 10;

id | pref | city | latitude | longitude

----+------+------+----------+-----------

1 | E | r | 0.248 | 0.722

2 | D | p | 0.614 | 0.874

3 | B | d | 0.908 | 0.002

4 | E | f | 0.746 | 0.219

5 | D | b | 0.875 | 0.204

6 | D | x | 0.182 | 0.12

7 | D | h | 0.235 | 0.152

8 | A | z | 0.269 | 0.981

9 | D | b | 0.947 | 0.659

10 | E | z | 0.533 | 0.426

(10 rows)

作成されるデータのサイズ: 約500MB
行数: 1000万行

クエリプランの確認: `EXPLAIN ANALYZE`の使い方

PostgreSQLでは、クエリの実行計画を確認するためにEXPLAINコマンドを使用します。
さらに、ANALYZEオプションを追加すると、実際の実行結果も表示されるため、どのようにクエリが処理されたかが具体的にわかります。
以下のクエリを実行することで、各ステップのコストや実行時間を表示できます。

EXPLAIN ANALYZE
SELECT 
  *
FROM 
  test.geographical_data
WHERE 
  pref = 'A' AND city = 'a';

EXPLAIN ANALYZE

SELECT

FROM

test.geographical_data

WHERE

pref = 'A' AND city = 'a';

インデックス

現状、WHERE句を満たすデータを検出するためにはgeographical_dataテーブル全体を1行ずつスキャンする必要があります。
geographical_dataテーブルに多くの行があり、この問い合わせで返されるのが数行の場合、非効率です。
その様な場合、prefやcity列にインデックスを使用することで、クエリ実行時のパフォーマンスを向上させることができます。
インデックスの種類

CREATE INDEX index_name ON schema.table_name (column1, column2...);

1	CREATE INDEX index_name ON schema.table_name (column1, column2...);

演習

各インデックスを使用した際の合計コストや実行時間の改善率を以下の表の様にまとめましょう。

クエリ実行時にインデックスが使用されているか使用されていないかを判断するにはどこを確認すればよいでしょうか。
どの様な点を考慮してインデックスを使用すべきでしょうか。
以下のクエリをインデックスを用いて処理速度を改善しましょう。

EXPLAIN ANALYZE
SELECT pref,
       city,
       latitude,
       longitude
FROM test.geographical_data
GROUP BY pref,
         city,
         latitude,
         longitude;

EXPLAIN ANALYZE

SELECT pref,

city,

latitude,

longitude

FROM test.geographical_data

GROUP BY pref,

city,

latitude,

longitude;

あなたも、Avintonでこのような最先端技術を習得し活用してみませんか？

社員の成長を導きながら、AIやビッグデータなどの最先端技術をプロジェクトに活用していくことが私たちのビジョンです。Avintonの充実した技術研修でスキルアップを図り、あなたのキャリア目標を一緒に達成しませんか？

採用情報

PostgreSQL – インデックスを利用したパフォーマンス改善方法