Già nel mio precedente articolo sui BRIN ho evidenziato le novità introdotte soprattutto in ambito geospaziale con la 9.5. Oggi mostrerò una funzionalità della nuova versione di PostgreSQL che amplia ancora di più gli strumenti messi a disposizione in ambito GIS: la distanza punto-poligono, definita matematicamente come la minima delle distanze tra il punto di interesse e ciascun punto del poligono. Sebbene PostgreSQL abbia già nativamente introdotto alcune geometrie bidimensionali (point, circle, polygon, …), per poter calcolare la distanza di un punto da un poligono in 2D, fino ad oggi, era necessaria l’installazione di PostGIS (per esempio per trovare i punti di interesse più vicini ad un dato perimetro, o viceversa). Con PostgreSQL 9.5 non sarà più strettamente necessaria l’installazione di PostGIS (per quanto utilissima in ambito GIS) per questo tipo di operazioni.

La patch per il calcolo delle distanze tra point e polygon è stata introdotta da Alexander Korotkov, nome già noto nel mondo PostgreSQL soprattutto per i suoi lavori sugli indici GiST. Alexander ha implementato un algoritmo che iterativamente calcola la distanza del punto dai singoli segmenti, per poi prenderne la minima; tecnicamente, ha esteso l’overloading dell’operatore <-> in modo che non fosse limitato al calcolo della distanza fra coppie di geometrie dello stesso tipo point o polygon.

Cerchiamo di capire con un piccolo esempio come funziona, utilizzando uno script che permette di creare 10 milioni di quadrati con lato di lunghezza unitaria uniformemente distribuiti sul piano. La creazione della tabella sul mio desktop è stata completata in circa 13 minuti. Cerchiamo adesso quali sono i 10 quadrati più vicini all’origine (ovvero il punto di coordinate x=0, y=0), ossia facciamo una ricerca di tipo “the k nearest neighbours” (kNN):

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

L’esecuzione della query ha richiesto, sul mio desktop, circa 17 secondi. Il piano di esecuzione prevede ovviamente un sequential scan di tutta la tabella per la ricerca:

