【PostgreSQL 総復習】7. インデックス

「完全攻略 PostgreSQL: 現場で使える最強データベース入門 」という書籍をベースに、自分なりに気になったことやよく使いそうな機能をまとめていきます。

作業リポジトリはこちら:

• 【PostgreSQL 総復習】1. psqlコマンドライン操作
• 【PostgreSQL 総復習】2. データベース・スキーマ・ユーザー
• 【PostgreSQL 総復習】3. データ型・CREATE TABLE
• 【PostgreSQL 総復習】4. SELECT・ウィンドウ関数・CTE (WITH句)
• 【PostgreSQL 総復習】5. INSERT・UPDATE・DELETE・RETURNING
• 【PostgreSQL 総復習】6. トランザクションとACID特性
• 【PostgreSQL 総復習】7. インデックス

インデックスの種類

• CREATE INDEX | PostgreSQL 17.6

B-Tree インデックス（デフォルト）

特に指定がない場合、PostgreSQLはこれを作成します。

仕組み

• データをソートされた木構造（バランス木）で保持します。
• ルートからリーフ（葉）へと辿ることで、対数時間 (logn)でデータに到達します。

特徴

• 等価比較 (=) だけでなく、範囲検索 (<, <=, >, >=) や ソート (ORDER BY) にも有効
• LIKE 'hoge%' のような前方一致検索にも利用可能

使いどころ

• ID、名前、日付など、一般的なカラム。
• ユニーク制約や主キー制約の裏側。

サンプル

-- テーブル作成
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    department TEXT
);

-- データ挿入
INSERT INTO employees (name, age, department) VALUES 
('Tanaka Atsushi', 25, 'Sales'),
('Tanaka Minami', 30, 'Engineering'),
('Suzuki Junko', 35, 'Sales'),
('Ishii Kazuhisa', 40, 'HR');

-- インデックス作成 （年齢に対するB-Tree）
CREATE INDEX idx_employees_age ON employees USING btree (age);
-- インデックス作成 （名前に対するB-Tree）
CREATE INDEX idx_employees_name ON employees USING btree (name);

-- 検索（30歳以上を検索：インデックスが使われる）
EXPLAIN ANALYZE SELECT * FROM employees WHERE age >= 30;
--                                               QUERY PLAN                                               
-- -------------------------------------------------------------------------------------------------------
--  Seq Scan on employees  (cost=0.00..1.05 rows=1 width=72) (actual time=0.011..0.011 rows=3.00 loops=1)
--    Filter: (age >= 30)
--    Rows Removed by Filter: 1
--    Buffers: shared hit=1
--  Planning:
--    Buffers: shared hit=16 read=5
--  Planning Time: 0.156 ms
--  Execution Time: 0.019 ms

-- 前方一致（効果的）
EXPLAIN ANALYZE SELECT * FROM employees WHERE name LIKE 'Tanaka%';
--                                               QUERY PLAN                                               
-- -------------------------------------------------------------------------------------------------------
--  Seq Scan on employees  (cost=0.00..1.05 rows=1 width=72) (actual time=0.007..0.008 rows=2.00 loops=1)
--    Filter: (name ~~ 'Tanaka%'::text)
--    Rows Removed by Filter: 2
--    Buffers: shared hit=1
--  Planning:
--    Buffers: shared hit=3 read=6
--  Planning Time: 0.171 ms
--  Execution Time: 0.016 ms

Hash インデックス

等価比較（完全一致）に特化したインデックスです。

仕組み

• ハッシュ関数を使って値を計算し、バケットにマッピングします。

特徴

• 等価比較 (=) のみサポート。範囲検索には使えません。
• データサイズが大きい場合、B-Treeよりサイズが小さくなることがあります。

使いどころ

• URLやUUIDなど、非常に長い文字列の「完全一致」のみを行う場合。

サンプルコード

-- テーブル作成
CREATE TABLE api_tokens (
  id SERIAL PRIMARY KEY,
  token TEXT
);

-- データ挿入
INSERT INTO api_tokens (token) VALUES 
('abc-123-def'),
('xyz-987-uvw'),
('long-random-string-value');

