Indici Compositi e Parziali

Nelle lezioni precedenti abbiamo coperto l'uso dell'indice in interrogazioni del DBMS lineari (WHERE country = 'Italy'). E quando cerchiamo su due fronti combinati?

Ad esempio in un e-commerce reale vorremmo cercare ordini che appartengono ad uno specifico ID cliente E che rientrano in un determinato range temporale di validità: WHERE customer_id = 90 AND placed_at >= '2025-01-01'.

Se hai soltanto l'indice base su customer_id, Postgres troverà i 50 ordini del cliente in zero secondi avvelendosi dell'albero binario, ma dovrà poi iterarli tutti e 50 manualmente in maniera sequenziale ed impigrita filtrando le date ad occhio.

Gli Indici Compositi

Possiamo dire al DBMS di ordinare i file del "Dizionario analitico" non in 1 sola dimensione chiave, ma in più dimensioni.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);

Un indice "Composito" usa l'ordine dei suoi parametri con molta importanza: qui i nodi B-Tree raggruppamento e categorizzano in modo rigido in un blocco il customer_id (il più discriminante per separare); internamente ad esse vi sarà il sotto-albero ramificato con i vari valori temporali placed_at in modo perfettamente ordinato.

Questa struttura annichilisce brutalmente le WHERE con molteplici logiche annidate.

Gli Indici Parziali (Filtered Indexes)

Ogni Indice costa spazio Disco e CPU sugli stream di input inserimento utente. E se invece l'E-Commerce richiedesse un ordinamento e reperimento costante di un filtro ma solo per la minoranza dei records? (Ad es: controllare a ruota il check rapido delle notifiche non ancora lette). Creare l'indice "Is_Read" su tutti e 10.000.000 gli avvisi è incredibilmente tedioso se gli "Unread" ne rappresentano solo 5.

CREATE INDEX idx_unread_notifs ON notifications(user_id)
  WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"

Il database creerà un B-Tree rapidissimo in cui conserverà le chiavi d'accesso veloci al DB SOLAMENTE se il record è Unread! Il disco si infatuerà di un indice da 5 righe (3 byte) invece che uno da 10 Milioni. Le SELECT rapide ne beneficeranno totalmente:

SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;

Prova tu

Esplorare indici condizionati (Partial)