QUERY PLAN    --------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505032.60..505032.62 rows=10 width=101) (actual time=16505.291..16505.976 rows=10 loops=1)
       ->  Sort  (cost=505032.60..530032.69 rows=10000035 width=101) (actual time=16504.416..16504.420 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 26kB
             ->  Seq Scan on polygons  (cost=0.00..288935.44 rows=10000035 width=101) (actual time=0.533..13198.879 rows=10000000 loops=1)

Uso degli indici

La patch di Alexander introdotta in PostgreSQL non prevede il supporto per gli indici GiST per l’operatore <-> in ricerche kNN tra point e polygon: proviamo ad esempio a costruire l’indice sui polygon (sul mio desktop l’indicizzazione ha richiesto circa 30 minuti):

CREATE INDEX gist_index
ON polygons
USING gist(polygons);

Rilanciamo la stessa ricerca “nearest neighbours” di prima:

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

Il tempo di esecuzione è stato nuovamente di 17 secondi, gli stessi del caso in assenza di indici. Infatti se andiamo a vedere quale è stato il piano di esecuzione troveremo che nuovamente è stata eseguita una sequential scan:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505031.61..505031.63 rows=10 width=101) (actual time=15284.445..15285.214 rows=10 loops=1)
       ->  Sort  (cost=505031.61..530031.62 rows=10000006 width=101) (actual time=15283.761..15283.765 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Seq Scan on polygons  (cost=0.00..288935.08 rows=10000006 width=101) (actual time=0.236..12805.262 rows=10000000 loops=1)

Conclusioni

PostgreSQL 9.5 ha introdotto la possibilità di effettuare ricerche kNN anche tra point e polygon sul piano, senza necessariamente installare PostGIS. Sebbene le ricerche kNN tra point o circle supportano l’indice GiST, ad oggi ciò non è possibile per quelle miste, tra point e polygon, in quanto richiedono una ricerca basata sul sequential scan completo dei dati.

Questa situazione sta per essere risolta. Alexander ha già presentato una patch in cui vuole introdurre una modifica degli indici GiST per supportare ricerche del tipo kNN-GiST with recheck [¹] tra geometrie miste. Questo dovrebbe, tra le altre cose, rendere l’indice GiST utilizzabile anche per ricerche kNN tra point e polygon.

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.

La clausola WITHIN GROUP

La clausola WITHIN GROUP è particolarmente utile nei casi in cui si vogliano effettuare aggregazioni su subset ordinati di dati.
PostgreSQL ha introdotto, fin dalla versione 9.0, le window function per poter lavorare su subset di dati correlabili a ciascun record corrente delle tabelle, definendo una sorta di “finestre di aggregazione” centrate su ogni specifico record man mano che la query viene eseguita tramite la clausola SQL OVER (PARTITION BY/ORDER BY) e sfruttando tali funzioni che possono essere eseguite su tali aggregazioni.

Con la versione 9.4 di PostgreSQL è stata introdotta la clausola SQL WITHIN GROUP che permette di semplificare molte operazioni finora possibili solo con l’uso delle window function, definendo aggregazioni di subset ordinati di dati.
Sono state introdotte, inoltre, nuove funzioni che possono essere applicate su tali subset e che ampliano la collezione delle window function presenti:

• percentile_cont(), percentile_disc() per il calcolo di percentili;
• mode() funzione statistica che calcola la moda su subset ordinati;
• rank(), dense_rank(), percent_rank(), cume_dist(): window function già presenti in PostgreSQL per essere eseguite sui subset ottenuti tramite la clausola OVER (PARTITION BY/ORDER BY ) e da adesso in grado di prendere come parametro subset ordinati prodotti con la clausola WITHIN GROUP.

Per capire meglio, supponiamo ad esempio di voler calcolare il 25°, il 50°, il 75° ed il 100° percentile dei primi 20 numeri interi. Finora era possibile solo partizionando i numeri in 4 set tramite la clausola OVER (PARTITION BY/ORDER BY) per poi ordinarli internamente in 4 subset ordinati di cui poi prendiamo il massimo valore, ad esempio sfruttando una CTE:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ WITH subset AS (
    SELECT val,
       ntile(4) OVER (ORDER BY val) AS tile
    FROM t
  )
  SELECT max(val)
  FROM subset GROUP BY tile ORDER BY tile;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Con PostgreSQL 9.4 tutto si riduce ad un solo comando SQL, comportando notevoli vantaggi in termini di leggibilità degli script e di esecuzione dei comandi:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
    WITHIN GROUP (ORDER BY val))
  FROM t;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Clausola FILTER di SQL

Questa seconda clausola dei comandi SQL è utile nei casi in cui si vogliano applicare dei filtri su subset di dati senza necessariamente eseguire aggregazioni.
Ad esempio, è ora possibile effettuare un count totale dei record di una tabella ed anche parziale di un suo subset che soddisfi una certa condizione (espressa mediante la clausola WHERE) all’interno di una unica query, senza doverne usare ulteriori da eseguire sulle aggregazioni:

$ SELECT count(*) count_all,
         count(*) FILTER(WHERE bid=1) count_1,
         count(*) FILTER(WHERE bid=2) count_2
  FROM pgbench_history;

 count_all | count_1 | count_2
 ----------+---------+---------­­­­­­­­­
      7914 |     758 |     784
 (1 row)

Semplificando, anche in questo caso, la leggibilità degli script e migliorando le performance in esecuzione.

Conclusioni

L’estensione dello standard SQL tramite l’introduzione di queste nuove clausole facilita ulteriormente il compito degli sviluppatori, che si trovano a poter delegare sempre più al database la manipolazione e l’aggregazione di subset di dati.
Con la clausola WITHIN GROUP diventa più semplice la gestione di subset di dati ordinabili, introducendo nuove window function. La clausola FILTER facilita la gestione di subset di dati che soddisfano certe condizioni, evitando le aggregazioni.

