Indicizzazione di array: creazione di indici GIN e BTREE

Immagina di avere una tabella con milioni di record, e una delle colonne contiene array. Per esempio, abbiamo la tabella products e ogni prodotto può appartenere a più categorie:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Array di stringhe per salvare le categorie del prodotto
);

Supponiamo che vuoi trovare tutti i prodotti che appartengono alla categoria electronics. Usare semplicemente l’operatore @> per la ricerca può portare a una scansione completa della tabella:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

La scansione completa (Seq Scan) — è lenta. Soprattutto se la tabella è enorme. Gli indici vengono in soccorso per trasformare questa ricerca in qualcosa di molto più veloce.

Tipi di indici per array

PostgreSQL supporta due tipi principali di indici che puoi usare con gli array:

1. GIN (Generalized Inverted Index) — perfetto per cercare velocemente elementi dentro un array o controllare le intersezioni.
2. BTREE (Binary Tree) — adatto per altre operazioni, tipo il confronto esatto tra array.

Vediamo ognuno di questi più nel dettaglio.

1. Indice GIN: super veloce

GIN (Generalized Inverted Index) — è un indice che va alla grande con operatori come:

• @> (l’array include un elemento o un altro array),
• <@ (l’array è incluso in un altro array),
• && (gli array si intersecano).

Ecco come puoi creare un indice GIN sulla nostra colonna categories:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Dopo aver creato l’indice, le query saranno molto più veloci. Per esempio, questa query:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

userà il tuo indice GIN.

Curiosità: L’indice GIN funziona come una lista invertita — tiene traccia di quali elementi (tipo stringhe) si trovano in quali record. È un po’ come l’indice analitico che trovi nei libri per cercare un argomento tramite il numero di pagina. Comodo, vero?

2. Indice BTREE: quando conta l’ordine

BTREE (Binary Tree) — è l’indice standard usato nella maggior parte dei database. È perfetto per operazioni che richiedono il confronto esatto degli array, tipo:

• Controllo di uguaglianza dell’array =,
• Confronto degli array in base all’ordine degli elementi (>, <).

Per creare un indice BTREE su un array puoi fare così:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Esempio di query che può usare l’indice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Ma occhio: gli indici BTREE non vanno bene per operatori tipo @> o <@. Per quelli meglio GIN.

Esempi di utilizzo degli indici

Ora uniamo la teoria alla pratica e vediamo qualche esempio.

1. Ricerca di intersezione tra array

Supponiamo di voler trovare tutti i prodotti collegati alle categorie electronics e smartphones, usando l’operatore && (intersezione di array):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Per questo va benissimo l’indice GIN che hai già creato prima:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Con questo indice la query sarà molto più veloce grazie alla lista invertita.

2. Confronto di array per uguaglianza

Se vuoi trovare i prodotti che appartengono solo alle categorie electronics e gadgets (in questo ordine), qui è meglio usare l’indice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Crea l’indice giusto:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Performance degli indici

Gli indici aiutano a velocizzare le query, ma hanno anche un rovescio della medaglia. Per esempio:

• Creazione dell’indice richiede tempo e risorse. Se hai una tabella enorme, la costruzione dell’indice può essere un processo piuttosto lungo.
• Aggiornamento della tabella. Ogni volta che inserisci nuove righe o aggiorni i dati esistenti, anche gli indici vengono aggiornati. Questo può rallentare le operazioni INSERT e UPDATE.

Comunque, nella maggior parte dei casi il vantaggio di avere query più veloci supera questi costi.

Come scegliere: GIN o BTREE?

Ecco una tabellina che ti aiuta a scegliere l’indice giusto per ogni situazione: