【初心者向け】 PostgreSQL の EXPLAIN の出力を確認してみた

[ec2-user@ip-10-0-13-178 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.

postgres=# select * from version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.7 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)

-- 検証用データベース(explain_test)の作成
postgres=# CREATE DATABASE explain_test;
CREATE DATABASE

-- explain_test データベースが作成できているか確認
postgres=# \l
                                                   List of databases
     Name     |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges   
--------------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 explain_test | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | 
 postgres     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | 
...
(4 rows)

-- データベース切り替え
postgres=# \c explain_test
You are now connected to database "explain_test" as user "postgres".

-- テーブル(users)作成
explain_test=# CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    age  INT
);
CREATE TABLE

-- 10 万件のデータを投入
explain_test=# INSERT INTO users (name, age)
SELECT
    'user_' || i,
    (random() * 100)::INT
FROM generate_series(1, 100000) i;
INSERT 0 100000

-- レコード件数の確認
explain_test=# SELECT COUNT(*) FROM users;
 count  
--------
 100000
(1 row)

-- 最初の 5 行を表示
explain_test=# SELECT * FROM users LIMIT 5;
 id |  name  | age 
----+--------+-----
  1 | user_1 |  17
  2 | user_2 |  47
  3 | user_3 |  89
  4 | user_4 |  17
  5 | user_5 |  36
(5 rows)

-- 最後の 5 行を表示
explain_test=# SELECT * FROM users ORDER BY id DESC LIMIT 5;
   id   |    name     | age 
--------+-------------+-----
 100000 | user_100000 |  39
  99999 | user_99999  |  54
  99998 | user_99998  |  65
  99997 | user_99997  |  66
  99996 | user_99996  |  65
(5 rows)

--各カラムの説明
id： 1 〜 100,000 までの連番
name： user_1 〜 user_100000 までの文字列
age： 0 〜 100 のランダムな整数

補足：
age をランダムな整数にしているのは
「WHERE age = 25」のような条件で絞り込んだときに
インデックスの効果が出やすくするためです。

explain_test=# EXPLAIN SELECT * FROM users WHERE age = 25;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on users  (cost=0.00..1887.00 rows=960 width=18)
   Filter: (age = 25)
(2 rows)

-- 現状のインデックス状態を確認
-- users テーブルには id カラムのみインデックスが作られている
explain_test=# \d users
                            Table "public.users"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('users_id_seq'::regclass)
 name   | text    |           |          | 
 age    | integer |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

-- age カラムにインデックスを作成
explain_test=# CREATE INDEX idx_users_age ON users(age);
CREATE INDEX

-- 作成できたか確認
-- idx_users_age が作成できているので OK
explain_test=# \d users
                            Table "public.users"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('users_id_seq'::regclass)
 name   | text    |           |          | 
 age    | integer |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "idx_users_age" btree (age)

explain_test=# EXPLAIN SELECT * FROM users WHERE age = 25;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=11.73..690.90 rows=960 width=18)
   Recheck Cond: (age = 25)
   ->  Bitmap Index Scan on idx_users_age  (cost=0.00..11.49 rows=960 width=0)
         Index Cond: (age = 25)
(4 rows)

Step 1: Bitmap Index Scan on idx_users_age
        インデックスを使って age = 25 の行がどこにあるかを調べる

Step 2: Bitmap Heap Scan on users
        Step 1 で調べた場所から実際のデータを取りに行く

explain_test=# EXPLAIN SELECT * FROM users WHERE age = 25;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on users  (cost=0.00..1887.00 rows=960 width=18)
   Filter: (age = 25)
(2 rows)

explain_test=# EXPLAIN SELECT * FROM users WHERE age = 25;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=11.73..690.90 rows=960 width=18)
   Recheck Cond: (age = 25)
   ->  Bitmap Index Scan on idx_users_age  (cost=0.00..11.49 rows=960 width=0)
         Index Cond: (age = 25)
(4 rows)