Seq Scan, Index Scan e Index-Only

Quando il planner ha bisogno di righe da una tabella, sceglie uno dei tre metodi di accesso seguenti; gli intervistatori si aspettano che li sappia elencare tutti:

• Seq Scan: legge ogni riga della tabella dall'inizio alla fine.
• Index Scan: percorre un indice per trovare le righe corrispondenti, quindi recupera ciascuna riga dalla tabella.
• Index-Only Scan: restituisce il risultato interamente dall'indice, senza accedere alla tabella.

Capire perché il planner sceglie ciascun metodo è il cuore di questa lezione e una domanda da senior quasi garantita.

Una Seq Scan legge le pagine della tabella una dopo l'altra e applica l'eventuale filtro a ogni riga. Non viene consultato alcun indice.

Può sembrare una scelta negativa, ma spesso è quella giusta. Le letture sequenziali sono veloci per il disco (non richiedono salti casuali), quindi, quando una query restituisce una grande parte della tabella, leggere tutto è più efficiente che attraversare un indice milioni di volte.

Nell'esempio si esegue una scansione di orders e si conservano le righe per cui amount > 100. Se la maggior parte degli ordini supera 100, una scansione sequenziale è la scelta corretta.

Una Index Scan usa un B-tree per raggiungere direttamente le chiavi corrispondenti, quindi legge le relative righe dall'heap della tabella.

È particolarmente vantaggiosa quando il filtro è selettivo e restituisce una piccola parte della tabella. Cercare 5 righe tramite un indice è più efficiente che leggere 10 milioni di righe.

Il piano indica l'indice utilizzato. Ogni corrispondenza richiede una ricerca nell'indice e un recupero dall'heap della tabella (una lettura casuale), quindi le scansioni con indice perdono il loro vantaggio quando restituiscono troppe righe.

Il concetto fondamentale alla base di tutto questo è la selettività: la frazione di righe che un predicato mantiene.

• Alta selettività (corrispondono poche righe, ad esempio con un ID univoco) favorisce una Index Scan.
• Bassa selettività (corrispondono molte righe, ad esempio con status IS NOT NULL) favorisce una Seq Scan.

Una regola pratica comune è che, quando una query restituisce più o meno del 5-10% di una tabella, il planner spesso preferisce una scansione sequenziale, perché i recuperi casuali dall'heap richiesti da un indice diventano più costosi della lettura ordinata dell'intera tabella.

Una Index-Only Scan è la più veloce dei tre metodi. Se ogni colonna necessaria alla query è già contenuta nell'indice, il motore non accede mai all'heap della tabella.

La query dell'esempio seleziona solo customer_id e applica un filtro su di esso, mentre l'indice è definito su customer_id. Tutti i dati necessari si trovano nell'indice, quindi Postgres segnala Index Only Scan.

In questo modo si evitano le letture casuali dall'heap che rallentano una normale scansione con indice: un enorme vantaggio sulle tabelle con righe larghe.

Gli intervistatori apprezzano molto questa sfumatura. Una scansione solo indice deve comunque verificare che ogni riga sia visibile alla transazione (MVCC), ma l'indice da solo non memorizza le informazioni sulla visibilità.

Postgres usa la visibility map: se una pagina è contrassegnata come all-visible, evita l'accesso all'heap; altrimenti deve recuperare comunque la riga dall'heap. Il piano mostra Heap Fetches: N.

Per questo una tabella appena aggiornata può mostrare molti recuperi dall'heap e scansioni solo indice lente, finché VACUUM non aggiorna la visibility map.

Esiste un quarto metodo che compare spesso: la Bitmap Heap Scan. Il planner la sceglie quando un predicato corrisponde a più righe di quante ne gestirebbe una normale scansione con indice, ma a meno righe di un'intera tabella.

Prima costruisce dall'indice una bitmap delle posizioni delle righe corrispondenti (Bitmap Index Scan), poi recupera le pagine dell'heap in ordine fisico anziché in ordine casuale. I recuperi ordinati sono molto meno costosi delle letture sparse di una normale scansione con indice.

Una domanda classica nei colloqui: ho aggiunto un indice, ma il piano esegue ancora una Seq Scan: perché? Le ragioni comuni sono:

• Il predicato non è selettivo: la scansione è effettivamente più economica.
• Una funzione avvolge la colonna: WHERE lower(email) = ... non può usare un normale indice su email.
• Una incompatibilità di tipo impone una conversione implicita che rende inutilizzabile l'indice.
• Statistiche obsolete: esegua ANALYZE.
• La tabella è piccolissima: la scansione di poche pagine è più efficiente dell'overhead dell'indice.

Supponga che orders abbia un indice su created_at, ma che questa query esegua comunque una scansione sequenziale:

La causa è DATE(created_at). Racchiudere la colonna in una funzione significa che l'indice sulla colonna created_at originale non può essere utilizzato. La soluzione consiste nel riscrivere il predicato come un predicato di intervallo che lasci la colonna invariata, oppure nel creare un indice su espressione per DATE(created_at).

Tenga a mente questo confronto per il colloquio:

• Seq Scan: ideale quando restituisce una grande parte delle righe; I/O sequenziale.
• Index Scan: ideale per ricerche selettive; attraversamento dell'indice più recuperi casuali dall'heap.
• Bitmap Heap Scan: numero intermedio di corrispondenze; dall'indice alla bitmap, quindi letture ordinate dell'heap.
• Index-Only Scan: la più veloce quando l'indice contiene tutte le colonne necessarie e le pagine sono tutte visibili.

Il planner sceglie in base al cost stimato, determinato principalmente dalla selettività e dalle statistiche.

Per dimostrare un concetto durante lo sviluppo, può temporaneamente orientare il planner: SET enable_seqscan = off; lo costringe a preferire gli indici, così può confrontare i piani.

Si tratta di un espediente diagnostico, mai di una soluzione da usare in produzione. Nei colloqui, specifichi che le soluzioni reali sono statistiche migliori, un indice appropriato o la riscrittura del predicato, non la disattivazione globale delle funzionalità del planner.

Una query seleziona solo email e applica un filtro su email; esiste inoltre un indice B-tree su email. Il piano mostra Index Only Scan. Perché è più veloce di una normale Index Scan?

Punti chiave sui metodi di accesso:

• Seq Scan è la scelta migliore per le query a bassa selettività; Index Scan è la scelta migliore per quelle selettive.
• Index-Only Scan evita l'heap quando l'indice contiene tutte le colonne necessarie: presti attenzione a Heap Fetches e alla visibility map.
• Bitmap Heap Scan costituisce una soluzione intermedia, recuperando le pagine dell'heap in ordine fisico.
• Il planner decide in base a selettività e statistiche; le funzioni sulle colonne, le incompatibilità di tipo e le statistiche obsolete sono i motivi per cui un indice viene ignorato.