Gli indici BRIN (Block Range INdex) rappresentano una delle maggiori novità presenti in PostgreSQL 9.5. Si tratta di un nuovo tipo di indice che arricchisce la collezione già presente, aggiungendosi a quelli “ad albero” (btree, GiST/SP-GiST e GIN) ed Hash. Si tratta comunque di un indice molto differente dagli altri: non è basato sui singoli valori che devono essere indicizzati, ma sulle pagine da 8kB di PostgreSQL.

L’algoritmo alla base di questa indicizzazione unisce le caratteristiche della scansione sequenziale dei record di una tabella (SeqScan) con quella basata su un indice ad albero (IndexScan): durante la costruzione dei BRIN, le pagine di PostgreSQL vengono scansionate in blocchi, sequenzialmente, e per ogni blocco vengono mappati gli estremi dei valori contenuti che devono essere indicizzati. In un secondo tempo poi, il planner di PostgreSQL saprà quali sono i blocchi di pagine PostgreSQL che devono essere presi in considerazione durante l’esecuzione delle query.

Esistono due tipi di supporto per questo tipo di indice:

• minmax, che si occupa di mappare i valori minimi e massimi di un attributo indicizzato;
• inclusion, in cui vengono mappati gli estremi dell’intervallo in cui i valori dell’attributo indicizzato possono variare.

La differenza tra quest’ultimo tipo di supporto ed il precedente sta nel fatto che esso permette anche l’indicizzazione di tipi di dato cosiddetti “non-ordinabili”, ovvero che non presentano cardinalità come ad esempio per i numeri o le stringhe.

Per come sono definiti, gli indici BRIN presentano due grandi vantaggi:

• occupano uno spazio su disco notevolmente minore degli altri indici (che hanno invece una dimensione paragonabile a quella dell’intera tabella indicizzata), e dunque sono particolarmente utili nel caso di tabelle molto grandi;
• richiedono poca manutenzione rispetto agli altri indici.

Vari esempi sono stati mostrati sull’uso dei BRIN[¹][²][³], in particolare per il tipo di supporto minmax. Vorrei invece parlare adesso del supporto di tipo inclusion, e di quanto sia utile se si ha a che fare con dati geospaziali.

Un esempio di uso per i punti

I punti, come ogni altra entità geospaziale, soffrono del fatto che non contemplano criteri di ordinamento (non è possibile definire “un punto maggiore di un altro”), per cui non possono essere indicizzabili con le metodologie standard usate ad esempio per gli indici btree.

Esistono tuttavia in PostgreSQL l’indice GiST, che si basa su algoritmi “di ordinamento” quali R-tree ed k-NN, e quello SP-GiST, basato invece su Quad-tree e kd-tree, che sono in grado di indicizzare dati geospaziali. La differenza tra i due indici è data dal fatto che, mentre il primo utilizza una struttura ad albero bilanciato, il secondo ne usa uno non bilanciato.

Le strutture non bilanciate non sono generalmente molto utili quando si ha a che fare con numeri e stringhe; viceversa, tendono ad essere usati in ambito geospaziale, soprattutto per ricerce del tipo “inclusione all’interno di bounding box”.

Il mio intento adesso è di presentare un semplice esempio in cui confrontare le prestazioni ottenibili effettuando questo tipo di ricerche basandosi sugli indici attualmente presenti (GiST, SP-GiST) e sui futuri BRIN sfruttando il supporto di tipo inclusion.

Innanzitutto è necessario installare la versione beta (per il momento in cui è stato scritto questo articolo) di PostgreSQL 9.5: ad esempio, io ho eseguito i miei test su una macchina CentOS 6.5, ed ho dunque installato il repository yum di PGDG per poter accedere ai pacchetti di PostgreSQL 9.5beta[⁴].

Consideriamo poi, ad esempio, di costruire una tabella contenente 10000000 di punti casualmente distribuiti sul piano all’interno di un’area 100unità X 100unità (sfruttiamo qui a titolo di esempio il tipo di dato point presente nel core del database PostgreSQL, tralasciando le geometrie fornite dall’estensione PostGIS):

