Menu

CREATE INDEX PostgreSQL: tipi di indici ed esempi

CREATE INDEX crea di default un indice B-tree, così PostgreSQL trova le righe senza leggere tutta la tabella. Gli altri tipi (GIN, GiST, BRIN, hash) coprono JSONB, array, ricerca full-text, intervalli e tabelle molto grandi.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

CREATE INDEX name ON table (column); crea un indice che permette a PostgreSQL di trovare le righe corrispondenti senza leggere tutta la tabella. Il tipo predefinito è B-tree, che serve =, <, >, BETWEEN, IN, IS NULL e ORDER BY. Scegli un altro tipo con USING: gin, gist, spgist, brin o hash.

La prima riga del piano dice Index Scan using users_email_idx on users: PostgreSQL cerca il valore nell'indice e legge solo la riga corrispondente. Togli la riga CREATE INDEX ed eseguilo di nuovo per vedere Seq Scan on users, che legge tutte le 10.000 righe.

Su una tabella di qualche decina di righe, il planner sceglie spesso una scansione sequenziale anche quando esiste un indice: leggere poche pagine costa meno. EXPLAIN ANALYZE spiega come leggere questi piani.

Scegliere un tipo di indice

TipoUsalo per
B-tree (predefinito)uguaglianza, intervalli, ordinamento, LIKE 'prefix%' (con la collation C o text_pattern_ops)
GINvalori fatti di molte parti: jsonb (@>, ?), array (@>, &&), tsvector per il full-text
GiSTgeometria, intervalli (&&, sovrapposizioni), full-text, vicino più prossimo con ORDER BY ... <->
SP-GiSTdati che si dividono in regioni senza sovrapposizioni: punti, indirizzi IP, prefissi di testo
BRINtabelle molto grandi i cui valori seguono l'ordine fisico, come un created_at in sola aggiunta
Hashsolo uguaglianza; un B-tree fa lo stesso e di più, quindi si sceglie di rado

Un indice GIN su una colonna jsonb fa usare l'indice alle query di contenimento:

Il piano mostra una Bitmap Index Scan sull'indice GIN, che alimenta una Bitmap Heap Scan che legge le righe corrispondenti. La pagina su JSONB copre i suoi operatori.

Indicizzare più colonne

Un indice B-tree su più colonne ordina per la prima colonna, poi per la seconda dentro ogni valore della prima. Serve le query che filtrano sulla colonna iniziale, o sulla colonna iniziale e sulle successive. Un filtro solo sulla seconda colonna ne guadagna poco, quindi metti per prima la colonna che filtri sempre con =.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+) salva colonne in più nell'indice senza ordinare per esse: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Una query che legge solo quelle colonne può allora ricevere risposta dal solo indice, con una Index Only Scan.

Indici unique e parziali: imporre una regola su alcune righe

CREATE UNIQUE INDEX rifiuta i valori duplicati. Un indice parziale aggiunge una WHERE e indicizza solo le righe che la soddisfano. È più piccolo e può far valere una regola di unicità su un sottoinsieme di righe:

Entrambe le righe vengono accettate perché solo una delle due non è eliminata. Una terza riga attiva con la stessa email fallisce con duplicate key value violates unique constraint "accounts_email_live". Una query usa un indice parziale solo quando la sua WHERE implica la condizione dell'indice, qui WHERE NOT deleted.

Indicizzare un'espressione

Un indice può salvare il risultato di un'espressione. Per usarlo, una query deve ripetere la stessa espressione, e un indice su email non aiuta WHERE lower(email) = ...:

Il piano usa users_email_lower_idx perché la WHERE ripete esattamente lower(email). L'espressione deve essere immutabile, quindi now() o una funzione che dipende da un'impostazione vengono rifiutate.

Elencare, creare ed eliminare indici su un database in produzione

pg_indexes elenca ogni indice con l'istruzione che lo ricreerebbe:

La chiave primaria e il vincolo UNIQUE hanno creato i propri indici, users_pkey e users_email_key. In psql, \d users mostra lo stesso elenco sotto le colonne della tabella.

Su una tabella che riceve scritture, crea ed elimina gli indici con CONCURRENTLY così le scritture non vengono bloccate. Queste forme non possono girare dentro un blocco di transazione. Una creazione concorrente fallita lascia un indice INVALID, che devi eliminare e creare di nuovo:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Domande frequenti

PostgreSQL crea gli indici in automatico?

Solo per i vincoli PRIMARY KEY, UNIQUE e di esclusione. Una colonna con chiave esterna non riceve un indice, quindi aggiungilo tu quando fai join su di essa o elimini righe dalla tabella referenziata.

Perché PostgreSQL non usa il mio indice?

Motivi comuni: la tabella è piccola, quindi leggerla tutta costa meno; la query restituisce una grande parte delle righe; la WHERE avvolge la colonna in una funzione o in un cast che l'indice non ha; oppure le statistiche sono vecchie (esegui ANALYZE). EXPLAIN mostra quale piano è stato scelto.

Quanti indici sono troppi?

Non c'è un numero fisso. Ogni indice velocizza alcune letture e rallenta ogni INSERT e la maggior parte degli UPDATE sulla tabella, e occupa spazio su disco. pg_stat_user_indexes.idx_scan mostra quanto spesso viene usato ogni indice, così puoi eliminare quelli che restano a zero.

CREATE INDEX blocca la tabella?

Un CREATE INDEX semplice blocca INSERT, UPDATE e DELETE sulla tabella finché non finisce, anche se le letture continuano. CREATE INDEX CONCURRENTLY permette le scritture durante la creazione, ci mette di più e non può girare dentro un blocco di transazione.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia