【 PostgreSQL】インデックスの有無による実行計画の違い | JOINを含むSQLの場合

2021年11月6日2021年11月14日

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

検証用テーブルの作成

２つのテーブルをJOINするSQLにおけるインデックスの有無による実行計画の違いを確認します。検証のために以下のテーブルを作成します。

-- 書籍のカテゴリ
create table category (
    id serial,
    dummy_id int not null,
    category_name text not null,
    updated_at timestamp with time zone not null,
    constraint pk_category primary key(id)
);

-- 書籍
create table book (
    id serial,
    book_title text not null,
    category_id int not null,
    updated_at timestamp with time zone not null,
    constraint pk_book primary key(id),
    constraint fk_category foreign key(category_id) references category(id)
);

以下のSQLを使ってcategoryに1万件、bookに200万件のテストデータを投入します。

insert into category (
    id,
    dummy_id,
    category_name,
    updated_at
) 
select
    i,
    i,
    md5(i::text),
    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, 10000, 1) as i;

insert into book (
    id,
    book_title,
    category_id,
    updated_at
) 
select
    i,
    md5(i::text),
    1 + round(random() * 9999),
    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, 2000000, 1) as i;

検証

categoryのdummy_idとbookのcategory_idをキーにしてinner joinします。

$ explain analyze select category_name, book_title from category c inner join book b on c.dummy_id = b.category_id where c.dummy_id = 2500;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1000.00..32286.67 rows=200 width=66) (actual time=1.618..1373.750 rows=191 loops=1)
   ->  Seq Scan on category c  (cost=0.00..229.00 rows=1 width=37) (actual time=0.299..1.831 rows=1 loops=1)
         Filter: (dummy_id = 2500)
         Rows Removed by Filter: 9999
   ->  Gather  (cost=1000.00..32055.67 rows=200 width=37) (actual time=1.283..1364.491 rows=191 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Seq Scan on book b  (cost=0.00..31035.67 rows=83 width=37) (actual time=9.892..843.761 rows=64 loops=3)
               Filter: (category_id = 2500)
               Rows Removed by Filter: 666603
 Planning Time: 0.098 ms
 Execution Time: 1376.587 ms
(12 rows)

次にcategoryの主キーであるidとbookのcategory_idをキーにしてinner joinします。

$ explain analyze select category_name, book_title from category c inner join book b on c.id = b.category_id where c.id = 3500;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1000.28..32065.97 rows=200 width=66) (actual time=3.163..599.164 rows=177 loops=1)
   ->  Index Scan using pk_category on category c  (cost=0.29..8.30 rows=1 width=37) (actual time=0.312..0.340 rows=1 loops=1)
         Index Cond: (id = 3500)
   ->  Gather  (cost=1000.00..32055.67 rows=200 width=37) (actual time=2.815..591.423 rows=177 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Seq Scan on book b  (cost=0.00..31035.67 rows=83 width=37) (actual time=1.932..254.088 rows=59 loops=3)
               Filter: (category_id = 3500)
               Rows Removed by Filter: 666608
 Planning Time: 0.171 ms
 Execution Time: 601.880 ms

categoryに対する検索がIndex Scanになりました。この結果ではdummy_idでjoinした場合より約2倍速くなっていますが、何度か実行したところ実行時間にあまり差がないときもありました。

bookテーブルのcategory_idにインデックスを作成して実行計画の違いを確認します。

$ create index idx_category_id on book(category_id);
CREATE INDEX

先ほどと同様にcategoryの主キーであるidとbookのcategory_idをキーにしてinner joinします。

$ explain analyze select category_name, book_title from category c inner join book b on c.dummy_id = b.category_id where c.dummy_id = 2510;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.98..980.38 rows=200 width=66) (actual time=16.064..43.718 rows=187 loops=1)
   ->  Seq Scan on category c  (cost=0.00..229.00 rows=1 width=37) (actual time=0.337..6.270 rows=1 loops=1)
         Filter: (dummy_id = 2510)
         Rows Removed by Filter: 9999
   ->  Bitmap Heap Scan on book b  (cost=5.98..749.38 rows=200 width=37) (actual time=15.686..28.927 rows=187 loops=1)
         Recheck Cond: (category_id = 2510)
         Heap Blocks: exact=187
         ->  Bitmap Index Scan on idx_category_id  (cost=0.00..5.93 rows=200 width=0) (actual time=15.619..15.628 rows=187 loops=1)
               Index Cond: (category_id = 2510)
 Planning Time: 34.321 ms
 Execution Time: 47.080 ms

インデックスの追加により検索がかなり速くなっていることが確認できます。