0Pricing
SQL Interview Prep · Lezione

Quando gli indici sono dannosi: scritture e selettività

L'amplificazione delle scritture e il motivo per cui un indice su una colonna a bassa selettività è inutile.

Quando gli indici sono dannosi: scritture e selettività è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

La domanda dietro la domanda

Dopo tre lezioni sui motivi per cui gli indici sono utili, gli intervistatori cambiano prospettiva: «Perché non indicizzare semplicemente ogni colonna?» Un candidato preparato spiega che gli indici hanno costi reali, sulle scritture e in termini di cache e spazio di archiviazione, e che alcuni indici potrebbero non essere mai usati dal planner.

Questa lezione tratta i due motivi principali per cui un indice può essere dannoso: la write amplification e la bassa selettività.

Ogni indice rallenta le scritture

Un indice deve rimanere sincronizzato con la tabella. Ogni INSERT, ogni DELETE e ogni UPDATE su una colonna indicizzata devono aggiornare anche la struttura dell'indice. Questa è la write amplification: una modifica a una riga si traduce in una scrittura sulla tabella più una scrittura per ogni indice interessato.

Una tabella con otto indici sostiene un carico di scrittura circa nove volte superiore a quello di una tabella senza indici. Per le tabelle soggette a molte scritture o ad alto throughput, è un costo significativo.

Esempio svolto: il costo delle scritture

Immagini una tabella degli eventi che acquisisce migliaia di righe al secondo. Ogni indice aggiuntivo fa svolgere più lavoro a ogni inserimento: suddivide le pagine dell'indice, aggiorna le foglie e compete per lo spazio nella cache.

Per una tabella append-only, dominata dalle scritture, spesso la scelta corretta è avere pochi indici o nessuno oltre alla chiave primaria, eseguendo invece le letture pesanti su una replica o su un data warehouse.

-- Each of these indexes adds cost to EVERY insert below
CREATE INDEX ix_events_user ON events (user_id);
CREATE INDEX ix_events_type ON events (event_type);
CREATE INDEX ix_events_ts   ON events (created_at);

INSERT INTO events (user_id, event_type, created_at)
VALUES (42, 'click', now());  -- now updates table + 3 indexes

Che cosa significa selettività

La selettività indica quanto bene una colonna distingue le righe, cioè la frazione di righe corrispondenti a un valore tipico. Un'alta selettività significa poche righe per valore, come nel caso di un indirizzo email o di un UUID. Una bassa selettività significa molte righe per valore, come nel caso di un booleano o di uno stato con tre possibili valori.

Gli indici sono vantaggiosi sulle colonne con alta selettività, dove una ricerca elimina quasi tutte le righe. Spesso non lo sono sulle colonne con bassa selettività.

Perché un indice a bassa selettività è inutile

Supponga che is_active sia true per il 90% degli utenti. Una ricerca tramite indice restituirebbe il 90% della tabella e, per un numero così elevato di righe, il motore eseguirebbe un recupero dall'heap per ogni riga: sarebbe più lento di una semplice scansione sequenziale della tabella in un unico passaggio.

Perciò il planner ignora correttamente l'indice ed esegue una scansione sequenziale. L'indice comporta quindi solo costi di scrittura e di spazio, senza offrire alcun vantaggio in lettura.

-- 90% of rows match: the planner will likely skip this index
CREATE INDEX ix_users_active ON users (is_active);
SELECT * FROM users WHERE is_active = true;

La soglia approssimativa

Una regola pratica da enunciare è la seguente: quando un predicato corrisponde a più o meno del 5-20% di una tabella, una scansione sequenziale solitamente è più veloce di una scansione tramite indice, perché i recuperi casuali dall'heap costano più della lettura sequenziale delle pagine.

Il punto di equilibrio esatto dipende dalle dimensioni delle righe, dalla cache e dalla velocità dello spazio di archiviazione; per questo il planner usa le statistiche, non un numero fisso, per decidere.

Gli indici parziali vengono in aiuto