-- インデックス作成 （トークンの完全一致用）
CREATE INDEX idx_api_tokens_token ON api_tokens USING hash (token);

-- 検索（完全一致：効果あり）
EXPLAIN ANALYZE SELECT * FROM api_tokens WHERE token = 'xyz-987-uvw';
--                                                QUERY PLAN                                               
-- --------------------------------------------------------------------------------------------------------
--  Seq Scan on api_tokens  (cost=0.00..1.04 rows=1 width=36) (actual time=0.008..0.008 rows=1.00 loops=1)
--    Filter: (token = 'xyz-987-uvw'::text)
--    Rows Removed by Filter: 2
--    Buffers: shared hit=1
--  Planning:
--    Buffers: shared hit=19 read=4
--  Planning Time: 0.141 ms
--  Execution Time: 0.017 ms

GIN インデックス (Generalized Inverted Index)

「中身の要素」を検索するのに適しており、複合的な値（JSONB、配列、全文検索）に対して使われます

仕組み

• 転置インデックス（Inverted Index） です。ドキュメント内の各要素（単語や配列の要素）に対して、それを含む行IDのリストを保持します。

特徴

• 検索は非常に高速ですが、更新（INSERT/UPDATE） では一度に多数のインデックスエントリを書き換える必要があるため B-Treeよりコストが高い

使いどころ

• 全文検索 (tsvector, pg_bigm)。
• JSONB の特定のキーや値の検索。
• 配列 (ARRAY) の「含んでいるか」検索。

サンプル (JSONB検索)

-- テーブル作成
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- データ挿入
INSERT INTO products (data) VALUES 
('{"name": "T-Shirt", "colors": ["red", "blue"], "tags": ["summer", "casual"], "price": 3000, "size": ["S", "M", "L"]}'),
('{"name": "Jeans", "colors": ["blue", "black"], "tags": ["casual"], "brand": "Lee", "price": 45000, "size": ["M", "L"]}'),
('{"name": "Socks", "colors": ["white", "black"], "tags": ["casual"], "price": 500}'),
('{"name": "Coat", "colors": ["black"], "tags": ["winter", "formal"], "brand": "BURBERRY", "price": 110000}');

-- インデックス作成 （JSONB全体に対して）
CREATE INDEX idx_products_data ON products USING gin (data);

-- タグに "summer" を含む商品を検索
-- @> 演算子は「左辺が右辺を含むか」を表します
EXPLAIN ANALYZE SELECT * FROM products WHERE data @> '{"tags": ["summer"]}';
--                                               QUERY PLAN                                              
-- ------------------------------------------------------------------------------------------------------
--  Seq Scan on products  (cost=0.00..1.04 rows=1 width=36) (actual time=0.007..0.008 rows=1.00 loops=1)
--    Filter: (data @> '{"tags": ["summer"]}'::jsonb)
--    Rows Removed by Filter: 2
--    Buffers: shared hit=1
--  Planning:
--    Buffers: shared hit=21 read=1
--  Planning Time: 0.111 ms
--  Execution Time: 0.017 ms


-- "brand" というキーを持つ行を検索
-- ? 演算子は指定した文字列をキー(または配列の要素)として持つ行を検索します。
SELECT * FROM products WHERE data ? 'brand';
--  id |                                           data                                           
-- ----+------------------------------------------------------------------------------------------
--   2 | {"name": "Jeans", "tags": ["casual"], "brand": "Lee", "colors": ["blue", "black"]}
--   3 | {"name": "Coat", "tags": ["winter", "formal"], "brand": "BURBERRY", "colors": ["black"]}

-- 'brand' 'size' いずれかのキーを持つ行を検索
-- ?| 演算子は配列で指定した文字列のいずれかをキー(または配列の要素)として持つ行を検索します。
SELECT * FROM products WHERE data ?| ARRAY['brand', 'size'];
--  id |                                                          data                                                          
-- ----+------------------------------------------------------------------------------------------------------------------------
--   1 | {"name": "T-Shirt", "size": ["S", "M", "L"], "tags": ["summer", "casual"], "price": 3000, "colors": ["red", "blue"]}
--   2 | {"name": "Jeans", "size": ["M", "L"], "tags": ["casual"], "brand": "Lee", "price": 45000, "colors": ["blue", "black"]}
--   4 | {"name": "Coat", "tags": ["winter", "formal"], "brand": "BURBERRY", "price": 110000, "colors": ["black"]}

