# PostgreSQL indexes that help

A PostgreSQL index earns its writes when it covers the filtered and sorted columns in order; on 300,000 rows the measured gap is 15.5 ms against 0.02 ms.

> Page: https://ui.eneawork.it/blog/postgresql-indici-utili
> Markdown: https://ui.eneawork.it/blog/postgresql-indici-utili.md

*2026-11-01*

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.

Su questa macchina non c'era un'istanza PostgreSQL raggiungibile, quindi la misura è un esperimento di controllo su SQLite 3.51.3 con Node 24.15.0: tabella orders da 300.000 righe, query con filtro su customer_id, ordinamento su created_at e limite 20. Mediana di 30 esecuzioni, il 2 novembre 2026: 15,471 ms senza indice, 0,024 ms con indice sulla colonna filtrata, 0,008 ms con indice composto su (customer_id, created_at). Costruzione dell'indice: 66 ms e 170 ms. Lo stesso test, con tre indici presenti, porta 5.000 insert da 5 a 85 ms. PostgreSQL 18, prima release il 25 settembre 2025, è la versione di riferimento per la sintassi sotto; licenza PostgreSQL License. Il catalogo EneUI conta 111.985 componenti, 1.556 blocchi e 12.808 icone da 1.472 librerie, letti da /api/stats il 1 novembre 2026.

## 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

-- 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.

## FAQ

### Quanti indici servono su una tabella?

Quanti ne richiedono le query lente reali, di solito uno o due. Oltre, ogni insert e update aggiorna più strutture e il guadagno in lettura non compensa.

### Un indice su una colonna a bassa cardinalità serve?

Raramente da solo. Su una colonna con pochi valori il planner preferisce una scansione; serve un indice parziale o composto con una colonna selettiva.

### Meglio CREATE INDEX o CREATE INDEX CONCURRENTLY?

CONCURRENTLY in produzione: non blocca le scritture durante la costruzione. Costa più tempo e non può girare dentro una transazione.

### Come capisco se un indice è inutile?

Guarda idx_scan in pg_stat_user_indexes dopo un ciclo di traffico completo: se resta a zero, l'indice occupa spazio e rallenta le scritture senza essere letto.

### Un indice composto (a, b) serve anche per filtrare su b?

No, il prefisso conta: funziona per a e per a con b, non per b da solo. L'ordine delle colonne va scelto sulle query, non sull'intuito.