内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch14 インデックスメンテナンス
インデックスメンテナンスが必要な状況
- インデックスへのアクセス性能が低下する原因
- 肥大化
- 断片化
- クラスタ性の欠落
インデックスファイルの肥大化
- テーブルの肥大化と同様
- 有効なデータが少量なのにページがかさんでいる状態
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
これ
クラスタ性の欠落
クラスタ性を調べる方法
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
とは併用できない
- REINDEXでは、デフォルトで
- 実施してみる
- 実施前
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
- インデックスのページ数が減少、密度は増大、断片化が解消された
【改善策】クラスタ性の欠落
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)
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を用いて判定を行う
- テーブルデータ(Heap)を取得していないことは実行計画で確認できる
EXPLAIN (ANALYZE on, BUFFERS on) SELECT...
インデックスオンリースキャンの利用上の注意
- 制限
- インデックスが返却行の情報を有していること
- B-tree, GiST, SP-GiST
- hashなんかは当然ダメ
- GINは元の情報の一部しか持っていないのでダメ
- インデックスが返却行の情報を有していること
- 更新が激しいと効果が薄い
- Visibility Mapは8192バイトのページ単位
- 取得行が可視だとしても、同じページの別の行が更新されるとVisibility Mapのビットは下りてしまう
- すると、結局テーブルデータの確認が必要になる