これは 😺TECHSCORE Advent Calendar 2019😺の25日目の記事です。

横田です。転職して3カ月程ですが前職までは Oracle や SQL Server を中心に触っていました。本格的にPostgreSQLを使うようになったのが今回始めてのため、SQL Server の言葉を借りて説明している箇所が有ります。予めご了承ください。

今回は、PostgreSQL 11 で実装された付加列Index (※) を中心としたパフォーマンス関連のお話をしたいと思います。商用DBと比較してしまうとOSS-DBに無い機能が多かったりするのですが、近年はパーティショニングやIndex機能の充実化など、溝を埋めていこうとする姿に感銘を受けました。なのでDBを使う側も実装されたものを使いこなせる様になろうと思います。

※「付加列Index」はSQL serverで同機能の呼び名です。機能の詳細は後述しますが、PostgreSQLでは同機能に名前が無いので、この記事では便宜上この機能をPostgreSQLでも「付加列Index」と書いていきます。

【まずはIndexの基本をおさらい】

今回のお話は「Indexって何だろう？」を卒業したところをスタートラインとしています。もしIndexについておさらいしたい方は以下のサイトを読んでみては如何でしょうか。まあ、Indexを簡単に言うと「検索を早くするためのもの」です。

→（参考）PostgreSQL公式サイトの説明（外部サイトに飛びます）

→（参考）B-treeインデックス入門（外部サイトに飛びます）

→（参考）Indexの種類について

【Index ( B-Tree ) の基本的なアクセスパスについて】

まず最初に、B-Tree型Indexの基本的なアクセスパスについて以下のオブジェクトを使って説明します。

※SQL serverに読み替えて読んでいる方は、非クラスタ化インデックスを作成したと想定してください。

[ テーブル定義を表したDDL ]　※データは入っている前提でお話します。

CREATE TABLE TEST1 ( x int , y int , z int ) ;
CREATE INDEX TEST1_x1 ON TEST1( x ) ;

(図1) 作成するテーブルのイメージ

このテーブルに対して以下のクエリを発行したとします。
SELECT x , y FROM TEST1 WHERE x = 3 ;

この場合、以下の流れになります。(図2)

１）Indexを使って[ x = 3 ] の行を探して、実データテーブルのアドレスを特定します。

２）そのアドレスを使って実際のテーブル行からデータを取得します。

この１～２の流れでデータを探す動作は、SQL serverの言葉を使うとRID Lookup、Oracleの言葉を使うとTABLE ACCESS BY INDEX ROWID と言います。PostgreSQLでの呼び名が解らないので、本記事では便宜上この動作をRID Lookupと呼んで説明をしていきます。RID Lookupが発生すると余計な処理が発生して遅くなると思ってください。

(図2) B-Tree Indexの基本的なアクセスパス

【カバリングインデックス】

先ほどのSQLではIndexのアクセスパスとしてRID Lookup(図3)が発生していたのですが、Indexスキャン時にノードの値を見れば x列の値は解るので、Indexに無いy列の情報だけ探しに行けば早くなるのかな？という発想が出てきます。

(図3)RID Lookupの流れ

ならばと言う事で、クエリのselect句で問い合わせている列を全部Indexに持たせてしまえば RID Lookupは発生しないのでは？という考え方から生まれたIndexをカバリングインデックスと呼びます。但し、何も考えずただ単にSelect 文で使う列を入れた複数列インデックス(※)を作れば良いのかというと決してそうではなく、カバリングインデックスが使用されて実行計画が Index-only-scan となるためには一定の条件が必要です。

※商用DBで複合Index と言われているIndexのことです。PostgreSQLでは複数列インデックスと呼びます。

▽カバリングインデックスが使用される条件

Indexの種類が Index-only-scan をサポートしていること。
クエリはIndexに格納されている列だけを使用すること。

1.はPostgreSQLのサイトを確認してバージョンと型を確認してみてください。

→（参考）Indexの種類（PostgreSQL 11）

2.は簡単な話で、最初に書いたIndexは x列だけに設定されているので、y列にもIndexを定義すれば良いだけです。つまり以下のIndex を作成すれば良いです。

