Indici PostgreSQL che servono

Un indice PostgreSQL si guadagna le sue scritture quando copre le colonne che la query filtra o ordina, nell'ordine giusto. Su 300.000 righe la stessa query è passata da 15,471 ms a 0,024 ms con un indice e a 0,008 ms con un indice composto che copre anche l'ordinamento.

Prima l'EXPLAIN, poi l'indice

Un indice scelto a intuito risolve il caso sbagliato. Esegui EXPLAIN (ANALYZE, BUFFERS) sulla query reale, con i parametri reali e i dati reali: cerca una scansione sequenziale su una tabella grande, un numero di righe stimate lontano da quelle effettive, o un sort che finisce in temp file. Se le stime sono sbagliate, il primo rimedio è ANALYZE, non un indice in più.

Tre indici che si ripagano

• Chiave esterna: PostgreSQL non crea un indice sulla colonna di una foreign key. Se fai join o cancelli il padre, senza indice la ricerca è una scansione.
• Composto su filtro più ordine: (customer_id, created_at DESC) risolve filtro e ORDER BY in un solo passaggio, come nel test qui sopra.
• Parziale su un sottoinsieme caldo: WHERE status = 'open' indicizza poche righe invece di tutta la tabella, e resta piccolo quando la tabella cresce.

-- the query you actually run, with real parameters
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

-- one index for the filter and the sort
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

-- a partial index for the hot subset only
CREATE INDEX CONCURRENTLY idx_orders_open
  ON orders (created_at DESC)
  WHERE status = 'open';

CREATE INDEX CONCURRENTLY non può girare dentro un blocco di transazione e può lasciare un indice invalido se fallisce: in quel caso va rimosso e ricreato, perché il planner non lo usa. La stessa clausola non blocca le scritture, ma richiede due scansioni della tabella, quindi su una tabella grande impiega più tempo di una costruzione normale.

Il costo in scrittura

Nel test di controllo 5.000 insert senza indici hanno richiesto 5 ms, con tre indici 85 ms. PostgreSQL scrive su heap, su ogni btree e sul WAL a ogni riga, quindi la forma è la stessa; il rapporto non è lineare e dipende dalla cache, ma la direzione non cambia. Un indice che non legge nessuno è un rallentamento puro.

Trovare gli indici che non lavorano

Dopo un ciclo di traffico completo, pg_stat_user_indexes dice quante volte ogni indice è stato usato. Un idx_scan fermo a zero indica un indice da rimuovere, salvo che serva a un vincolo. La query sotto elenca i candidati con la dimensione occupata.

SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Il catalogo EneUI

Per mostrare il risultato di un indice servono tabelle dense: in catalogo ci sono i componenti tabella, i blocchi tabella e un record verificato, table di Origin UI, licenza MIT dichiarata a monte. Controllo eseguito il 1 novembre 2026 sul catalogo da 111.985 componenti.

La limitazione

I numeri qui sopra vengono da SQLite, non da PostgreSQL: servono a mostrare la forma del guadagno, non a stimare la latenza del tuo database. PostgreSQL ha un modello a costi, statistiche, bloat e visibilità delle transazioni che SQLite non ha, e un indice può restare inutilizzato per una stima sbagliata anche quando è corretto. La misura valida è sempre EXPLAIN (ANALYZE, BUFFERS) sulla tua istanza.

Dove continuare

L'indice è metà del problema: l'altra metà è la forma della query e del contratto, descritta in progettare un'API. Se la latenza dipende dalla cache, leggi i quattro livelli di cache in Next.js. Il confronto fra stili di API, con i costi di entrambi, è in MCP e REST.