A volte scrivere le funzioni in SQL è molto utile. SQL è più semplice rispetto ai linguaggi procedurali, ha meno richieste in termini di memoria e risulta molto più veloce.

Noi di 2ndQuadrant Italia utilizziamo un approccio agile e collaborativo alla programmazione, questo implica che diverse persone all’interno del team debbano mettere le mani sullo stesso codice, anche a settimane di distanza. Per questo sentiamo la necessità di scrivere codice che sia chiaro e leggibile.

In PostgreSQL 9.2 la scrittura di funzioni in SQL è stata resa più facile aggiungendo la possibilità di usare parametri denominati (in inglese named parameter).

Prima della 9.2 era necessario utilizzare $1 per riferirsi al primo parametro della funzione, $2 per il secondo, e così via.

Questo ha sempre creato grossi problemi in termini di manutenibilità del codice.

Un esempio di utilizzo degli argomenti denominati è descritto di seguito, tramite la definizione di una funzione SQL che controlla il valore di md5sum rispetto a due file, restituendo TRUE in caso sia uguale, FALSE altrimenti.

Tenete presente che i file devono risiedere sul server e il percorso dei file deve essere relativo alla directory PGDATA di PostgreSQL (per motivi di sicurezza).

BEGIN;
CREATE OR REPLACE FUNCTION check_md5( _file1 TEXT, _file2 TEXT )
   RETURNS BOOLEAN LANGUAGE SQL immutable AS
$BODY$

SELECT CASE WHEN md5(pg_read_binary_file( _file1 )) = md5(pg_read_binary_file( _file2 ))
   THEN TRUE ELSE FALSE END;
$BODY$;

SELECT check_md5('../img/a.jpg','../img/b.jpg');
SELECT check_md5('../img/a.jpg','../img/c.jpg');
SELECT check_md5(NULL,'../img/c.jpg');
ROLLBACK;

Questa nuova funzionalità renderà molto più leggibile e manutenibile il codice, e per noi non può che essere un’ottima notizia!

L’azienda, che si occupa di formazione di livello avanzato nel settore informatico, sta organizzando nei prossimi giorni un corso con un ospite eccezionale che i professionisti e gli appassionati di database non possono ignorare: Chris Date.

Il noto esperto e ricercatore di database relazionali (e annessa teoria), nonché autore di diversi libri di successo su SQL e database, terrà un corso di tre giorni intitolato "SQL e Teoria Relazionale: Come scrivere un corretto SQL".

Purtroppo non potrò partecipare al corso, ma mi sento di pubblicizzare l’evento verso tutti gli utilizzatori di PostgreSQL per due ottimi motivi:

• PostgreSQL permette di usare gran parte dello standard SQL e quindi la fruizione del corso è completa
• la qualità e l’organizzazione dei corsi Technology Transfer è veramente di altissimo livello, un vero e proprio punto di riferimento per le aziende che come 2ndQuadrant fanno formazione avanzata in IT.

Si tratta di un progetto pluriennale che coinvolgerà sia il personale di 2ndQuadrant che i volontari dell’associazione e della comunità di PostgreSQL.

La prima fase che durerà diversi mesi (variabile tra i 18 e i 24 mesi) concerne la completa traduzione della documentazione relativa versione attualmente in fase di sviluppo.

Una volta terminata la prima fase, inizierà il lavoro costante di manutenzione e aggiornamento multi-versione della documentazione.

Il team attuale per la traduzione è composto da Carlo Ascani, Cosimo D’Arcangelo e Marco Coratella.

La revisione e la supervisione sono al momento affidate a Gabriele Bartolini e Gianni Ciolli.

L’obiettivo è quello di promuovere l’adozione di PostgreSQL in Italia anche per scopi didattici, ritenendo che la mancanza di una documentazione in italiano costituisca una rilevante barriera all’apprendimento.

Il progetto è aperto a tutti e si invitano sia i soci dell’associazione che gli appassionati di PostgreSQL in generale a partecipare, adottando anche un singolo capitolo. Per informazioni, è sufficiente inviare una e-mail a info@itpug.org.

