【 PostgreSQL】インデックスの有無による実行計画の違いを確認する

2021年10月27日2021年11月14日

動作確認はPostgreSQL 13.0で行っています。

検証用テーブルの作成

テーブルのカラムに対するインデックスの有無による実行計画の違いを確認します。検証のために以下のテーブルを作成します。

create table item(
    id serial,
    item_name text not null,
    price numeric not null,
    updated_at timestamp with time zone not null,
    constraint pk_id primary key(id)
);

以下のSQLを使ってテストデータを20万件投入します。

insert into item (
    id,
    item_name,
    price,
    updated_at
) 
select
    i,
    md5(i::text),
    10000 + round(random() * 10000),
    timestamp '1970-01-01 00:00:00' + random() * (timestamp '2000-12-31 23:59:59' - timestamp '1970-01-01 00:00:00')
from
generate_series(1, 200000, 1) as i;

検証

主キーであるidをwhere句の条件に指定したselect文の実行計画を確認します。以下のようにIndex Scanとなることが確認できます。

$ explain analyze select * from item where id = 10003;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Index Scan using pk_id on item  (cost=0.42..8.44 rows=1 width=51) (actual time=0.041..0.057 rows=1 loops=1)
   Index Cond: (id = 10003)
 Planning Time: 24.022 ms
 Execution Time: 0.153 ms
(4 rows)

次にIndexが作られていないitem_nameをwhere句の条件に指定したselect文の実行計画を確認します。以下のようにSeq Scanとなることが確認できます。実行時間もIndex Scanに比べて約200倍遅くなっています。

$ explain analyze select * from item where item_name = 'f5dffc111454b227fbcdf36178dfe6ac';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on item  (cost=0.00..4562.00 rows=1 width=51) (actual time=1.478..29.498 rows=1 loops=1)
   Filter: (item_name = 'f5dffc111454b227fbcdf36178dfe6ac'::text)
   Rows Removed by Filter: 199999
 Planning Time: 0.102 ms
 Execution Time: 29.563 ms
(5 rows)

selectで指定するカラムをidのみした場合はIndex Only Scanとなることが確認できます。何度か実行しましたが、Index Scanに比べて必ず速くなるというわけではないようです。

$ explain analyze select id from item where id = 10009;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Only Scan using pk_id on item  (cost=0.42..4.44 rows=1 width=4) (actual time=0.145..0.168 rows=1 loops=1)
   Index Cond: (id = 10009)
   Heap Fetches: 0
 Planning Time: 0.089 ms
 Execution Time: 0.250 ms
(5 rows)