Su una tabella da un milione di righe, la stessa SELECT può richiedere cinquanta millisecondi oppure molto meno di uno. L’unica differenza è se il database legge riga per riga per trovare quelle che hai chiesto, oppure salta dritto a quelle giuste. Un indice è ciò che gli permette di saltare, e lo paghi a ogni scrittura.

Concretamente, un indice è una seconda copia ordinata di una o più colonne, salvata accanto alla tabella e aggiornata automaticamente ogni volta che la tabella cambia. Il database la usa per raggiungere le righe corrispondenti senza toccare il resto.

PerchĂ© una query senza indice legge l’intera tabella

Senza un indice, WHERE customer_id = 42 costringe Postgres a leggere ogni riga e a testare la condizione su ciascuna. Questa è una scansione sequenziale (Seq Scan). Su mille righe non te ne accorgi nemmeno. Su un milione di righe dove quasi niente corrisponde, stai leggendo e scartando 999.989 righe per ottenerne 11.

Puoi vederlo da solo. Avvia un container Postgres, configurato come in Cos’è Docker Compose e Come Usarlo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

1
2

docker compose up -d db
docker compose exec db psql -U postgres

Poi costruisci una tabella abbastanza grande da far notare la differenza:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total_cents integer NOT NULL
);

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT
  (random() * 50000)::int,
  (ARRAY['pending', 'paid', 'shipped', 'refunded'])[(floor(random() * 4) + 1)::int],
  now() - (random() * interval '365 days'),
  (random() * 20000)::int
FROM generate_series(1, 1000000);

ANALYZE orders;