-- 'brand' 'size' 両方をキーに持つ行を検索
-- ?& 演算子は配列で指定した文字列の両方をキー(または配列の要素)として持つ行を検索します。
SELECT * FROM products WHERE data ?& ARRAY['brand', 'size'];
--  id |                                                          data                                                          
-- ----+------------------------------------------------------------------------------------------------------------------------
--   2 | {"name": "Jeans", "size": ["M", "L"], "tags": ["casual"], "brand": "Lee", "price": 45000, "colors": ["blue", "black"]}

-- "tags" 配列の中に "summer" が含まれるか（JSON Path記法）
-- JSONBにおける @@ 演算子は "jsonb @@ jsonpath" という形で利用し、指定したパスの条件が true になるかを返します
SELECT * FROM products WHERE data @@ '$.tags[*] == "summer"';
--  id |                                                         data                                                         
-- ----+----------------------------------------------------------------------------------------------------------------------
--   1 | {"name": "T-Shirt", "size": ["S", "M", "L"], "tags": ["summer", "casual"], "price": 3000, "colors": ["red", "blue"]}


-- "price" が 1000 より大きいデータ  (数値の比較もインデックスが効く場合があります)
-- JSONBにおける @@ 演算子は "jsonb @@ jsonpath" という形で利用し、指定したパスの条件が true になるかを返します
SELECT * FROM products WHERE data @@ '$.price > 10000';
--  id |                                                          data                                                          
-- ----+------------------------------------------------------------------------------------------------------------------------
--   2 | {"name": "Jeans", "size": ["M", "L"], "tags": ["casual"], "brand": "Lee", "price": 45000, "colors": ["blue", "black"]}
--   4 | {"name": "Coat", "tags": ["winter", "formal"], "brand": "BURBERRY", "price": 110000, "colors": ["black"]}

インデックスが効かない検索

-- × GINインデックスは効かない
SELECT * FROM products WHERE data->>'name' = 'T-Shirt';

-- × GINインデックスは効かない
SELECT * FROM products WHERE (data->>'price')::int > 1000;

サンプルコード (全文検索: 英語)

-- テーブル作成
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT ''
);

-- データ挿入
INSERT INTO articles (title, body) VALUES 
('Postgres Guide', 'PostgreSQL is a powerful relational database.'),
('Vector Search', 'Using pgvector for semantic search is popular.'),
('Database Indexing', 'Indexes improve query performance significantly.');

-- ★インデックス作成
-- to_tsvector関数でテキストをベクトル化してインデックス化します
-- || は文字列結合演算子
CREATE INDEX idx_body_search ON articles USING gin (to_tsvector('english', title || ' ' || body));

-- to_tsquery関数で検索語をクエリ化して、"search" という単語を含む記事を探す
-- インデックスを作ったときの式と、検索の時の式を合わせる必要があるため、左辺は to_tsvector('english', title || ' ' || body) が必要
-- @@ 演算子は全文検索を行います。tsvector(検索対象ドキュメント) と tsquery(検索したいキーワード) の2つのデータ型を比較し、tsqueryで指定したキーワードがtsvectorに含まれてるかを判定します。
SELECT * FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgresql');
--  id |     title      |                     body                      
-- ----+----------------+-----------------------------------------------
--   1 | Postgres Guide | PostgreSQL is a powerful relational database.

DROP TABLE articles;

検索専用カラムを作ると検索クエリがシンプルになります

-- テーブル作成
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',

  -- 検索専用カラム tsv を定義
  tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED
);


-- データ挿入
INSERT INTO articles (title, body) VALUES 
('Postgres Guide', 'PostgreSQL is a powerful relational database.'),
('Vector Search', 'Using pgvector for semantic search is popular.'),
('Database Indexing', 'Indexes improve query performance significantly.');