Ecco una lista delle principali novità in ambito SQL di PostgreSQL 8.4.

Funzioni finestra (Windowing Functions)

Anche conosciute con il termine "windowing aggregate", le funzioni finestra permettono di effettuare operazioni di aggregazione (come count(), sum(), ecc.) e di rango come (rank() e row_number()) su un sottoinsieme dei dati (la cosiddetta finestra o window).

A livello pratico, questo comporta che report multi-livello che in precedenza avrebbero richiesto 3 o 4 query (e possibilmente la scrittura di procedure), possano essere ora generati con una singola query.

Le funzioni finestra ampliano il numero di applicazioni di Business Intelligence e supporto alle decisioni che PostgreSQL è in grado di supportare.

Approfondimento su Window Function e sulla sintassi delle chiamate.

Common Table Expression (CTE) e query ricorsive

Le Common Table Expression, anche conosciute con il nome di query WITH, permettono la creazione di subquery e di assegnar loro un nome. Le subquery possono a loro volta essere referenziate all’interno delle clausole della query alla quale appartengono.

Oltre a rimuovere la necessità di creare tabelle temporanee per alcune operazioni, le Common Table Expression consentono di eseguire query ricorsive nelle quali poter attraversare strutture ad albero o grafi all’interno di una singola query, in modo efficiente. Ciò risulta particolarmente importante per tutte le applicazioni che hanno dati organizzati in strutture gerarchiche come forum, gestori di file e organigrammi.

Approfondimento su Common Table Expression

Comando TABLE

Come specificato dallo standard SQL, il comando TABLE nome_tabella esegue la stessa identica mansione del comando SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM nome_tabella.

Il comando SELECT

ALTER SEQUENCE RESTART e TRUNCATE TABLE RESTART IDENTITY

Tramite le istruzioni ALTER SEQUENCE RESTART e TRUNCATE TABLE RESTART IDENTITY è adesso possibile azzerare in modo semplice le sequenze, ripristinando il valore iniziale. La prima agisce sull’oggetto sequenza, la seconda azzera la sequenze associate alle colonne della tabella che si intende svuotare.

Per approfondimenti:

• Il comando ALTER SEQUENCE

• Il comando TRUNCATE

Aggiunta di una colonna con ALTER VIEW

Permette di aggiungere colonne alla fine di una vista esistente, senza dover ricostruire le dipendenze della vista. Modifiche o rimozioni di colonne continuano a richiedere la ricostruzione delle dipendenze.

LIMIT (espressione o subquery)

Adesso è possibile limitare il numero di righe restituite da una interrogazione al database utilizzando una espressione oppure addirittura una subquery. In precedenza, LIMIT vincolava all’utilizzo di una costante numerica. Questa modifica rende più facile per una singola vista o stored procedure ad esempio di supportare in modo dinamico meccanismi di paginazione.

Esempio per il recupero del primo 10% dei record della tabella notizie: SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM notizie ORDER BY orario DESC LIMIT (SELECT count(*) / 10 FROM notizie);

Approfondimento su clausola LIMIT

Parola chiave AS opzionale per alias di colonna

Questa funzionalità, che renderà il passaggio da MySQL a PostgreSQL meno doloroso, rende opzionale la specifica della parola chiave "AS" nell’assegnazione di alias di colonna all’interno delle query.

Esempio: SELECT tablename tabella FROM pg_tables;

Il comando SELECT

Migliorata la conformità rispetto alla standard SQL per la gestione degli intervalli temporali

La specifica degli intervalli temporali è stata potenziata e resa più conforme rispetto allo standard SQL. E’ stato inoltre aggiunto il supporto per la specifica di intervalli secondo lo standard ISO 8601.

Esempio di specifica di intervallo secondo lo standard ISO 8601: SELECT INTERVAL 'P2Y1M1DT4H20M7.5S';

Tipi di dato per date e orari

Questo articolo è una traduzione da me riadattata del documento "What’s new in 8.4" del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli "Waiting for 8.4".