インデックスメンテナンスが必要な状況

インデックスへのアクセス性能が低下する原因
- 肥大化
- 断片化
- クラスタ性の欠落

インデックスファイルの肥大化

テーブルの肥大化と同様
有効なデータが少量なのにページがかさんでいる状態
pg_classビューでページ数・タプル数をもとに診断できる

postgres=# SELECT relname,relpages,reltuples FROM pg_class WHERE relname='idx_1';

 relname | relpages | reltuples 
---------+----------+-----------
 idx_1   |        2 |         3
(1 row)

インデックスファイルの断片化

B-treeインデックス固有の事象
テーブルとインデックス作る

postgres=# CREATE TABLE tbl (i INTEGER);
CREATE TABLE

postgres=# CREATE INDEX ON tbl USING btree (i);
CREATE INDEX

postgres=# \di
               List of relations
 Schema |   Name    | Type  |  Owner   | Table 
--------+-----------+-------+----------+-------
 public | tbl_i_idx | index | postgres | tbl
(1 row)

postgres=# SELECT relname,relpages,reltuples FROM pg_class where relname='tbl_i_idx';
  relname  | relpages | reltuples 
-----------+----------+-----------
 tbl_i_idx |        1 |         0
(1 row)

500行挿入し、インデックスのrelpagesとreltuples確認
- 500行に4ページ

postgres=# INSERT INTO tbl VALUES (generate_series(1,500));
INSERT 0 500

postgres=# ANALYSE tbl;
ANALYZE

postgres=# SELECT relname,relpages,reltuples FROM pg_class where relname='tbl_i_idx';
  relname  | relpages | reltuples 
-----------+----------+-----------
 tbl_i_idx |        4 |       500
(1 row)

250行削除し、250行挿入、のちインデックスのrelpagesとreltuplesを再確認
- まだ500行に4ページ

postgres=# DELETE FROM tbl WHERE i % 2 = 0;
DELETE 250

postgres=# INSERT INTO tbl VALUES (generate_series(2,500,2));
INSERT 0 250

postgres=# ANALYSE tbl;
ANALYZE

postgres=# SELECT relname,relpages,reltuples FROM pg_class where relname='tbl_i_idx';
  relname  | relpages | reltuples 
-----------+----------+-----------
 tbl_i_idx |        4 |       500
(1 row)

もう一度

postgres=# DELETE FROM tbl WHERE i % 2 = 0;
DELETE 250

postgres=# INSERT INTO tbl VALUES (generate_series(2,500,2));
INSERT 0 250

postgres=# ANALYSE tbl;
ANALYZE

postgres=# SELECT relname,relpages,reltuples FROM pg_class where relname='tbl_i_idx';
  relname  | relpages | reltuples 
-----------+----------+-----------
 tbl_i_idx |        5 |       500
(1 row)

5ページになった
行の削除・挿入を繰り返すとページが断片化していく
- 同じ有効データを格納するのに余分のページを要している
- つまり、1ページあたりの有効データが少ない
- キャッシュヒット率が低下し、性能に悪影響を及ぼす

断片化を調べる方法

contribモジュールのpgstattupleに含まれるpgstatindex関数を使用する
- 自分でmake worldしなくてもdockerhubのpostgresイメージをそのまま使えば入ってる

postgres=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION


postgres=# SELECT * FROM pgstatindex('tbl_i_idx');
-[ RECORD 1 ]------+------
version            | 4
tree_level         | 1
index_size         | 40960
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 3
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 41.1
leaf_fragmentation | 33.33

の

leaf_fragmentation | 33.33

これ

クラスタ性の欠落

公式
クラスタ性
- テーブルデータのカラムの物理的な配置と論理的な順序の相関
- あるテーブルのあるカラムのクラスタ性が高いとは、相関係数が1または-1に近いということ
クラスタ性が高いと、そのカラムのインデックススキャンはI/Oが少なく低コストで済む

クラスタ性を調べる方法

ANALYZE後、pg_statsビューのcorrelationカラムを参照する
連番を挿入したてだと正の相関(+1)

postgres=# CREATE TABLE tbl2 (col int);
CREATE TABLE

postgres=# INSERT INTO tbl2 VALUES(generate_series(1,1000));
INSERT 0 1000

postgres=# ANALYZE tbl2;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
 tablename | attname | correlation 