-- 検索用のカラムにインデックスを貼る
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 検索クエリもシンプルになるし
SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'postgresql');
--  id |     title      |                     body                      |                                tsv                                 
-- ----+----------------+-----------------------------------------------+--------------------------------------------------------------------
--   1 | Postgres Guide | PostgreSQL is a powerful relational database. | 'databas':8 'guid':2 'postgr':1 'postgresql':3 'power':6 'relat':7

DROP TABLE articles;

サンプルコード (全文検索: 日本語)

pg_bigm は別途インストールが必要なので pg_trgm を使う

-- 拡張機能の有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- テーブル作成
CREATE TABLE jp_articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',

  -- 検索用のカラムを作成
  search_text TEXT GENERATED ALWAYS AS (title || ' ' || body) STORED
);

-- データ挿入
INSERT INTO jp_articles (title, body) VALUES 
('PostgreSQLの日本語検索', 'pg_bigmを使うと、辞書メンテナンスなしで高精度な検索が可能です。'),
('東京都の観光名所', '東京スカイツリーは、東京都墨田区にある電波塔です。'),
('最新のAI技術', 'RAG（検索拡張生成）やVector Searchが急速に普及しています。'),
('京都の文化', '京都府は歴史的な建造物が多く、観光客に人気です。');


-- インデックス作成 (演算子クラスにgin_trgm_opsを指定)
-- gin_trgm_ops は "演算子クラス" といい、「データ型(TEXT)」と「インデックス(gin)」をつなぐアダプタです。
-- GINは何も指定しないとTEXTをどのようにインデックスにすればいいかわかりません。
-- gin_trgm_opsという演算子クラスを指定することで "文字を3文字ずつバラバラにして管理する" という事がわかるようになります。
CREATE INDEX idx_jp_articles_search ON jp_articles USING gin (search_text gin_trgm_ops);

-- LIKE検索でインデックスが効きます
SELECT * FROM jp_articles WHERE search_text LIKE '%スカイツリー%';
--  id |      title       |                        body                        |                             search_text                             
-- ----+------------------+----------------------------------------------------+---------------------------------------------------------------------
--   2 | 東京都の観光名所 | 東京スカイツリーは、東京都墨田区にある電波塔です。 | 東京都の観光名所 東京スカイツリーは、東京都墨田区にある電波塔です。

---

GiST インデックス (Generalized Search Tree)

「重なり」や「近さ」を扱うのに適した、柔軟なインデックス構造です。

仕組み

• B-Treeのような厳密な順序ではなく、データをある枠（バウンディングボックスなど）に分類して木構造を作ります。

特徴:**

• 幾何データ型（位置情報）や範囲型（Range types）に特化しています。
• 全文検索にも使えますが、検索速度はGINに劣ります（ただし更新速度はGiSTの方が速い傾向があります）。

使いどころ

• PostGIS を使った地理情報（「この地点から半径5km以内」など）。
• 期間の重複チェック（「予約時間が重なっていないか」）。
• 最近傍探索（Nearest Neighbor Search）。

サンプルコード

-- テーブル作成
CREATE TABLE shops (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location POINT -- 緯度経度のような座標
);

-- データ挿入 (x, y)
INSERT INTO shops (name, location) VALUES 
('Shop A', POINT(10, 10)),
('Shop B', POINT(20, 20)),
('Shop C', POINT(100, 100));

-- ★インデックス作成
CREATE INDEX idx_shops_loc ON shops USING gist (location);

-- 検索（座標(0,0)から(15,15)の四角形エリアに含まれる店を探す）
-- <@ は「含まれる」演算子、boxはエリア定義
SELECT * FROM shops WHERE location <@ box(point(0,0), point(15,15));
--  id |  name  | location 
-- ----+--------+----------
--   1 | Shop A | (10,10)

サンプルコード (PostGIS)

-- 拡張機能(PostGIS)の有効化
CREATE EXTENSION IF NOT EXISTS postgis;


