概要
PostgreSQLのインデックスは非クラスタ化インデックスです。非クラスタ化インデックスはでは、インデックス自体はテーブルのデータと直接的に並んで格納されるわけではなく、インデックスのエントリにはテーブルの行への参照（ポインタ）が格納されます。
nameとageというカラムをもつテーブルがあるとしてnameで検索をかけてもb+treeのリーフノードにはageのデータは保持しておらずそのPrimary Keyを元に再度クラスタインデックスの探索をしてageを取って来る必要があります。ここでクラスタインデックスが超大規模だとクラスタインデックスの探索にかかる計算だけでも時間がかかってしまうので多くの場合はnameだけでなくageにもインデックスを貼る複合インデックスを貼るという手法が取られます。こうすることでリーフノードにnameとageがあるのでセカンダリインデックスの探索だけで済むようになります。これはPostgreSQLに限った話ではなくMySQLでも同様です。
この時にageではsumだけしたいケースがあるとします。複合インデックスを貼るとnameでのソートに加えてageでのソートも行われます。もしもageはソートされていなくて良いのであればINSERTやUPDATE時の処理を考えるとageはキーである必要がないです。そういった時用にPostgreSQLではCREATE INDEXにINCLUDEという機能が存在します。
INCLUDE オプションのINCLUDE句は非キー列としてインデックスに含める列のリストを指定します。 非キー列をインデックススキャンの検索条件に使うことはできません。また、インデックスで何であれ一意性制約や排他制約を強制する目的に対しても無視されます。 しかしながら、インデックスオンリースキャンは、インデックスエントリから値を直接得ることができるので、インデックスのテーブルを見に行く必要なく、非キー列の内容を返すことができます。 このように非キー列の追加は、そうでないとできないインデックスオンリースキャンを利用可能にします。
実験
nameとageというカラムをもつテーブルを作ります。nameにだけINDEXを貼っておきます。
postgres=# \d+ users;
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------------------------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('users_id_seq'::regclass) | plain | | |
name | character varying(100) | | not null | | extended | | |
age | integer | | not null | | plain | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"idx_users_name" btree (name)
Access method: heap
200万行くらいINSERTしておきます。
postgres=# SELECT COUNT(*) FROM users; count --------- 2000000 (1 row)
この状態で特定のユーザー名でMAXとSUMをageに対して実行させていきます。(単位はms)
| 種類 | nameのみ | nameにインデックスを貼ってageはINCLUDE | name,ageの複合インデックス |
|---|---|---|---|
| SUM | 77.952 | 69.511 | 61.022 |
| MAX | 74.434 | 71.524 | 0.166 |
name
max
postgres=# EXPLAIN ANALYZE SELECT MAX(age) FROM users WHERE name = 'User';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=45657.64..45657.65 rows=1 width=4) (actual time=72.216..74.369 rows=1 loops=1)
-> Gather (cost=45657.43..45657.64 rows=2 width=4) (actual time=72.121..74.358 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=44657.43..44657.44 rows=1 width=4) (actual time=68.450..68.450 rows=1 loops=3)
-> Parallel Index Scan using idx_users_name on users (cost=0.43..42557.72 rows=839881 width=4) (actual time=0.089..41.757 rows=670000 loops=3)
Index Cond: ((name)::text = 'User'::text)
Planning Time: 0.318 ms
Execution Time: 74.434 ms
(9 rows)
sum
postgres=# EXPLAIN ANALYZE SELECT SUM(age) FROM users WHERE name = 'User';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=45657.64..45657.65 rows=1 width=8) (actual time=75.927..77.907 rows=1 loops=1)
-> Gather (cost=45657.43..45657.64 rows=2 width=8) (actual time=75.787..77.884 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=44657.43..44657.44 rows=1 width=8) (actual time=72.108..72.108 rows=1 loops=3)
-> Parallel Index Scan using idx_users_name on users (cost=0.43..42557.72 rows=839881 width=4) (actual time=0.060..44.793 rows=670000 loops=3)
Index Cond: ((name)::text = 'User'::text)
Planning Time: 0.149 ms
Execution Time: 77.952 ms
複合
max
postgres=# EXPLAIN ANALYZE SELECT MAX(age) FROM users WHERE name = 'User';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.45..0.46 rows=1 width=4) (actual time=0.129..0.131 rows=1 loops=1)
InitPlan 1
-> Limit (cost=0.43..0.45 rows=1 width=4) (actual time=0.122..0.124 rows=1 loops=1)
-> Index Only Scan Backward using idx_users_name_age on users (cost=0.43..42259.06 rows=2015693 width=4) (actual time=0.120..0.121 rows=1 loops=1)
Index Cond: (name = 'User'::text)
Heap Fetches: 0
Planning Time: 0.234 ms
Execution Time: 0.166 ms
sum
postgres=# EXPLAIN ANALYZE SELECT SUM(age) FROM users WHERE name = 'User';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=33600.75..33600.76 rows=1 width=8) (actual time=59.772..60.976 rows=1 loops=1)
-> Gather (cost=33600.53..33600.74 rows=2 width=8) (actual time=59.660..60.972 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=32600.53..32600.54 rows=1 width=8) (actual time=56.254..56.255 rows=1 loops=3)
-> Parallel Index Only Scan using idx_users_name_age on users (cost=0.43..30500.85 rows=839872 width=4) (actual time=0.038..34.458 rows=670000 loops=3)
Index Cond: (name = 'User'::text)
Heap Fetches: 0
Planning Time: 0.256 ms
Execution Time: 61.022 ms
(10 rows)
参考
https://modern-sql.com/slides/PostgreSQL-BTree-INCLUDE-20190312.pdf
