postgresql インデックスメモ

はじめに

自分用の学習メモです

indexとは

テーブル内のデータの目次みたいなものです。
目的を持って作成しておくことで、データへのアクセスを高速にすることができます。

indexの種類

• B-tree

• Hash

• GiST

• SP-GiST

• GIN

• BRIN

CREATE INDEXコマンドは最も一般的な状況に適したB-treeインデックスが作成される

B-treeインデックスとは

値の「順序」を利用して目的の場所へ辿るためのインデックス

例：fruits.idにB-treeインデックスがあるとする

作られる「目次（B-tree）」の構造

目次は階層構造になっており、数値を比較して進む方向を案内してくれる。

・一番上のページ

40未満なら左へ / 40以上なら右へ

・中間のページ

左の道： 20未満なら左 / 20以上なら右

右の道： 60未満なら左 / 60以上なら右

一番下のページ（リーフ = 実際の目次データ）

実際のIDと、テーブル上のデータが置いてある場所（ポインタ）が順番に並んでる。

探索の仕方（例：ID = 50 (Lemon) を探す場合）

B-treeは上から順番に条件を判定しながら、一発で目的の場所に絞り込んでいく。

1. ルート（頂点）を見る

2. 「50」は 40 以上なので、右の道に進む。

3. ブランチ（中間）を見る

4. 「50」は 60 未満なので、左の道に進む。

5. リーフ（一番下）に着く

ここには 40, 50 のインデックス情報が入っている。「ID = 50」を発見！

そこに書かれている「実際のデータ場所」へ跳んで Lemon を取得する。

フルスキャンだったら7件のデータを全て見ることになるのに、indexを使うと確認するデータの数が減らせて高速になる。

この左右の道は誰が定義するんだ？

B-treeの左右の道は、インデックスを作る時にデータベース（DBMS）がデータを自動でソート（並べ替え）し、バランスよくノード（ページ）に分割することで定義される。

人間があらかじめルールを設定するのではなく、DBエンジンが自動で計算して作る。

データの数が変わっても都度最適化してくれる。

検索クエリの場合

完全一致の検索、前方一致の検索はB-treeインデックスで問題なし

部分一致、後方一致の検索の場合はB-treeは使えない。

B-treeは先頭から順に比較するアルゴリズムのため、先頭から順にみるだけではヒットする条件にならないパターンでは対応できない。

じゃあどうする、、？？？？

GINをつーかう！！！！！💪

GINインデックス

GINは値から行を逆引きする転置インデックスになっていて、行内のどの位置にその値があってもヒットさせることができる。

そのため後方や中間の文字列検索にも適用可

B-tree

[B-treeインデックス] (ID列)
ID: 10 → 行1
ID: 20 → 行2
ID: 30 → 行3

GIN

[GINインデックス] （Name列の文字単位）
"a" → [行1, 行2]        (Apple, Banana)
"b" → [行2]             (Banana)
"e" → [行1, 行3]        (Apple, Lemon)
"l" → [行1, 行3]        (Apple, Lemon)
"m" → [行3]             (Lemon)
"n" → [行2, 行3]        (Banana, Lemon)
"o" → [行3]             (Lemon)
"p" → [行1]             (Apple)

GINは、キーから該当する行を逆引きする転置インデックス。

ただし、GIN自体が文字列を分解するわけではない。文字列の分解方法は、組み合わせる演算子クラスによって決まる。

PostgreSQLでは、部分一致検索を高速化する方法としてpg_trgmを利用できる。
pg_trgmは文字列を連続する3文字（trigram）に分解する。分割する文字数は3文字固定で、人間が自由に指定するものではない。

一旦ここまで

バックエンド掘り下げるとインフラまで行くし沼る、、、
学生の頃(古の話)に習ったアルゴリズムがここで初めて繋がってほんの少しだけ感動した。

インデックスは比較的とっつきやすいパフォーマンスチューニングの手段だと思うから内部で何が起きてるか理解して最適な選択していきたい。

AIのサポートがあれば、「このインデックスはこういう仕組みだから、この検索に向いている」くらいは多分パッと記憶して説明くらいはできる。
でも、この辺のインデックスによりどんなアルゴリズムの違いがあるか仕組みを理解してないと「じゃあ、なんでほかのインデックスじゃダメなん？」「それぞれ何が違うん？」って掘り下げて聞かれた時にはきっと答えられない。
AIが提案したものをそのまま使うんじゃなくて、比較したうえで最適な選択ができるようになりたい。

やっぱ私はバックエンドの方が好きだ〜〜