-- テーブル作成
-- GEOGRAPHY型 は地球の丸みを考慮した計算を行う型で距離をメートルで扱える
--   POINT: 点のデータ
--   4326: GPSで使われる世界測地系(WGS84) のIDコード
CREATE TABLE convenience_stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    -- 緯度経度を持つカラム (SRID 4326 = GPS標準)
    location GEOGRAPHY(POINT, 4326)
);


-- 東京駅周辺のダミーデータを挿入
INSERT INTO convenience_stores (name, location) VALUES 
-- 東京駅 (中心)
('セブン-イレブン 東京駅店', ST_SetSRID(ST_MakePoint(139.767125, 35.681236), 4326)),
-- 有楽町 (近い)
('ローソン 有楽町店',       ST_SetSRID(ST_MakePoint(139.763000, 35.675000), 4326)),
-- 秋葉原 (少し離れている)
('ファミマ 秋葉原店',       ST_SetSRID(ST_MakePoint(139.774000, 35.698000), 4326)),
-- 新宿 (遠い)
('ミニストップ 新宿店',     ST_SetSRID(ST_MakePoint(139.700000, 35.689000), 4326));


-- 位置情報カラムにGiSTインデックスを作成
CREATE INDEX idx_stores_location ON convenience_stores USING gist (location);


-- 半径検索（Radius Search）
-- 東京駅(139.767, 35.681)から2000m以内の店舗
SELECT
  name,
  -- 距離も表示（メートル）
  ST_Distance(location, ST_MakePoint(139.767125, 35.681236)::geography) AS distance_m
FROM
  convenience_stores
WHERE
  ST_DWithin(
    location,
    ST_MakePoint(139.767125, 35.681236)::geography,
    2000 -- 2000メートル
  )
ORDER BY
  distance_m;
--            name           |  distance_m   
-- --------------------------+---------------
--  セブン-イレブン 東京駅店 |             0
--  ローソン 有楽町店        |  786.24457073
--  ファミマ 秋葉原店        | 1961.35852331

-- 近傍探索（KNN: K-Nearest Neighbors）
-- 現在地から 近い順に3件 取得する
SELECT
  name,
  ST_Distance(location, ST_MakePoint(139.767125, 35.681236)::geography) AS distance_m
FROM
  convenience_stores
ORDER BY
  -- 距離演算子（近い順にソート）
  location <-> ST_MakePoint(139.767125, 35.681236)::geography
LIMIT 3;
--            name           |  distance_m   
-- --------------------------+---------------
--  セブン-イレブン 東京駅店 |             0
--  ローソン 有楽町店        |  786.24457073
--  ファミマ 秋葉原店        | 1961.35852331

1. データを作る (MakePoint & SetSRID)
   「経度139度、緯度35度」というただの数字を、PostGISが理解できる形式に変換します。
   • ST_SetSRID(ST_MakePoint(139.76, 35.68), 4326) (経度(x), 緯度(y) の順)
     • ST_MakePoint(x, y) : 点を作る
     • ST_SetSRID(..., 4326) : それに「WGS84（GPSの世界標準）」というタグを付ける
2. 検索する (ST_DWithin)
   「半径2km以内のお店」を探すときなどに使います。
   • WHERE ST_DWithin(location, ST_SetSRID(...), 2000)
     • ST_Distance(a, b) < 2000 と書いてしまうとインデックスが使われないため非常に遅い
3. 表示する (ST_Distance)
   結果として「ここから何メートル」と表示したり、「近い順」に並べるときに使います。
   • SELECT ST_Distance(location, ST_SetSRID(...)) AS meter
   • ORDER BY meter ASC

BRIN インデックス (Block Range INdex)

超巨大なテーブルに対して、非常に小さなサイズで作成できるインデックスです。

仕組み

• テーブルの物理的なブロック（ページ）ごとに、値の最小値と最大値だけを記録します。
• 「このブロックには目的の値が含まれる可能性があるか？」を大まかに判定し、不要なブロックを読み飛ばします。

特徴

• インデックスサイズが極端に小さい（B-Treeの数百分の一になることも）。
• データが物理的に整列している（相関がある）場合にのみ有効です。

使いどころ

• 時系列のログデータ（タイムスタンプ順にINSERTされ続ける数億行のテーブルなど）。

