Scegliere l'indice giusto in PostgreSQL

Aggiornato il 5 luglio 2026

PostgreSQL include sei metodi di accesso agli indici, e CREATE INDEX senza una clausola USING ti dà sempre un B-tree. Quel default è corretto la maggior parte delle volte — questa guida parla di riconoscere i casi in cui non lo è, e delle tre funzionalità degli indici (parziale, su espressione, covering) che contano più del metodo di accesso nel lavoro di tutti i giorni.

B-tree: il default per un buon motivo

Un indice B-tree supporta confronti di uguaglianza e di intervallo (=, <, >, BETWEEN, IN), IS NULL, LIKE 'abc%' con prefisso (con la giusta collation o text_pattern_ops), e — spesso dimenticato — l'ordinamento: un indice su (created_at) può soddisfare ORDER BY created_at DESC LIMIT 20 senza ordinare nulla.

Per i B-tree multicolonna, l'ordine delle colonne è tutto. Un indice su (customer_id, created_at) è eccellente per WHERE customer_id = ? ORDER BY created_at, ma pressoché inutile per una query che filtra solo su created_at. Regola pratica: prima le colonne di uguaglianza, poi la colonna di intervallo o di ordinamento.

Quando il B-tree è lo strumento sbagliato

Indici parziali: indicizza solo ciò che interroghi

CREATE INDEX orders_pending_idx
    ON orders (created_at)
    WHERE status = 'pending';

Se il 99% dei tuoi ordini è completato ma ogni polling chiede quelli pending, un indice parziale resta piccolo, resta caldo in cache ed è economico da mantenere — le righe completate non lo toccano mai. La query deve includere la condizione WHERE (o qualcosa da cui il planner possa dimostrarne l'implicazione) affinché l'indice venga preso in considerazione.

Indici su espressione: corrispondi a ciò che la query calcola

CREATE INDEX users_email_lower_idx ON users (lower(email));

Una query che filtra su lower(email) = lower($1) non può usare un semplice indice su email — l'indice memorizza i valori grezzi, non quelli in minuscolo. L'espressione nell'indice deve corrispondere all'espressione nella query. La stessa tecnica funziona per date_trunc('day', created_at), l'estrazione di campi JSON e qualsiasi funzione immutable.

Indici covering e index-only scan

CREATE INDEX orders_cust_idx
    ON orders (customer_id) INCLUDE (total, created_at);

Se l'indice contiene ogni colonna di cui la query ha bisogno, PostgreSQL può saltare del tutto la tabella — un index-only scan. INCLUDE aggiunge colonne di payload alle foglie dell'indice senza renderle parte della chiave. Due avvertenze: l'indice diventa più grande, e gli index-only scan richiedono anche che la visibility map sia aggiornata — su una tabella molto aggiornata e raramente sottoposta a vacuum vedrai Heap Fetches salire e gran parte del beneficio svanire.

Regole pratiche

• Crea gli indici in produzione con CREATE INDEX CONCURRENTLY — non blocca le scritture (non può girare dentro una transazione e ci mette più tempo, è il prezzo da pagare).
• Ogni indice tassa ogni INSERT/UPDATE/DELETE. Controlla periodicamente pg_stat_user_indexes.idx_scan ed elimina ciò che non viene mai letto.
• Un indice che il planner ignora non è necessariamente privo di statistiche — verifica che i tipi corrispondano, che le funzioni nel predicato corrispondano all'espressione dell'indice, e che la frazione filtrata sia abbastanza piccola da battere una scansione sequenziale.
• Verifica, non dare per scontato: esegui EXPLAIN (ANALYZE, BUFFERS) prima e dopo. La prova è nel piano.