Se esegue query solo sui valori rari di una colonna con distribuzione asimmetrica, un indice parziale (Postgres) indicizza soltanto quelle righe: è piccolo, selettivo ed economico da mantenere.

Se l'1% degli ordini è pending e sono proprio quelli che interroga continuamente, indicizzi solo quegli ordini. L'indice rimane piccolo e il planner lo utilizzerà volentieri.

-- Index only the rare, frequently-queried rows
CREATE INDEX ix_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Le statistiche obsolete traggono in inganno il planner

L'ottimizzatore decide tra indice e scansione sulla base delle statistiche delle colonne. Se queste sono obsolete, ad esempio dopo un caricamento massivo o un aggiornamento significativo, può valutare male la selettività e scegliere il piano sbagliato.

Quando un intervistatore dice «l'indice esiste ma non viene usato», una risposta eccellente include l'aggiornamento delle statistiche con ANALYZE, prima di attribuire la colpa all'indice stesso.

ANALYZE orders;  -- refresh planner statistics

Altri modi in cui gli indici possono danneggiare le prestazioni

Completi la risposta menzionando i costi meno noti:

  • Spazio di archiviazione e cache: gli indici occupano spazio su disco e competono per la memoria, espellendo pagine di dati utili.
  • Indici ridondanti o sovrapposti: vengono mantenuti, ma non vengono mai scelti.
  • Frammentazione: con aggiornamenti intensivi, i B-Tree si frammentano e richiedono REINDEX.
  • Confusione per l'ottimizzatore: un numero eccessivo di indici simili rende la pianificazione più lenta e meno prevedibile.

Individuare gli indici inutilizzati

Per motivare una pulizia in un caso reale, ricordi che Postgres tiene traccia dell'uso degli indici. Gli indici con idx_scan = 0 sono candidati alla rimozione: comportano costi di scrittura e di spazio senza mai servire una lettura.

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Come formulare la risposta al colloquio

Un riepilogo completo ed equilibrato:

«Gli indici comportano write amplification: ogni inserimento, aggiornamento o eliminazione li mantiene, aumentando inoltre la pressione su spazio di archiviazione e cache. Sono vantaggiosi solo per i predicati ad alta selettività; su una colonna che corrisponde alla maggior parte delle righe, il planner preferisce correttamente una scansione sequenziale, quindi l'indice rappresenta solo un sovraccarico. Per le colonne con distribuzione asimmetrica scelgo un indice parziale, mantengo aggiornate le statistiche con ANALYZE e rimuovo gli indici inutilizzati.»

Verifica rapida

Determini quale indice ha meno probabilità di valere il costo che comporta.

Riepilogo: quando gli indici fanno danni

Punti chiave:

  • Ogni indice aggiunge write amplification e costi in termini di spazio di archiviazione e cache.
  • Gli indici sono utili sulle colonne con alta selettività; su quelle con bassa selettività, il planner preferisce una scansione sequenziale.
  • Quando vengono confrontate più o meno del 5-20% delle righe, solitamente vince una scansione.
  • Usi un indice parziale per le colonne con distribuzione asimmetrica che interroga solo in corrispondenza dei valori rari.
  • Mantenga aggiornate le statistiche con ANALYZE e rimuova gli indici inutilizzati (idx_scan = 0).

Con questo si conclude il corso sulle strategie di indicizzazione: crei gli indici dove risultano utili e lo dimostri con il piano di esecuzione.

Domande Frequenti

La lezione «Quando gli indici sono dannosi: scritture e selettività» è gratuita?

Sì — il testo completo di «Quando gli indici sono dannosi: scritture e selettività» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Quando gli indici sono dannosi: scritture e selettività»?

L'amplificazione delle scritture e il motivo per cui un indice su una colonna a bassa selettività è inutile. Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Quando gli indici sono dannosi: scritture e selettività»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?

Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Indici B-Tree e loro utilità
  2. Ordine delle colonne negli indici compositi
  3. Indici covering e scansioni index-only
  4. Quando gli indici sono dannosi: scritture e selettività
← Torna a SQL Interview Prep