CREATE INDEX TEST1_x1 ON TEST1( x , y ) ;

「Indexに格納されている列だけを使用する」という条件はそのままの意味で、カバリングインデックスが使用されるパターンは以下の通りです。

[使用する]

SELECT x FROM TEST1 WHERE x = 3 ;

SELECT x , y FROM TEST1 WHERE x = 3 and y = 3 ;

[使用しない]

SELECT x , y , z FROM TEST1 WHERE x = 3 ;

SELECT x , y FROM TEST1 WHERE x = 3 and z = 3 ;

なんだ、カバリングインデックスって簡単じゃないか。これでクエリが早くなるな。とほっこりした方は詰めが甘い。では、もう少しPostgreSQLのアーキテクチャを覗いてみましょう。

DBは複数のユーザー、複数のトランザクションが同時に様々な処理を行います。それらのトランザクションの一貫性やデータとしての整合性を担保しているのはロック機構だけではありません。MVCC（MultiVersion Concurrency Control：多版型同時実行制御）と言って、それぞれのトランザクション毎にスナップショットを用意し、それに対して操作を行っています。トランザクションが終わったらトランザクション隔離レベルで定めたルールに従って他のスナップショットと帳尻を合わせるという制御方式が取られています。その処理においてもカバリングインデックスのアクセスパスの無駄を省くよう考えておかないといけません。

→（参考）MVCC（MultiVersion Concurrency Control：多版型同時実行制御）

それを踏まえて、カバリングインデックスが最速で動くための付随条件を考えてみましょう。

▽カバリングインデックスが最速になる条件

検索された各行が問い合わせのMVCCスナップショットに対して「可視」であること。（※）
複数列インデックスになる場合、複数列インデックスが効率の良い構成で作成されていること。
無駄なIndex がないこと。

※PostgreSQL以外で読み替えている方は１は考えなくても良いと思います。

１.検索された各行が問い合わせのMVCCスナップショットに対して「可視」であること。

PostgreSQLならではの仕様なので、少し説明します。PostgreSQLには可視性マップ（Visibility Map）という仕様があります。これはPostgreSQLが追記型アーキテクチャであるが故の仕様（例えばUPDATE文を実行すると元レコードを無効化したうえで、修正値で新しいレコードを追加するという仕様）のため、VACUUMする必要があるかどうかを判断するため、あるいはIndexを読んだ際にテーブルのレコード本体と一致しているか確認するために読みに行く必要があるかどうか、といった判断をする必要が有るために存在します。(図4)

(図4) 追記型アーキテクチャで起こる問題

可視性マップは、各ブロックのレコードの可視性状態を保持するファイルで、テーブルヒープの各ページ毎に可視/不可視の情報をビットマップで保持しています。(図5) 実態は拡張子 “_vm” のファイルで、ブロックをVACUUMする必要があるかどうかの判断とIndex-Only Scan（レコード本体を見ない）できるかどうかの判断に使います。

(図5) 可視性マップ（Visibility Map）の構造

全てのビットが立っていると、ブロック内の全タプルが全トランザクションに可視である（つまりIndexの情報と実データテーブルは一致している）事が解るので、テーブルファイルにアクセスしなくても、Indexで得たタプルのデータがそのまま使えることが解ります。(図6) パフォーマンス向上のためにも、可能な限りビットを立たせるようにしたほうが良いと思います。業務仕様で更新の多いテーブルでは他の施策を考えたほうが良いでしょう。

(図6) 可視性マップの使われ方

こういった無駄な動きをさせないようにするために、可視性マップのフラグを立ててください。というのが1.で言いたかったことです。実際のアクションとしては「VACUUM を実行する」で正解なのですが、” おまじない ” の様にするのではなく、内部動作を理解したうえで行うと「カバリングインデックスが有効になっているのに何故か遅い」という場面でも対処法が判るようになると思います。

2.複数列インデックスになる場合、複数列インデックスが効率の良い構成で作成されていること。