-----------+---------+-------------
 tbl2      | col     |           1
(1 row)

UPDATEして、偶数だけ後ろへ
- 【所感】ノコギリの歯みたいになるのかな

postgres=# UPDATE tbl2 SET col=col WHERE col % 2 = 0;
UPDATE 500

postgres=# ANALYSE tbl2;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';

 tablename | attname | correlation 
-----------+---------+-------------
 tbl2      | col     |   0.5014985
(1 row)

相関係数が約0.5になった

【予防策】インデックスファイルの肥大化

テーブルの肥大化よろしく、VACUUMでインデックスメンテナンスも行われる

【改善策】インデックスファイルの断片化

REINDEXでインデックスを再定義する

Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

URL: https://www.postgresql.org/docs/12/sql-reindex.html

対象
- INDEX: 対象のインデックス
- TABLE: 対象のテーブルの全インデックス
- SCHEMA: 対象のスキーマ上の全インデックス
- DATABASE: 対象のデータベース上の全インデックス
- SYSTEM: 対象のデータベース上のシステムカタログのインデックス
CONCURRENTLY
- REINDEXでは、デフォルトでSHARE UPDATE EXCLUSIVEテーブルロックを獲得する
- CONCURRENTLYオプションをつけると、ロックを獲得しない
- 制限
  - トランザクションブロック内では実行できない
  - SYSTEMとは併用できない
実施してみる
実施前

postgres=# SELECT * FROM pgstatindex('tbl_i_idx');
-[ RECORD 1 ]------+------
version            | 4
tree_level         | 1
index_size         | 65536
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 6
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 71.81
leaf_fragmentation | 33.33

postgres=# select relname,relpages,reltuples from  pg_class where relname='tbl_i_idx';
-[ RECORD 1 ]--------
relname   | tbl_i_idx
relpages  | 8
reltuples | 1000

REINDEX実施

postgres=# REINDEX INDEX tbl_i_idx;
REINDEX

postgres=# SELECT relname,relpages,reltuples FROM pg_class WHERE relname='tbl_i_idx';
-[ RECORD 1 ]--------
relname   | tbl_i_idx
relpages  | 5
reltuples | 1000

postgres=# SELECT * FROM pgstatindex('tbl_i_idx');
-[ RECORD 1 ]------+------
version            | 4
tree_level         | 1
index_size         | 40960
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 3
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 81.99
leaf_fragmentation | 0

インデックスのページ数が減少、密度は増大、断片化が解消された

【改善策】クラスタ性の欠落

公式/CLUSTER

Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

URL: https://www.postgresql.org/docs/12/sql-cluster.html

実施前

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
-[ RECORD 1 ]----------
tablename   | tbl2
attname     | col
correlation | 0.5014985

実施後確認

postgres=# CLUSTER tbl2 using tbl2_col_idx;
CLUSTER

postgres=# ANALYZE tbl2;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
-[ RECORD 1 ]-----
tablename   | tbl2
attname     | col
correlation | 1

CLUSTER実行時に適用されるインデックス

PostgreSQLでは、MySQLのClusteredIndexのように、クラスタ性が保たれるインデックスを定義することはできない
が、定期的にクラスタ化を実行することでクラスタ性を保つことはできる
クラスタ性が崩れたテーブルを用意する

postgres=# UPDATE tbl2 SET col=col WHERE col % 2 = 0;
UPDATE 500

postgres=# ANALYZE tbl2;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
-[ RECORD 1 ]----------
tablename   | tbl2
attname     | col
correlation | 0.5014985

再度クラスタ化する際はインデックス指定不要

postgres=# CLUSTER tbl2;
CLUSTER tbl2;

postgres=# ANALYZE tbl2;
ANALYZE tbl2;

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='tbl2';
-[ RECORD 1 ]-----
tablename   | tbl2
attname     | col
correlation | 1

当該インデックスでクラスタ化したという情報はシステムカタログに残る

postgres=# \d tbl2
                Table "public.tbl2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "tbl2_col_idx" btree (col) CLUSTER

ALTER TABLE文で解除可能

postgres=# ALTER TABLE tbl2 SET WITHOUT CLUSTER;
ALTER TABLE

postgres=# \d tbl2
                Table "public.tbl2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "tbl2_col_idx" btree (col)

この状態でインデックス指定省力してCLUSTER文を実行するとエラー