サンプルコード

-- テーブル作成
CREATE TABLE sensor_logs (
  id SERIAL PRIMARY KEY,
  log_time TIMESTAMP,
  value NUMERIC
);

-- データ挿入（大量データを想定：時系列順に並んでいることが重要）
INSERT INTO sensor_logs (log_time, value)
SELECT 
    generate_series('2024-01-01 00:00:00'::timestamp, '2024-01-31 23:59:00'::timestamp, '1 minute'),
    random() * 100;

-- ★インデックス作成
CREATE INDEX idx_sensor_logs_time ON sensor_logs USING brin (log_time);

-- 検索（ある期間のデータを範囲検索）
SELECT * FROM sensor_logs WHERE log_time BETWEEN '2024-01-15 00:00:00' AND '2024-01-15 01:00:00';
--   id   |      log_time       |      value       
-- -------+---------------------+------------------
--  20161 | 2024-01-15 00:00:00 | 58.3859916689804
--  20162 | 2024-01-15 00:01:00 | 41.2963241504118
--  ...

HNSW (Hierarchical Navigable Small World)

HNSWは、「階層型（Hierarchical）」の「グラフ構造」を作ってデータを管理します。

仕組み

階層構造 (Hierarchical)

データ（ノード）は複数のレイヤーに分かれて配置されます。

• 最上層（Layer N）: データがまばらに存在します。ここにあるノード同士は「長距離リンク」で繋がっています（＝特急電車や飛行機）。
• 中間層: 少しデータが増えます。中距離のリンクがあります（＝快速電車）。
• 最下層（Layer 0）: 全てのデータが存在します。隣同士が細かく繋がっています（＝各駅停車・徒歩）。

検索の流れ (Navigable Small World)

検索したいベクトル（クエリ）が来たら、上から順に降りていきます。

• 最上層でズームイン :まず、最上層の少ないデータの中から、クエリに一番近い点を探します。「大体この辺だな」というエリアを特定します。
• 下の層へ降りる :特定したエリアを起点に、下の層へ降ります。
• 詳細な探索 :層が下がるごとにデータが密になるので、より近い点を貪欲（Greedy）に辿っていきます。
• 最下層でゴール :最終的にLayer 0で、最も近いデータ（近傍点）を特定します。

この「大まかな位置から徐々に詳細へ」というアプローチにより、全データをスキャンすることなく、対数時間（$O(\log n)$）での検索が可能になります。

特徴

• グラフを辿るだけ（$O(\log n)$）なので、検索速度が早い
• 近似検索ですが、ほぼ正確な答えを返す
• どの点とどの点が繋がっているか」というグラフ（エッジ）の情報を持つため、大量のメモリが必要
• データを1件追加するたびに「どこに繋げるか」を計算してグラフを更新するため、大量データのインポート時は時間がかかる

使い所

• RAG
• リアルタイムレコメンデーション (この商品を見ている人はこれも、、、)
• 画像・動画検索 (類似の画像をリアルタイムに探す)

サンプル

-- 1. 拡張機能の有効化
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. テーブル作成
-- RAGでよくある「ドキュメントのチャンク」を想定
CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    content TEXT,                -- テキスト本文
    embedding VECTOR(3)          -- 3次元ベクトル（実際は1536次元などが一般的）
);

-- 3. データ挿入
-- わかりやすく3つのグループ（カテゴリ）っぽいデータを入れています
INSERT INTO document_chunks (content, embedding) VALUES 
-- グループA: [1, 0, 0] 近辺
('犬についての記事', '[0.9, 0.1, 0]'),
('猫の飼い方',       '[0.8, 0.2, 0]'),
('ペットフード',     '[0.9, 0.0, 0.1]'),

-- グループB: [0, 1, 0] 近辺
('最新のiPhone',     '[0, 0.9, 0.1]'),
('Androidスマホ',    '[0.1, 0.8, 0]'),
('充電器の選び方',   '[0, 0.9, 0]'),

