2019😺の25日目の記事です。中心に触っていました。言葉を借りて説明している箇所が有ります。中心としたパフォーマンス関連のお話をしたいと思います。商用DBと比較してしまうとOSS-DBに無い機能が多かったりするのですが、近年はパーティショニングやIndex機能の充実化など、溝を埋めていこうとする姿に感銘を受けました。DBを使う側も実装されたものを使いこなせる様になろうと思います。serverで同機能の呼び名です。機能の詳細は後述しますが、PostgreSQLでは同機能に名前が無いので、この記事では便宜上この機能をPostgreSQLでも「付加列Index」と書いていきます。今回のお話は「Indexって何だろう?」を卒業したところをスタートラインとしています。Indexについておさらいしたい方は以下のサイトを読んでみては如何でしょうか。Indexを簡単に言うと「検索を早くするためのもの」です。基本的なアクセスパスについて】まず最初に、B-Tree型Indexの基本的なアクセスパスについて以下のオブジェクトを使って説明します。serverに読み替えて読んでいる方は、非クラスタ化インデックスを作成したと想定してください。※データは入っている前提でお話します。作成するテーブルのイメージこのテーブルに対して以下のクエリを発行したとします。;この場合、以下の流れになります。行を探して、実データテーブルのアドレスを特定します。2)そのアドレスを使って実際のテーブル行からデータを取得します。Lookupと呼んで説明をしていきます。Lookupが発生すると余計な処理が発生して遅くなると思ってください。x列の値は解るので、Indexに無いy列の情報だけ探しに行けば早くなるのかな?という発想が出てきます。Lookupは発生しないのでは?という考え方から生まれたIndexをカバリングインデックスと呼びます。なるためには一定の条件が必要です。言われているIndexのことです。PostgreSQLでは複数列インデックスと呼びます。サポートしていること。クエリはIndexに格納されている列だけを使用すること。1.はPostgreSQLのサイトを確認してバージョンと型を確認してみてください。x列だけに設定されているので、y列にもIndexを定義すれば良いだけです。;「Indexに格納されている列だけを使用する」という条件はそのままの意味で、カバリングインデックスが使用されるパターンは以下の通りです。;なんだ、カバリングインデックスって簡単じゃないか。これでクエリが早くなるな。PostgreSQLのアーキテクチャを覗いてみましょう。DBは複数のユーザー、複数のトランザクションが同時に様々な処理を行います。それらのトランザクションの一貫性やデータとしての整合性を担保しているのはロック機構だけではありません。)と言って、それぞれのトランザクション毎にスナップショットを用意し、それに対して操作を行っています。トランザクションが終わったらトランザクション隔離レベルで定めたルールに従って他のスナップショットと帳尻を合わせるという制御方式が取られています。その処理においてもカバリングインデックスのアクセスパスの無駄を省くよう考えておかないといけません。)それを踏まえて、カバリングインデックスが最速で動くための付随条件を考えてみましょう。検索された各行が問い合わせのMVCCスナップショットに対して「可視」であること。効率の良い構成で作成されていること。※PostgreSQL以外で読み替えている方は1は考えなくても良いと思います。1.検索された各行が問い合わせのMVCCスナップショットに対して「可視」であること。PostgreSQLならではの仕様なので、少し説明します。Map)という仕様があります。これはPostgreSQLが追記型アーキテクチャであるが故の仕様(例えばUPDATE文を実行すると元レコードを無効化したうえで、修正値で新しいレコードを追加するという仕様)のため、VACUUMする必要があるかどうかを判断するため、あるいはIndexを読んだ際にテーブルのレコード本体と一致しているか確認するために読みに行く必要があるかどうか、といった判断をする必要が有るために存在します。可視性マップは、各ブロックのレコードの可視性状態を保持するファイルで、テーブルヒープの各ページ毎に可視/不可視の情報をビットマップで保持しています。Scan(レコード本体を見ない)できるかどうかの判断に使います。全てのビットが立っていると、ブロック内の全タプルが全トランザクションに可視である(つまりIndexの情報と実データテーブルは一致している)事が解るので、テーブルファイルにアクセスしなくても、Indexで得たタプルのデータがそのまま使えることが解ります。パフォーマンス向上のためにも、可能な限りビットを立たせるようにしたほうが良いと思います。業務仕様で更新の多いテーブルでは他の施策を考えたほうが良いでしょう。可視性マップの使われ方こういった無駄な動きをさせないようにするために、可視性マップのフラグを立ててください。いうのが1.で言いたかったことです。様にするのではなく、内部動作を理解したうえで行うと「カバリングインデックスが有効になっているのに何故か遅い」という場面でも対処法が判るようになると思います。効率の良い構成で作成されていること。複数列インデックスの特性はPostgreSQLの公式マニュアルにも記載されていますが、定義列の順番が重要で一番最初(一番左)に列を最初に検査します。ほうが早いと判断してしまう傾向があるようなので、Index設計はプログラム設計のフェーズでしっかり吟味する必要があると思います。賢い作り方としては、回答を返すだけ列(別にIndexとして機能しなくてもいい列)を末尾の列することだと公式マニュアルにも書いてあります。パフォーマンスを考慮したIndexの一例として書きましたが、上記を考慮してご自身の環境でもテーブル設計を見直しては如何でしょうか。11より実装されました。scanだけでクエリの回答をしようという考え方はカバリングインデックスと同じですが、[Indexとして機能させたい列]と[回答を返す値だけを保持する列]とを分離させたIndexです。作り方は簡単で[回答を返す値だけを保持する列]をINCLUDE句に指定すれば良いだけです。実行すると、TEST1_x1のリーフノードまで検索されて実データテーブルのアドレス”■”まで辿り着くのですが、そのリーフノードにInclude句で指定した列のアドレスではなく実値として格納されているので、可視性マップを見て全て「可視(ビットが立っている)」であれば、その値を使って結果を返します。Lookupが発生せず高速なスキャンが実現できます。KEYの制約として書くことも出来ます。便利ですが一定の制約もあります。INCLUDE句に含めるペイロード列に、インデックス型の最大サイズを超えるタプルを指定すると失敗します。INCLUDE句に列を含め過ぎてインデックスサイズが膨張すると、検索が遅くなるという元も子もない状態に陥ります。Indexが肥大化している分、逆に遅くなる可能性があります。複数列インデックスについても、4列を超えた複数列キー定義は問題があると公式サイトにも書いています。考えて欲しいことCRUDのRとCUDを分離した分散構成でもない限り、クエリの高速化は更新処理の速度劣化とトレードオフの関係です。更新処理はストレージアクセスを先送りできる特性が有るので、クエリの高速化に偏重する考え方でも決して間違いではないのですが、システムの特性を鑑みたバランスが一番大切だと思います。要はDBを理解した上でのプログラム設計の重要性に帰結するのではないでしょうか。最後にDBを中心としたパフォーマンスチューニングを10年程経験していますが、前職、前々職の現場で「ある日突然遅くなったのでチューニングしてほしい」という依頼を度々受けたことがあります。統計情報が劣化した?IndexかTableの断片化が激しい?Indexが過多?不足?色々と調べることはありますが、原因究明にDBだけを見ている開発者が多いと感じます。プログラム側でトランザクションの設計は適切ですか?そのコミットタイミングやコミット間隔は適切ですか?システム設計時の想定処理件数に対して、今の件数で処理するとどうなりますか?設計時に運用上のデータ逓増をどれだけ設計に取り入れていますか?という質問に閉口されてもチューニングを依頼された側としては困ってしまいます。DB内で更新データがどのように流れているのか、どこで管理されているのかを知っていれば、特にコミットタイミングやコミット間隔が適切でない事態は防げるかもしれません。今回はIndexのお話でしたが、機会があればパフォーマンスの劣化を防ぐ設計の考え方についてお話しできればと思います。Codeのフォーマッタを自分好みに設定してみる。