Pokročilé indexování PostgreSQL: Částečné, Krycí, BRIN a GIN
Jednosloupcový index B-Strom je řešením 80 % problémů s výkonem. ale 20% zbývající vyžaduje sofistikovanější nástroje: částečný index, který indexuje pouze „aktivní“ řádky snižuje index o 95 %, krycí index zcela eliminuje přístupy k tabulce haldy, BRIN na stole časové řady zabírá 100krát méně místa než B-strom a GIN umožňuje fulltextové a pole prohledávání, které žádný B-Strom neumí. Tato příručka pokrývá všechny čtyři typy.
Co se naučíte
- Částečný index: Indexujte pouze řádky, které jsou důležité pro nejčastější dotazy
- Krytí indexu pomocí INCLUDE: povolí skenování pouze indexu na SELECT dotazy
- BRIN Index: pro tabulky časových řad s miliony řádků
- GIN Index: fulltextové vyhledávání, obsah pole a dotazy JSONB
- Jak vybrat správný typ indexu pro každý scénář
Částečný index: Indexujte pouze to, na čem záleží
Částečný index indexuje pouze řádky, které splňují podmínku WHERE. A technika nejvýkonnější pro scénáře, kde malé procento řádků obdrží velkou většinu dotazů: "nevyřízené" objednávky, "nepřečtené" zprávy, "probíhající" úkoly.
-- 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!)
Částečný index pro částečné jedinečné hodnoty
-- 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
Krycí index s INCLUDE: Eliminujte přístup k haldě
Prohledávání pouze indexu čte data přímo z indexu bez přístupu k tabulce haldy.
Je to nejrychlejší možný typ skenování. Aby to bylo možné, musí index obsahovat všechny
sloupce, které se objeví v SELECT. Doložka INCLUDE přidat sloupce "navíc".
v indexu bez jejich zahrnutí do struktury B-stromu (tedy bez dopadu na výkon
z INSERT/UPDATE na samotném indexu).
-- 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!
Když to OBSAHUJE, nepomůže to
-- 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 Index: Pro tabulky časových řad
BRIN (Block Range INdex) je určen pro tabulky, kde jsou data fyzicky tříděna sloupec (obvykle časové razítko vložení). Namísto indexování každé hodnoty BRIN ukládá pouze min/max pro každý blok stránek. Je enormně menší než a B-Strom, ale méně přesný: ideální pro skenování období.
-- 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 vs B-Strom: Kdy si vybrat
-- 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
Index GIN: Fulltextové vyhledávání, pole a JSONB
GIN (Generalized Inverted INdex) je nejuniverzálnější typ indexu PostgreSQL. Umožňuje vám to index složený obsah — pole, tsvectors, JSONB — kde každý prvek obsahu a indexovány samostatně. Nezbytné pro fulltextové vyhledávání a maticové dotazy.
GIN pro fulltextové vyhledávání
-- 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 pro Array Containment
-- 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"
GIN pro JSONB
-- 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)
Rozhodovací mapa: jaký index zvolit
| Scénář | Typ indexu | Důvod |
|---|---|---|
| Dotaz na PK nebo FK (rovnost) | B-Strom (výchozí) | Nejlepší pro =, <, >, BETWEEN |
| Pouze několik „aktivních“ řádků (např. čekající na vyřízení) | Částečný B-strom | Index 100x menší |
| SELECT se všemi sloupci v indexu | B-strom s VČETNĚ | Index-Only Scan, žádný přístup k hromadě |
| Tabulku lze pouze připojit s časovým razítkem | BRIN | 1000x menší než B-Strom |
| Fulltextové vyhledávání v italštině/angličtině | GIN na tsvector | Jediný způsob pro operátora @@ |
| Obsah pole (@>, &&) | GIN na TEXT[] | Jediný způsob pro operátory polí |
| Kontejner JSONB (@>) | GIN jsonb_path_ops | O 30 % kompaktnější než výchozí |
| LIKE 'vzor%' v textu | B-Strom (varchar_pattern_ops) | Pouze předpona LIKE |
| LIKE '%pattern%' v textu | GIN s pg_trgm | Jediný způsob, jak dát LIKE na médiu |
Údržba indexu
-- 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;
Závěry
Výběr správného typu indexu může znamenat rozdíl mezi dotazem trvajícím 10 sekund a ten, který trvá 10 milisekund. Částečný index a krycí index jsou optimalizacemi větší dopad na provozní dotazy. BRIN je revoluční pro protokoly a data časových řad. GIN otevírá možnosti vyhledávání, které žádný jiný typ indexu neumožňuje.
Další článek série se zabývá dělením tabulek: technikou správy tabulky s miliardami řádků bez ztráty výkonu, s ořezáváním oddílů a DROP Instant PARTITION pro správu životního cyklu dat.
Řada: Databázové inženýrství a optimalizace
- Článek 1: PostgreSQL 17/18 – Novinky a výkon
- Článek 2: VYSVĚTLENÍ ANALÝZY – Přečtěte si Plány dotazů
- článek 3 (tento): Pokročilá indexace - Částečná, Krycí, BRIN, GIN
- Článek 4: Rozdělení tabulek – Správa miliard řádků
- Článek 5: Sdružování připojení – PgBouncer vs Pgpool-II