-- グループC: [0, 0, 1] 近辺
('美味しいラーメン', '[0, 0.1, 0.9]'),
('パスタのレシピ',   '[0.1, 0, 0.8]'),
('カレーライス',     '[0, 0, 0.9]');

-- 4. ★ HNSWインデックスの作成
-- vector_cosine_ops: コサイン類似度用の演算子クラス
-- m: 各ノードが持つリンクの最大数（デフォルト16。大きいと精度UP・メモリ増）
-- ef_construction: インデックス構築時の探索深さ（デフォルト64。大きいと精度UP・構築遅）
CREATE INDEX idx_chunks_hnsw 
ON document_chunks 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

-- 5. 検索実行
-- 「犬([1,0,0])」に近い情報を探す
-- <=> はコサイン距離演算子（値が小さいほど似ている）
EXPLAIN ANALYZE
SELECT id, content, embedding <=> '[1, 0, 0]' as distance
FROM document_chunks
ORDER BY embedding <=> '[1, 0, 0]'
LIMIT 3;
--  id |     content      |       distance       
-- ----+------------------+----------------------
--   1 | 犬についての記事 | 0.006116251198662548
--   3 | ペットフード     | 0.006116251198662548
--   2 | 猫の飼い方       |  0.02985753301900307

演算子の選び方

距離の種類	演算子	Opsクラス (インデックス用)	用途
コサイン距離	<=>	vector_cosine_ops	RAG・文章検索の基本。 方向の近さを重視。
ユークリッド距離	<->	vector_l2_ops	物理的な距離。画像検索などで使われることが多い。
内積	<#>	vector_ip_ops	行列計算など。正規化済みならコサインと同じ挙動で最速。

パフォーマンスチューニング

HNSWは「近似検索」なので、検索時 にパラメータを調整することで、「速度」と「精度」のバランスを変えることができます。

クエリを実行する直前に SET コマンドで行います。

-- 検索時の探索深さを設定（デフォルトは40）
-- 値を大きくすると：検索が少し遅くなるが、取りこぼしが減る（精度UP）
-- 値を小さくすると：爆速になるが、たまに近いデータを見逃す
SET hnsw.ef_search = 100;

-- その状態で検索
SELECT id, content, embedding <=> '[0, 1, 0]' as distance
FROM document_chunks 
ORDER BY embedding <=> '[0, 1, 0]'
LIMIT 3;
--  id |    content     |       distance       
-- ----+----------------+----------------------
--   6 | 充電器の選び方 |                    0
--   4 | 最新のiPhone   | 0.006116251198662548
--   5 | Androidスマホ  | 0.007722135797640806

比較まとめ

インデックス	主な用途	得意な演算子	特記
B-Tree	汎用 (90%はこれ)	=, <, >, BETWEEN, LIKE 'x%'	デフォルト。迷ったらこれ。
Hash	完全一致のみ	=	サイズ効率が良い場合があるが用途限定。
GIN	複合値・全文検索	@> (JSONB/配列), 全文検索	検索は爆速だが更新が重い。
GiST	地理情報・範囲	&&, <-> (距離), 重なり	PostGISで必須。
BRIN	超巨大な時系列データ	=, <, >	データが物理的にソートされている必要あり。
HNSW	ベクトル検索	<=>, <->, <#>

---

応用テクニック

インデックスの種類だけでなく、作り方も重要です。

1. マルチカラムインデックス (複合インデックス)

• CREATE INDEX ON table (a, b);
• 左側のカラム (a) が検索条件に含まれていないと、効果が薄い（または使われない）場合があります。

1. 部分インデックス (Partial Index)

• CREATE INDEX ... WHERE status = 'active';
• 特定の条件を満たす行だけにインデックスを張ります。インデックスサイズを削減できます。

1. 式インデックス (Expression Index)

• CREATE INDEX ON users (LOWER(email));
• 関数を通した結果に対してインデックスを張れます。WHERE LOWER(email) = '...' のような検索を高速化します。

1. カバリングインデックス (Index Only Scan)

• CREATE INDEX ... INCLUDE (col_c);
• 検索結果に必要なカラムをインデックスのペイロードに含めることで、テーブル本体へのアクセス（Heap Access）を回避し、高速化します。