PostgreSQL 9.5 accorcia le distanze!

Il 7 gennaio è stata rilasciata la versione 9.5.0 di PostgreSQL! Quale migliore occasione per continuare a testare le nuove funzionalità introdotte.

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.