Zaawansowane indeksowanie PostgreSQL: częściowe, obejmujące, BRIN i GIN
Jednokolumnowy indeks B-Tree jest rozwiązaniem 80% problemów z wydajnością. Ale 20% pozostałe wymagają bardziej wyrafinowanych narzędzi: częściowego indeksu, który indeksuje tylko „aktywne” wiersze zmniejsza indeks o 95%, indeks pokrywający całkowicie eliminuje dostęp do tabeli sterty, BRIN w tabeli szeregów czasowych zajmuje 100 razy mniej miejsca niż B-Drzewo, a GIN umożliwia przeszukiwanie pełnotekstowe i tablicowe, którego nie jest w stanie wykonać żadne B-Tree. W tym przewodniku opisano wszystkie cztery typy.
Czego się nauczysz
- Indeks częściowy: Indeksuj tylko wiersze, które mają znaczenie w przypadku najczęstszych zapytań
- Pokrycie indeksu za pomocą INCLUDE: włącz skanowanie tylko indeksu w zapytaniach SELECT
- Indeks BRIN: dla tabel szeregów czasowych zawierających miliony wierszy
- Indeks GIN: wyszukiwanie pełnotekstowe, zawieranie tablic i zapytania JSONB
- Jak wybrać właściwy typ indeksu dla każdego scenariusza
Indeks częściowy: indeksuj tylko to, co ma znaczenie
Indeks częściowy indeksuje tylko wiersze spełniające warunek WHERE. I technika najpotężniejszy w scenariuszach, w których niewielki procent wierszy otrzymuje zdecydowaną większość zapytań: zamówienia „oczekujące”, wiadomości „nieprzeczytane”, zadania „w toku”.
-- 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!)
Częściowy indeks częściowych unikalnych wartości
-- 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
Indeks obejmujący INCLUDE: Wyeliminuj dostęp do sterty
Skanowanie tylko indeksu odczytuje dane bezpośrednio z indeksu, bez dostępu do tabeli sterty.
Jest to najszybszy możliwy rodzaj skanowania. Aby to włączyć, indeks musi zawierać wszystkie
kolumny, które pojawiają się w SELECT. Klauzula INCLUDE dodaj „dodatkowe” kolumny
w indeksie bez uwzględniania ich w strukturze B-Tree (a zatem bez wpływu na wydajność
INSERT/UPDATE na samym indeksie).
-- 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!
Kiedy ZAWIERA, nie pomaga
-- 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
Indeks BRIN: dla tabel szeregów czasowych
BRIN (Block Range INdex) przeznaczony jest dla tabel, w których dane są fizycznie sortowane kolumna (zwykle znacznik czasu wstawienia). Zamiast indeksować każdą wartość, BRIN przechowuje tylko min./maks. dla każdego bloku stron. Jest znacznie mniejszy od np B-Tree, ale mniej precyzyjne: idealne do skanowania zakresu dat.
-- 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 kontra B-Tree: kiedy wybrać
-- 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
Indeks GIN: wyszukiwanie pełnotekstowe, tablice i JSONB
GIN (Generalized Inverted INdex) to najbardziej wszechstronny typ indeksu PostgreSQL. Pozwala na to indeksuj treść złożoną — tablice, tsvectors, JSONB — gdzie każdy element treści i indeksowane oddzielnie. Niezbędne w przypadku wyszukiwania pełnotekstowego i zapytań tablicowych.
GIN do wyszukiwania pełnotekstowego
-- 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 do przechowywania macierzy
-- 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 dla 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)
Mapa decyzyjna: który indeks wybrać
| Scenariusz | Typ indeksu | Powód |
|---|---|---|
| Zapytanie o PK lub FK (równość) | Drzewo B (domyślne) | Najlepsze dla =, <, >, POMIĘDZY |
| Tylko kilka „aktywnych” linii (np. oczekujących) | Częściowe drzewo B | Indeks 100x mniejszy |
| SELECT ze wszystkimi kolumnami w indeksie | B-Tree z ZAWIERA | Skanowanie tylko indeksowe, brak dostępu do sterty |
| Tabela przeznaczona tylko do dołączania ze sygnaturą czasową | BRIN | 1000 razy mniejszy niż B-Tree |
| Wyszukiwanie pełnotekstowe w języku włoskim/angielskim | GIN na tsvectorze | Jedyny sposób dla operatora @@ |
| Zawieranie tablicy (@>, &&) | GIN w TEKŚCIE[] | Jedyny sposób dla operatorów tablicowych |
| Przechowywanie JSONB (@>) | GIN jsonb_path_ops | 30% bardziej kompaktowy niż domyślny |
| LIKE „wzór%” w tekście | Drzewo B (varchar_pattern_ops) | Tylko przedrostek LIKE |
| JAK „% wzór%” w tekście | GIN z pg_trgm | Jedyny sposób, żeby polubić na średnim poziomie |
Konserwacja indeksu
-- 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;
Wnioski
Wybór odpowiedniego typu indeksu może sprawić różnicę w przypadku zapytania trwającego 10 sekund i taki, który trwa 10 milisekund. Indeks częściowy i indeks pokrywający są optymalizacjami bardziej skuteczny w przypadku zapytań operacyjnych. BRIN to rewolucja w zakresie logów i danych szeregów czasowych. GIN otwiera możliwości wyszukiwania, na które nie pozwala żaden inny typ indeksu.
Następny artykuł z tej serii dotyczy partycjonowania tabel: techniki zarządzania tabele z miliardami wierszy bez utraty wydajności, z czyszczeniem partycji i DROP Natychmiastowa PARTYCJA do zarządzania cyklem życia danych.
Seria: Inżynieria i optymalizacja baz danych
- Artykuł 1: PostgreSQL 17/18 – Nowości i wydajność
- Artykuł 2: WYJAŚNIJ ANALIZĘ - Przeczytaj plany zapytań
- Artykuł 3 (ten): Zaawansowane indeksowanie - częściowe, obejmujące, BRIN, GIN
- Artykuł 4: Partycjonowanie tabeli — zarządzanie miliardami wierszy
- Artykuł 5: Pula połączeń – PgBouncer kontra Pgpool-II