PostgreSQL の高度なインデックス作成: 部分、カバリング、BRIN および GIN
1 列の B ツリー インデックスは、パフォーマンスの問題の 80% を解決します。ただし20％ 残りの場合は、より洗練されたツール、つまり「アクティブな」行のみにインデックスを付ける部分インデックスが必要です。 インデックスを 95% 削減し、カバリング インデックスによりヒープ テーブルへのアクセスが完全に排除されます。 時系列テーブル上の BRIN は、B ツリーよりも 100 分の 1 のスペースしか占有せず、GIN により次のことが可能になります。 B-Tree では実行できない全文検索と配列検索。このガイドでは 4 つのタイプすべてを説明します。
何を学ぶか
- 部分インデックス: 最も頻繁なクエリにとって重要な行のみにインデックスを付けます。
- INCLUDE によるインデックスのカバー: SELECT クエリでインデックスのみのスキャンを有効にする
- BRIN インデックス: 数百万行の時系列テーブル用
- GIN インデックス: 全文検索、配列包含、JSONB クエリ
- 各シナリオに適したインデックス タイプを選択する方法
部分インデックス: 重要なもののみをインデックスします
部分インデックスは、WHERE 条件を満たす行のみにインデックスを付けます。そしてそのテクニックは 少数の行が大多数の行を受け取るシナリオで最も強力です。 クエリの数: 「保留中」の注文、「未読」のメッセージ、「進行中」のタスク。
-- Scenario: tabella ordini con 50 milioni di righe
-- 99% degli ordini ha stato 'completato' o 'cancellato'
-- Le query operative riguardano solo gli ordini 'pendenti' (50.000 righe)
-- Indice normale: 50 milioni di righe -> grande, lento da mantenere
CREATE INDEX idx_ordini_stato ON ordini (stato);
-- Dimensione: ~2GB, aggiornato ad ogni INSERT/UPDATE/DELETE
-- Partial index: solo gli ordini pendenti (50.000 righe)
CREATE INDEX idx_ordini_pendenti ON ordini (creato_il)
WHERE stato = 'pendente';
-- Dimensione: ~2MB (1000x piu piccolo!)
-- PostgreSQL lo usa automaticamente per query con WHERE stato = 'pendente'
-- Query che usa il partial index
EXPLAIN ANALYZE
SELECT id, utente_id, totale
FROM ordini
WHERE stato = 'pendente'
AND creato_il < NOW() - INTERVAL '1 hour';
-- -> Index Scan using idx_ordini_pendenti (2MB di indice vs 2GB!)
部分的な一意の値の部分インデックス
-- Caso d'uso: un utente puo avere al massimo un indirizzo "primario"
-- ma puo avere infiniti indirizzi non primari
-- Indice UNIQUE parziale: vincola l'unicita solo dove e_primario = true
CREATE UNIQUE INDEX idx_indirizzi_primary_unico
ON indirizzi (utente_id)
WHERE e_primario = true;
-- Ora questo INSERT fallisce (utente 42 ha gia un indirizzo primario)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Roma 1', true); -- ERROR: duplicate key value
-- Ma questo e permesso (indirizzi non primari non hanno il vincolo)
INSERT INTO indirizzi (utente_id, via, e_primario)
VALUES (42, 'Via Milano 5', false); -- OK
-- Partial index per soft delete
CREATE INDEX idx_prodotti_attivi ON prodotti (categoria_id, prezzo)
WHERE deleted_at IS NULL;
-- Il 95% delle query filtra su deleted_at IS NULL
-- L'indice e 20x piu piccolo di uno su tutte le righe
INCLUDE によるインデックスのカバー: ヒープ アクセスの排除
インデックスのみのスキャンでは、ヒープ テーブルにアクセスせずに、インデックスからデータを直接読み取ります。
これは可能な限り最速のタイプのスキャンです。これを有効にするには、インデックスにすべての
SELECT に表示される列。条項 INCLUDE 「余分な」列を追加する
B ツリー構造にそれらを含めずにインデックスに追加します (したがって、パフォーマンスに影響を与えることはありません)
インデックス自体に対する INSERT/UPDATE の影響）。
-- Query frequente: lista ordini per utente con totale e data
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = $1
ORDER BY creato_il DESC;
-- Indice normale: solo la colonna di filtro
CREATE INDEX idx_ordini_utente ON ordini (utente_id, creato_il DESC);
-- Piano: Index Scan -> poi Heap Fetch per ogni riga (per leggere 'totale')
-- Performance: O(k * log N) dove k = righe per utente
-- Covering index: aggiunge 'totale' con INCLUDE
CREATE INDEX idx_ordini_utente_covering ON ordini (utente_id, creato_il DESC)
INCLUDE (totale);
-- Piano: Index Only Scan -> nessun Heap Fetch!
-- Performance: O(log N) + legge solo le pagine dell'indice
-- Verificare che usi Index Only Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, creato_il, totale
FROM ordini
WHERE utente_id = 42
ORDER BY creato_il DESC
LIMIT 50;
-- Output: "Index Only Scan using idx_ordini_utente_covering"
-- "Heap Fetches: 0" <- ottimo!
含む場合は役に立ちません
-- INCLUDE non aiuta se:
-- 1. La visibility map non e aggiornata (heap fetches necessari per MVCC)
-- Soluzione: VACUUM regolare
VACUUM VERBOSE ordini;
-- Verifica: "Index Only Scans: N" in pg_stat_user_indexes
-- 2. La tabella ha molti UPDATE non vacuumati
SELECT
relname,
n_dead_tup,
n_live_tup,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'ordini';
-- 3. Le colonne in INCLUDE sono troppo grandi (testo lungo, array, JSONB)
-- Il indice diventa enorme, peggio che un heap fetch
-- Regola pratica per INCLUDE:
-- - Colonne numeriche (int, bigint, numeric): OK
-- - Colonne brevi (varchar < 50 char): OK
-- - Colonne lunghe, JSONB, testo: meglio evitare
BRIN インデックス: 時系列テーブル用
BRIN (ブロック範囲インデックス) は、データが物理的に並べ替えられるテーブル用に設計されています。 列 (通常は挿入タイムスタンプ)。各値にインデックスを付ける代わりに、 BRIN は、ページの各ブロックの最小/最大のみを保存します。よりもはるかに小さいです B ツリーですが精度は低くなります。日付範囲のスキャンに最適です。
-- Tabella eventi IoT: 500 milioni di righe, inserite in ordine temporale
-- B-Tree su ts_evento: 15GB di indice
-- BRIN su ts_evento: 150KB di indice (100.000x piu piccolo!)
CREATE TABLE eventi_iot (
id BIGSERIAL,
device_id INT NOT NULL,
ts_evento TIMESTAMPTZ NOT NULL DEFAULT NOW(),
valore FLOAT8 NOT NULL,
metadata JSONB
) PARTITION BY RANGE (ts_evento);
-- Creare BRIN invece di B-Tree per la colonna temporale
CREATE INDEX idx_eventi_ts_brin ON eventi_iot USING BRIN (ts_evento)
WITH (pages_per_range = 128); -- default: 128 pagine per range
-- Query che sfrutta il BRIN
EXPLAIN ANALYZE
SELECT device_id, AVG(valore)
FROM eventi_iot
WHERE ts_evento BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY device_id;
-- "Bitmap Index Scan on idx_eventi_ts_brin"
-- "Recheck Cond: (ts_evento BETWEEN ...)"
-- BRIN su colonne fisicamente correlate (non necessariamente timestamp)
-- Esempio: log con ID auto-incrementale
CREATE INDEX idx_log_id_brin ON application_logs USING BRIN (log_id)
WITH (pages_per_range = 64);
BRIN と B-Tree: いつ選択するか
-- Confronto pratico su tabella da 500M righe:
--
-- | B-Tree | BRIN
-- ---------|--------------|--------
-- Dimensione| 15 GB | 150 KB
-- Build time| 45 min | 2 min
-- Scan (1 settimana su 1 anno) | 0.1s | 0.8s
-- INSERT overhead | alto | minimo
-- UPDATE overhead | alto | minimo
-- BRIN e ottimo quando:
-- 1. La tabella cresce per append (log, eventi, transazioni finanziarie)
-- 2. I dati sono fisicamente correlati con la colonna indicizzata
-- 3. Lo spazio disco e un vincolo
-- 4. Le query sono sempre su range (non su valori singoli)
-- BRIN NON funziona bene quando:
-- 1. Gli INSERT non sono in ordine (rows sparse = BRIN inutile)
-- 2. Le query cercano valori singoli precisi
-- 3. La tabella ha molti UPDATE/DELETE che rovinano la correlazione fisica
-- Misurare la correlazione fisica (valore tra -1 e 1, vicino a 1 = BRIN ottimo)
SELECT
attname,
correlation
FROM pg_stats
WHERE tablename = 'eventi_iot'
AND attname = 'ts_evento';
-- Se correlation > 0.9: BRIN e molto efficiente
-- Se correlation < 0.5: BRIN poco utile, meglio B-Tree
GIN インデックス: 全文検索、配列、JSONB
GIN (Generalized Inverted INdex) は、PostgreSQL の最も汎用性の高いインデックス タイプです。それにより、次のことが可能になります 複合コンテンツのインデックス — 配列、tsvectors、JSONB — コンテンツの各要素 個別にインデックスが付けられます。全文検索と配列クエリには必須です。
全文検索用の GIN
-- Full-text search su una tabella di articoli
-- Aggiungere una colonna tsvector calcolata (PostgreSQL 12+)
ALTER TABLE articoli ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('italian',
coalesce(titolo, '') || ' ' ||
coalesce(contenuto, '') || ' ' ||
coalesce(tags::text, '')
)
) STORED;
-- Creare l'indice GIN sulla colonna search_vector
CREATE INDEX idx_articoli_fts ON articoli USING GIN (search_vector);
-- Query full-text che usa il GIN
SELECT id, titolo, ts_rank(search_vector, query) AS rank
FROM articoli,
to_tsquery('italian', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- Con highlighting
SELECT
id,
titolo,
ts_headline('italian', contenuto, query,
'MaxFragments=3, MaxWords=30, MinWords=10') AS snippet
FROM articoli,
to_tsquery('italian', 'postgresql:* & (performance | ottimizzazione)') AS query
WHERE search_vector @@ query;
配列包含のための GIN
-- Tabella prodotti con array di tag
CREATE TABLE prodotti (
id BIGSERIAL PRIMARY KEY,
nome TEXT NOT NULL,
tags TEXT[] NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_prodotti_tags ON prodotti USING GIN (tags);
-- Query con operatori array su GIN (molto piu veloci senza GIN)
-- @>: "contiene tutti questi tag"
SELECT id, nome
FROM prodotti
WHERE tags @> ARRAY['postgresql', 'performance'];
-- &&: "ha almeno uno di questi tag"
SELECT id, nome
FROM prodotti
WHERE tags && ARRAY['database', 'sql', 'nosql'];
-- Verifica che il GIN sia usato
EXPLAIN ANALYZE
SELECT id, nome FROM prodotti
WHERE tags @> ARRAY['postgresql'];
-- "Bitmap Index Scan on idx_prodotti_tags"
JSONB の GIN
-- Indice GIN su colonna JSONB (indicizza ogni chiave/valore)
CREATE TABLE eventi (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL
);
-- GIN con gin_trgm_ops (trigrammi): per LIKE e ricerche simili
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- GIN con jsonb_path_ops (piu compatto ma solo per @> e @?)
CREATE INDEX idx_eventi_payload_path ON eventi
USING GIN (payload jsonb_path_ops);
-- GIN standard (supporta tutti gli operatori JSONB)
CREATE INDEX idx_eventi_payload ON eventi USING GIN (payload);
-- Query che usa il GIN JSONB
-- Trovare eventi con specifico tipo
EXPLAIN ANALYZE
SELECT id, payload->>'timestamp'
FROM eventi
WHERE payload @> '{"tipo": "acquisto", "canale": "mobile"}';
-- Differenza tra i due tipi GIN per JSONB:
-- jsonb_path_ops: 30-40% piu compatto, solo @> e jsonpath
-- default: supporta anche ?, ?|, ?&, @?, @@
-- Scegliere default se usi operatori di existence (?)
-- Scegliere jsonb_path_ops se usi solo @> (containment)
意思決定マップ: どのインデックスを選択するか
| シナリオ | インデックスの種類 | 理由 |
|---|---|---|
| PK または FK (等しい) に関するクエリ | B ツリー (デフォルト) | =、<、>、BETWEEN に最適 |
| 少数の「アクティブな」行のみ (保留中など) | 部分的な B ツリー | インデックスが 100 分の 1 に小さくなる |
| インデックス内のすべての列を使用した SELECT | INCLUDES を含む B ツリー | インデックスのみのスキャン、ヒープアクセスなし |
| タイムスタンプ付きの追加専用テーブル | ブリン | B-Tree よりも 1000 分の 1 小さい |
| イタリア語/英語の全文検索 | tsvector の GIN | @@ 演算子の唯一の方法 |
| 配列の包含 (@>、&&) | TEXT[] の GIN | 配列演算子の唯一の方法 |
| JSONB 包含 (@>) | GIN jsonb_path_ops | デフォルトより 30% コンパクト |
| テキストの「パターン%」にいいね！ | B ツリー (varchar_pattern_ops) | LIKE プレフィックスのみ |
| テキストの「%pattern%」のように | pg_trgm を使用した GIN | メディアで「いいね」をする唯一の方法 |
インデックスのメンテナンス
-- Identificare indici non usati (candidati alla rimozione)
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scansioni,
pg_size_pretty(pg_relation_size(indexrelid)) AS dimensione
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Identificare indici duplicati (stesso set di colonne)
SELECT
t.relname AS tabella,
array_agg(i.relname) AS indici_duplicati,
pg_size_pretty(sum(pg_relation_size(ix.indexrelid))) AS spazio_totale
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
GROUP BY t.relname, ix.indkey
HAVING count(*) > 1;
-- Rebuild di un indice senza bloccare (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_ordini_stato;
-- Monitorare lo spazio degli indici
SELECT
relname,
pg_size_pretty(pg_total_relation_size(oid)) AS totale,
pg_size_pretty(pg_relation_size(oid)) AS tabella,
pg_size_pretty(pg_indexes_size(oid)) AS indici
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;
結論
適切なインデックス タイプを選択すると、クエリが 10 秒続くかどうかが変わります。 もう 1 つは 10 ミリ秒続きます。部分インデックスとカバーインデックスは最適化です 運用クエリではより効果的です。 BRIN はログと時系列データにとって革新的です。 GIN は、他のタイプのインデックスでは不可能な検索の可能性を広げます。
このシリーズの次の記事では、テーブルのパーティショニング: 管理手法について説明します。 パーティションプルーニングとDROPを使用して、パフォーマンスを損なうことなく数十億行のテーブルを作成できます。 データライフサイクル管理のためのインスタントパーティション。
シリーズ: データベースのエンジニアリングと最適化
- 記事 1: PostgreSQL 17/18 - ニュースとパフォーマンス
- 記事 2: EXPLAIN ANALYZE - クエリ プランを読む
- 第3条（本）: 高度なインデックス作成 - 部分、カバリング、BRIN、GIN
- 第 4 条: テーブルのパーティショニング - 数十億行の管理
- 記事 5: 接続プーリング - PgBouncer と Pgpool-II