Indicizzazione degli array e operatori (`@>`, `<@`, `&&`) per ricerche veloci

Gli array in PostgreSQL ti permettono di salvare tanti valori in una sola cella della tabella. È super comodo quando vuoi raggruppare dati collegati, tipo una lista di tag per un articolo o le categorie di un prodotto. Ma appena inizi a cercare, filtrare o incrociare gli array, le performance possono andare a farsi benedire. Ecco perché l’indicizzazione degli array ti salva la vita. Gli indici ti permettono di velocizzare operazioni come:

• verificare se un array contiene un elemento specifico,
• trovare array che contengono determinati elementi,
• verificare se ci sono elementi in comune tra array.

Operatori per lavorare con gli array

Prima di buttarti a creare indici, vediamo gli operatori base per lavorare con gli array:

@> (contains) — controlla se un array contiene tutti gli elementi di un altro array.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Qui cerchiamo i corsi che hanno il tag "SQL".

<@ (is contained by) — controlla se un array è contenuto in un altro.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Qui cerchiamo i corsi i cui tag includono tutti gli elementi dell’array ARRAY['PostgreSQL', 'SQL'].

&& (overlap) — controlla se ci sono elementi in comune tra due array.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Questa query trova i corsi che hanno almeno uno dei tag "NoSQL" o "Big Data".

Come aiuta l’indicizzazione?

Immagina di avere una tabella courses con milioni di righe e di lanciare una query con uno degli operatori sopra. Senza indice, PostgreSQL deve controllare ogni riga una per una — roba che può durare una vita (soprattutto se hai la pazienza di un dev che aspetta la compilazione).

Con gli indici, tutto questo si evita. PostgreSQL offre due tipi di indici adatti agli array:

1. GIN (Generalized Inverted Index) — la scelta migliore per gli array.
2. BTREE — usato per confrontare array interi.

Esempio: Creiamo una tabella con array e testiamo

Creiamo una tabella semplice con array per provare tutto in pratica.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Aggiungiamo qualche riga:

INSERT INTO courses (name, tags)
VALUES
    ('Basi di SQL', ARRAY['SQL', 'PostgreSQL', 'Basi di dati']),
    ('Lavorare con Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Sviluppo in Python', ARRAY['Python', 'Web', 'Dati']),
    ('Corso su PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Ecco come appare la tabella:

Senza indice: ricerca lenta

Ora immaginiamo di voler trovare tutti i corsi che hanno il tag SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

La query funziona, ma se hai tanti dati sarà lentissima. PostgreSQL farà uno scan sequenziale (Sequential Scan), cioè controllerà ogni riga della tabella.

Risultato della query:

Creazione indice GIN

Per velocizzare la ricerca, creiamo un indice di tipo GIN:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Riproviamo la stessa query:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Ora PostgreSQL userà l’indice GIN appena creato, e il tempo di risposta cala di brutto.

Prima si usava lo scan sequenziale (Seq Scan), ora nel piano di esecuzione vedrai Bitmap Index Scan:

Come funzionano gli operatori con gli indici?

Esempio 1: Operatore @>

Query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

L’indice GIN è perfetto per questo operatore. Postgres controlla velocemente quali righe hanno l’elemento richiesto e ti dà il risultato.

Risultato della query:

Esempio 2: Operatore &&

Query:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Questo operatore controlla se ci sono elementi in comune tra gli array: restituisce le righe dove tags si incrocia almeno con un elemento dell’array passato.

L’indice GIN fa di nuovo la magia — la ricerca è veloce anche con tanti dati.

Risultato della query:

Indicizzazione e ottimizzazione

Quando lavori con gli array, segui questi consigli:

1. Usa indici GIN per cercare dentro gli array. Sono molto più veloci dello scan sequenziale.
2. Aggiungi indici solo sulle colonne che usi spesso nelle query. Gli indici occupano spazio e rallentano gli insert, quindi non indicizzare tutto a caso.
3. Profiling delle query con EXPLAIN e EXPLAIN ANALYZE, così vedi se davvero l’indice viene usato.

Esempi: creare indici per gli array

Vediamo come creare indici per tipi specifici di operazioni sugli array e perché serve davvero nella pratica.

Indice per operatore @>

Supponiamo di avere già questa tabella courses:

Per velocizzare le query con l’operatore @> (array contiene elemento), creiamo un indice GIN:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Ora facciamo la query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Risultato:

Indice per operatori @>, <@, e &&

La tabella è la stessa dell’esempio precedente.

Visto che gli operatori @>, <@ e && funzionano tutti bene con gli indici GIN, puoi creare un unico indice universale che velocizza tutte queste query:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Esempi di query e risultati:

• @> — controlla se l’array contiene gli elementi indicati:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — controlla se l’array è contenuto in un altro array:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — controlla se ci sono elementi in comune tra array:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Proviamo qualcosa di più tosto

Facciamo una query che restituisce i corsi dove tra i tag c’è almeno un incrocio con la lista ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Output:

Con l’indice GIN questa query vola, anche con milioni di righe.

Errori tipici con gli array

Indice non usato: se nel risultato di EXPLAIN vedi Seq Scan, controlla che l’indice sia stato creato e che l’operatore che usi supporti davvero l’indicizzazione.

Uso raro dell’array: se la colonna array viene usata poco nelle query o viene aggiornata spesso, l’indice può solo occupare spazio senza portare vantaggi.

Indici inutili: gli indici occupano spazio su disco e rallentano le scritture, quindi crea solo quelli che ti servono davvero e che userai nelle query.

Ora hai tutti gli strumenti per lavorare in modo efficace con gli array in PostgreSQL — velocizza le query con gli operatori @>, <@, && e gli indici GIN. Non aver paura di provarli sui tuoi dati!