SQL Academy · Lezione

Indici parziali e su espressioni

Indicizzi solo le righe che le interessano usando clausole WHERE e indicizzi espressioni calcolate (lower(email), date_trunc('day', ts)).

Indici parziali e su espressioni è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 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 Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Indice parziale: indicizzare un sottoinsieme

Un indice parziale include solo le righe che corrispondono a una clausola WHERE al momento della creazione dell'indice. È più piccolo, più veloce e ideale per la query del caso comune:

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

Quando gli indici parziali sono vantaggiosi

Usarli quando:

Si applica sempre il filtro con lo stesso predicato (ad esempio per l'eliminazione logica)
Un valore prevale nella colonna (ad esempio, il 95% delle righe ha status='closed')
Si desidera un indice piccolo e frequentemente presente nella cache

Esempio: sessioni attive

La maggior parte delle sessioni è scaduta; vengono interrogate solo quelle attive:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

Meglio: usare un predicato stabile

Il predicato dell'indice deve essere immutable. Le espressioni dipendenti dal tempo, come NOW(), non sono ammesse. Usare invece una colonna come is_active:

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

Indice su un'espressione

Indicizzare una funzione applicata a una colonna:

CREATE INDEX users_email_lower_idx ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

Indici su espressioni comuni

LOWER(email) per la ricerca senza distinzione tra maiuscole e minuscole
(price * tax_rate) per l'ordinamento su un valore calcolato
date_trunc('day', created_at) per i report giornalieri
(data ->> 'user_id')::BIGINT per estrarre dati JSONB

L'espressione deve essere IMMUTABLE

L'espressione deve essere contrassegnata come IMMUTABLE: il suo risultato dipende solo dall'input. RANDOM(), NOW() e CURRENT_USER non sono ammesse.

Combinazione di indice parziale ed espressione

È possibile combinarli:

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

Indici parziali univoci

Il classico schema «una riga attiva»:

CREATE UNIQUE INDEX users_one_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Same email can exist many times in deleted users, but only once active.

Il predicato dell'indice deve corrispondere alla query

Il planner usa l'indice parziale solo quando la clausola WHERE della query «implica» il predicato dell'indice:

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

Non abusarne

Molti indici parziali che coprono sottoinsiemi disgiunti possono rallentare le scritture (ogni INSERT deve aggiornare tutti gli indici pertinenti). Usarli con moderazione per le query più frequenti.

Riepilogo

Gli indici parziali e quelli sulle espressioni offrono più prestazioni per ogni byte.

Parziale: indicizza solo il sottoinsieme interrogato
Su un'espressione: indicizza valori calcolati
Entrambi richiedono predicati IMMUTABLE
Per corrispondere, la query deve usare lo stesso predicato

Verifica rapida

Si desiderano ricerche senza distinzione tra maiuscole e minuscole tramite email. Quale indice è più efficiente?

Domande Frequenti

La lezione «Indici parziali e su espressioni» è gratuita?

Sì — il testo completo di «Indici parziali e su espressioni» è 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 Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Indici parziali e su espressioni»?

Indicizzi solo le righe che le interessano usando clausole WHERE e indicizzi espressioni calcolate (lower(email), date_trunc('day', ts)). Eserciti SQL Academy 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 Academy?

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

Quanto tempo richiede la lezione «Indici parziali e su espressioni»?

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 Academy?

Sì. Ogni lezione SQL Academy 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

← Torna a SQL Academy