複数列インデックスの特性はPostgreSQLの公式マニュアルにも記載されていますが、定義列の順番が重要で一番最初（一番左）に列を最初に検査します。WHERE句で一番最初の列を使用しなかったりすると、プランナが Table Scan のほうが早いと判断してしまう傾向があるようなので、Index設計はプログラム設計のフェーズでしっかり吟味する必要があると思います。(図7)

(図7) 複数列インデックスの使われ方

一番賢い作り方としては、回答を返すだけ列（別にIndexとして機能しなくてもいい列）を末尾の列することだと公式マニュアルにも書いてあります。パフォーマンスを考慮したIndexの一例として書きましたが、上記を考慮してご自身の環境でもテーブル設計を見直しては如何でしょうか。

【付加列インデックス】

SQL serverではお馴染みの機能ですが、PostgreSQL 11より実装されました。Index scanだけでクエリの回答をしようという考え方はカバリングインデックスと同じですが、[Indexとして機能させたい列]と[回答を返す値だけを保持する列]とを分離させたIndexです。(図8)

(図8) 付加列インデックスの構造

作り方は簡単で[回答を返す値だけを保持する列]をINCLUDE句に指定すれば良いだけです。

[ サンプルDDL ]
CREATE INDEX TEST1_x1 ON TEST1( x ) INCLUDE( y , z );

[使用例]
SELECT x , y , z FROM TEST1 WHERE x = 3 ;

上記SQL を実行すると、TEST1_x1のリーフノードまで検索されて実データテーブルのアドレス”■”まで辿り着くのですが、そのリーフノードにInclude句で指定した列のアドレスではなく実値として格納されているので、可視性マップを見て全て「可視（ビットが立っている）」であれば、その値を使って結果を返します。 RID Lookupが発生せず高速なスキャンが実現できます。

でも万能なものなんてありません

INCLUDE句はPRIMARY KEYの制約として書くことも出来ます。便利ですが一定の制約もあります。INCLUDE句に含めるペイロード列に、インデックス型の最大サイズを超えるタプルを指定すると失敗します。また、INCLUDE句に列を含め過ぎてインデックスサイズが膨張すると、検索が遅くなるという元も子もない状態に陥ります。

また、カバリングインデックスの項でも触れましたが、Index scanの際に必ず可視性マップを確認しに行きますので、そこでフラグが立っていなければ、結局実データテーブルを見に行きますので Indexが肥大化している分、逆に遅くなる可能性があります。

複数列インデックスについても、4列を超えた複数列キー定義は問題があると公式サイトにも書いています。

しっかりと考えて欲しいこと

CRUDのRとCUDを分離した分散構成でもない限り、クエリの高速化は更新処理の速度劣化とトレードオフの関係です。ただ、更新処理はストレージアクセスを先送りできる特性が有るので、クエリの高速化に偏重する考え方でも決して間違いではないのですが、システムの特性を鑑みたバランスが一番大切だと思います。要はDBを理解した上でのプログラム設計の重要性に帰結するのではないでしょうか。

最後に

DBを中心としたパフォーマンスチューニングを10年程経験していますが、前職、前々職の現場で「ある日突然遅くなったのでチューニングしてほしい」という依頼を度々受けたことがあります。統計情報が劣化した？IndexかTableの断片化が激しい？Indexが過多？不足？色々と調べることはありますが、原因究明にDBだけを見ている開発者が多いと感じます。プログラム側でトランザクションの設計は適切ですか？そのコミットタイミングやコミット間隔は適切ですか？システム設計時の想定処理件数に対して、今の件数で処理するとどうなりますか？設計時に運用上のデータ逓増をどれだけ設計に取り入れていますか？という質問に閉口されてもチューニングを依頼された側としては困ってしまいます。

DB内で更新データがどのように流れているのか、どこで管理されているのかを知っていれば、特にコミットタイミングやコミット間隔が適切でない事態は防げるかもしれません。今回はIndexのお話でしたが、機会があればパフォーマンスの劣化を防ぐ設計の考え方についてお話しできればと思います。

パフォーマンスを考慮したIndex定義設計

【まずはIndexの基本をおさらい】

【Index ( B-Tree ) の基本的なアクセスパスについて】