CREATE TABLE points AS (
SELECT id,
point(100.0 * random(), 100.0 * random()) AS point
FROM generate_series(1,10000000) AS id);

Costruiamo poi sulla colonna point un primo indice di tipo GiST:

CREATE INDEX gist_index ON points USING gist(box(point));

ed un secondo indice di tipo SP-GiST:

CREATE INDEX spgist_index ON points USING spgist(box(point));

Osserviamo dimensioni e tempi di esecuzione nella costruzione dei due indici:

L’indice SP-GiST, essendo non bilanciato, presenta una struttura meno complessa che si traduce in minor spazio occupato e minori tempi di esecuzione per la sua costruzione.

Costruiamo adesso l’indice BRIN sullo stesso campo della tabella:

CREATE INDEX brin_index ON points
USING brin(box(point) box_inclusion_ops);

Da notare la specifica dell’operator class box_inclusion_ops, che detta all’indice quali siano gli operatori con supporto inclusion che dovranno utilizzarlo. È bene precisare che gli indici BRIN vengono costruiti considerando di default blocchi da 128 pagine da 8kB di PostgreSQL: questo significa che potrà restituire un numero di blocchi (fino a 128) che potrebbero anche non contenere i dati richiesti sulla base della ricerca e che quindi, come anticipato sopra, dovranno essere scartati in un secondo tempo dal planner PostgreSQL.

È possibile comunque aumentare la “risoluzione” dell’informazione immagazzinata dall’indice BRIN diminuendo la dimensione del blocco di pagine PostgreSQL usato durante la sua costruzione, in modo da aumentarne l’efficienza di utilizzo: questo a scapito ovviamente della dimensione dell’indice che occuperà più spazio. Proviamo a confrontare come cambia un indice BRIN richiedendo che la dimensione del blocco di pagine PostgreSQL considerate sia pari a 64 (la metà del default) configurando il parametro pages_per_range:

CREATE INDEX brin64_index ON points
USING brin(box(point) box_inclusion_ops)
WITH (pages_per_range = 64);

Il risultato ci sorprende: l’indice BRIN occupa davvero uno spazio molto ridotto, con tempi di esecuzione considerabili “istantanei” rispetto a quelli degli altri indici.

Conclusioni

Proviamo adesso a lanciare la query di ricerca dei punti inclusi all’interno di un quadrato 50×50, “immerso” tra i punti della tabella:

SELECT * FROM points
WHERE box(point) <@ box(point(20, 20), point(70, 70));

Effettivamente notiamo, soffermandoci agli indici finora presenti in PostgreSQL, come la struttura non bilanciata sia più efficiente in questo tipo di ricerche (~50% in meno di tempo necessario):

Confrontiamo i tempi di esecuzione della query sfruttando gli indici BRIN:

Anche qui rimaniamo piacevolmente sorpresi: gli indici BRIN assicurano tempi di esecuzione paragonabili a quelli dell’indice non bilanciato SP-GiST nel caso di ricerche del tipo “inclusione all’interno di bounding box”, ma potendo vantare dimensioni e tempi di costruzione dell’indice stesso praticamente irrisori rispetto all’SP-GiST.

Concludendo, dagli altri blog[⁵][⁶] abbiamo imparato che gli indici BRIN, se usati per ricerche che si basano sul supporto minmax ad esempio su numeri o stringhe, hanno prestazioni generalmente inferiori agli altri indici (esempio il btree) che aumentano man mano che la “risoluzione” del BRIN viene espansa tramite il parametro pages_per_range.

In questo articolo abbiamo visto come gli indici BRIN usati per ricerche che si basano sul supporto inclusion hanno prestazioni del tutto simili a quelle degli altri indici, occupando uno spazio su disco molto inferiore.

In ogni caso, con PostgreSQL 9.5 i BRIN possono vantare miglioramenti in termini di manutenibilità, dimensione e tempi di creazione rispetto agli indici finora presenti in PostgreSQL.