postgres=# CLUSTER tbl2;
ERROR:  there is no previously clustered index for table "tbl2"

ALTER TABLE文でクラスタ化指定することも可能

postgres=# ALTER TABLE tbl2 CLUSTER ON tbl2_col_idx;
ALTER TABLE

postgres=# \d tbl2
                Table "public.tbl2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "tbl2_col_idx" btree (col) CLUSTER

postgres=# CLUSTER VERBOSE tbl2;
INFO:  clustering "public.tbl2" using index scan on "tbl2_col_idx"
INFO:  "tbl2": found 0 removable, 1000 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
CLUSTER

クラスタ化に用いるインデックスは(当然)1つ
{deg, sin}なるサイン関数のテーブルで試してみる

postgres=# CLUSTER sin USING sin_deg_idx;
CLUSTER

postgres=# \d sin
                     Table "public.sin"
 Column |       Type       | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 deg    | integer          |           |          | 
 sin    | double precision |           |          | 
Indexes:
    "sin_deg_idx" btree (deg) CLUSTER
    "sin_sin_idx" btree (sin)

postgres=# ANALYZE sin;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='sin';
 tablename | attname | correlation 
-----------+---------+-------------
 sin       | sin     | -0.17651178
 sin       | deg     |           1
(2 rows)

degのBtreeインデックスを用いてクラスタ化すると当然sinのクラスタ性は下がる
sinのBtreeインデックスを用いてクラスタ化してみる

postgres=# CLUSTER sin USING sin_sin_idx;
CLUSTER

postgres=# ANALYZE sin;
ANALYZE

postgres=# SELECT tablename,attname,correlation FROM pg_stats WHERE tablename='sin';
 tablename | attname | correlation 
-----------+---------+-------------
 sin       | sin     |           1
 sin       | deg     | -0.17651178
(2 rows)

postgres=# \d sin
                     Table "public.sin"
 Column |       Type       | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 deg    | integer          |           |          | 
 sin    | double precision |           |          | 
Indexes:
    "sin_deg_idx" btree (deg)
    "sin_sin_idx" btree (sin) CLUSTER

sinについて物理的に行が並び替わり、degのクラスタ性が下がる
"sin_deg_idx" btree (deg)のCLUSTERが外れる

CLUSTER実行時の注意点

CLUSTERを実行すると、インデックスがB-treeならばREINDEXも実施される
メモリ食う
- maintenance_work_memにはCLUSTERの実施に足りるぶんを割り当てること
VACUUM FULL同様、余計のディスク領域を要する
- 対象テーブル/インデックスと同程度

インデックスオンリースキャンの利用

公式
PostgreSQL 9.2~
テーブルデータを参照せずに、インデックスから結果行を返す
返却行はそのトランザクションから可視である必要がある
が、可視性の情報はインデックス領域にはない
可視性をチェックするためにテーブルデータを舐めるとインデックスオンリースキャンの意味がない
ので、Visibility Mapを用いて判定を行う
- テーブルデータと比べて容量が何桁も小さいので、I/Oコストが低い
  - テーブルデータの1ページは8192バイト
  - Visibility Mapは、テーブルデータ1ページの可視性を2ビットで管理する
テーブルデータ(Heap)を取得していないことは実行計画で確認できる

EXPLAIN (ANALYZE on, BUFFERS on) SELECT...

インデックスオンリースキャンの利用上の注意

制限
- インデックスが返却行の情報を有していること
  - B-tree, GiST, SP-GiST
  - hashなんかは当然ダメ
  - GINは元の情報の一部しか持っていないのでダメ
更新が激しいと効果が薄い
- Visibility Mapは8192バイトのページ単位
- 取得行が可視だとしても、同じページの別の行が更新されるとVisibility Mapのビットは下りてしまう
- すると、結局テーブルデータの確認が必要になる

D.Horiyama's output

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch14 インデックスメンテナンス

出典:

インデックスメンテナンスが必要な状況

インデックスファイルの肥大化

インデックスファイルの断片化

断片化を調べる方法

クラスタ性の欠落

クラスタ性を調べる方法

【予防策】インデックスファイルの肥大化

【改善策】インデックスファイルの断片化

【改善策】クラスタ性の欠落

CLUSTER実行時に適用されるインデックス

CLUSTER実行時の注意点

インデックスオンリースキャンの利用

インデックスオンリースキャンの利用上の注意