Un milione di righe, nessun indice su customer_id. Chiedi gli ordini di un cliente. Gli esempi che seguono disattivano prima i worker paralleli (SET max_parallel_workers_per_gather = 0), così il piano resta a un solo nodo. Su una macchina multi-core, Postgres potrebbe altrimenti dividere la stessa scansione tra un Gather e diversi worker Parallel Seq Scan, la stessa cosa ma con più processi in esecuzione insieme:

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Seq Scan on orders  (cost=0.00..20834.00 rows=21 width=31) (actual time=1.066..53.238 rows=11 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 999989
 Planning Time: 0.301 ms
 Execution Time: 53.284 ms

Rows Removed by Filter: 999989 è il conto da pagare. Postgres ha letto l’intera tabella per restituire 11 righe.

Cosa memorizza davvero un indice B-tree

Una tabella senza indice è una scatola di scontrini, buttati uno sull’altro nell’ordine in cui sono arrivati. Trovare tutti gli scontrini di un cliente significa svuotare la scatola.

Un indice B-tree è come gli stessi scontrini, ma archiviati in uno schedario e ordinati per cliente. Il livello piĂą basso, le leaf page, contiene ogni valore indicizzato accanto a un puntatore alla sua riga su disco. Sopra le foglie ci sono poche pagine interne, ognuna con il valore piĂą basso di un intervallo e un puntatore verso di esso. Le pagine di Postgres sono da 8 KB di default e una pagina interna può puntare a centinaia di figli, quindi tre o quattro livelli coprono decine di milioni di righe. Una ricerca legge una pagina per livello e atterra sulla foglia giusta. Poche letture di pagina invece di un passaggio sull’intera tabella.

Aggiungi l’indice ed esegui di nuovo la stessa query:

1

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

1
2

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

1
2
3
4
5
6
7

 Bitmap Heap Scan on orders  (cost=4.59..85.69 rows=21 width=31) (actual time=0.217..0.295 rows=11 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=11
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.58 rows=21 width=0) (actual time=0.090..0.090 rows=11 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.979 ms
 Execution Time: 0.449 ms

Da 53 ms a 0,45 ms, circa 120 volte piĂą veloce. Il costo stimato scende da 20834 a 86, lo Seq Scan è sparito e non c’è nessuna riga Rows Removed by Filter: non c’era niente da scartare.

Postgres ha scelto una Bitmap Heap Scan invece di un semplice Index Scan. Con una ventina di righe corrispondenti sparse per la tabella, conviene estrarre prima tutte le voci corrispondenti dall’indice (la riga Bitmap Index Scan), ordinarle per pagina fisica, poi visitare ogni pagina dell’heap una sola volta (Heap Blocks: exact=11) piuttosto che saltare nell’heap separatamente per ogni singola riga. Filtra su una sola riga (un confronto di uguaglianza su una colonna unica, ad esempio id = 42) e vedrai invece un semplice Index Scan. In entrambi i casi, l’indice fa lo stesso lavoro: raggiungere le righe corrispondenti senza leggere quelle che non corrispondono.

Come funziona l’ordine delle colonne in un indice composito

Un indice può coprire piĂą di una colonna, e l’ordine in cui le elenchi decide quali query può servire. CREATE INDEX ON orders (customer_id, status) ordina prima per customer_id, poi per status all’interno di ogni customer_id. Quindi può rispondere a:

  • WHERE customer_id = 42, usando la prima colonna.
  • WHERE customer_id = 42 AND status = 'paid', usando entrambe.

Non può rispondere in modo efficiente a WHERE status = 'paid' da sola. status è ordinata solo dentro il gruppo di ogni cliente, quindi non esiste un’unica sequenza ordinata di righe 'paid' su cui l’albero possa saltare. Questa è la regola del prefisso piĂą a sinistra (leftmost prefix rule): un indice composito aiuta una query solo se la query vincola le sue colonne da sinistra, in ordine, senza salti.

Due regole per scegliere l’ordine. Primo, metti davanti la colonna su cui le tue query filtrano da sola, perchĂ© è l’unica che l’indice può servire da sola. Secondo, metti le colonne che confronti con = prima di una colonna che confronti con un intervallo (>, <, BETWEEN), perchĂ© tutto quello che viene dopo la colonna a intervallo smette di restringere la ricerca. Per questa tabella, customer_id (circa 50.000 valori distinti) va prima di status (quattro valori): cerchi i clienti continuamente, e non vuoi mai scandagliare tutti gli ordini 'paid' senza distinguere il cliente.

Se ti servono anche ricerche veloci solo per status, costruisci un secondo indice. Non riordinare il primo.

Quando una index-only scan salta la tabella

La Bitmap Heap Scan di prima tocca comunque la tabella: è quello che indica Heap Blocks: exact=11. L’indice contiene customer_id e un puntatore a ogni riga; SELECT * chiede anche status, total_cents e created_at, e quelle vivono solo nella tabella. Una clausola INCLUDE mette colonne extra dentro l’indice stesso:

1
2
3

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id)
  INCLUDE (status, total_cents);

status e total_cents viaggiano come payload. Non fanno parte della chiave di ordinamento, quindi un WHERE status = ... non può usarle per cercare. Ma chiedi solo le colonne che l’indice ora porta con sĂ©:

1

EXPLAIN ANALYZE SELECT customer_id, status, total_cents FROM orders WHERE customer_id = 42;

1
2
3
4
5

 Index Only Scan using idx_orders_customer_covering on orders  (cost=0.42..4.79 rows=21 width=15) (actual time=0.071..0.073 rows=11 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
 Planning Time: 0.442 ms
 Execution Time: 0.108 ms

Index Only Scan e Heap Fetches: 0: ogni riga arriva direttamente dall’indice, senza nessuna visita alla tabella. Questa è una index-only scan.

Heap Fetches: 0 dipende dalla visibility map della tabella, che segna le pagine le cui righe sono visibili a ogni transazione — una scansione salta l’heap solo per le pagine segnate. VACUUM imposta questi segni (di solito autovacuum, che gira secondo un proprio programma, non qualcosa che lanci a mano) e ogni scrittura su una pagina li cancella di nuovo. Su una tabella con molte scritture recenti, molte pagine non sono segnate, Heap Fetches sale sopra zero, e Postgres visita comunque l’heap per quelle righe. Gli indici di copertura ripagano di piĂą su tabelle lette molto piĂą di quanto vengano scritte.

Cosa costa un indice e perché Postgres lo ignora

Ogni INSERT scrive una voce in ogni indice della tabella, e un UPDATE che cambia una colonna indicizzata scrive nuove voci allo stesso modo. Cinque indici significano cinque strutture extra da mantenere a ogni scrittura. Gli indici occupano anche spazio su disco. Confronta direttamente le due dimensioni:

1
2

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_relation_size('idx_orders_customer_id')) AS index_size;

E a volte costruisci l’indice e il planner non lo usa. Raramente è un bug di Postgres. Di solito è uno di questi casi:

  • Una funzione avvolge la colonna. WHERE lower(email) = '[email protected]' non può usare un indice semplice su email, perchĂ© l’indice contiene i valori grezzi, non lower(email). Indicizza l’espressione (CREATE INDEX ON users (lower(email))) oppure filtra sulla colonna grezza.
  • Un wildcard iniziale. WHERE name LIKE '%smith' non dĂ  all’albero nessun prefisso su cui cercare. LIKE 'smith%' sì.
  • Bassa selettivitĂ . Un indice su un booleano diviso 50/50 non risparmia niente. Postgres leggerebbe comunque metĂ  dell’indice e gran parte delle pagine della tabella, quindi il planner sceglie correttamente una scansione sequenziale.
  • Statistiche obsolete. Il planner sceglie tra i tipi di scansione usando le statistiche che ANALYZE raccoglie in pg_statistic. Dopo un caricamento massivo senza ANALYZE, le sue stime sulle righe possono essere molto lontane dalla realtĂ . Eseguilo dopo ogni caricamento grande, come nella configurazione qui sopra.

Quando vedi uno Seq Scan dove ti aspettavi un Index Scan, controlla la query rispetto a questa lista prima di toccare l’indice.

Quando aggiungere un indice e quando evitarlo

Aggiungine uno quando una colonna compare in una WHERE, una JOIN o una ORDER BY su una tabella abbastanza grande da rendere costosa una scansione, e il filtro è selettivo: restringe il risultato a una piccola fetta della tabella, non a metà. Evitalo quando la tabella è abbastanza piccola che una scansione è già veloce, quando la tabella riceve molte scritture e poche letture, o quando la colonna non riesce a restringere niente.

Non aggiungerlo per intuito. Esegui prima EXPLAIN ANALYZE sulla query lenta. Se mostra giĂ  un Index Scan ed è comunque lenta, l’indice non è il tuo problema: riscrivi la query, oppure metti il risultato in cache con Redis. Se mostra uno Seq Scan su una tabella grande con un filtro selettivo, costruisci l’indice esattamente sulle colonne che usa la WHERE, nell’ordine richiesto dalla regola del prefisso piĂą a sinistra, poi esegui di nuovo EXPLAIN ANALYZE e conferma che il piano sia cambiato.

Articoli correlati