Le versioni attuali di PostgreSQL (compresa la 9.6) soffrono di una debolezza dell'ottimizzatore di query per quanto riguarda le tabelle TOAST. Le tabelle TOAST sono una sorta di "stanza di estensione" utilizzata per memorizzare valori di grandi dimensioni (nel senso della dimensione dei dati) che non si adattano alle normali pagine di dati (come testi lunghi, immagini o geometrie complesse con molti vertici), si veda la documentazione PostgreSQL per TOAST per maggiori informazioni).
Il problema si presenta se si ha una tabella con geometrie piuttosto grandi, ma non troppe righe (come una tabella contenente i confini di tutti i paesi europei in alta risoluzione). In questo caso, la tabella stessa è piccola, ma utilizza molto spazio TOAST. Nel nostro caso di esempio, la tabella stessa aveva circa 80 righe e utilizzava solo 3 pagine di dati, ma la tabella TOAST utilizzava 8225 pagine.
Ora si lanci una query che usi l'operatore && e che trovi solo poche righe. L'ottimizzatore di query ora vede che la tabella ha solo 3 pagine e 80 record. Stima che una scansione sequenziale su una tabella cosi' piccola e' molto piu' veloce rispetto all'uso di un indice, e quindi decide di ignorare l'indice GiST. Normalmente questa stima e' corretta, ma nel nostro caso l'operatorore && deve estrarre ogni geometria dal disco per confrontare i bounding box finendo con il leggere anche tutte le pagine TOAST.
Per controllare se i propri dati sono interessati da questo bug, si può usare il comando PostgreSQL "EXPLAIN ANALYZE". Per maggiori informazioni e dettagli tecnici consultare il corrispondente thread sulla mailing list di PostgreSQL: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
e una discussione più recente su PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html
Gli sviluppatori di PostgreSQL stanno cercando di risolvere il problema rendendo la valutazione della query indipendente dalla tabella TOAST. Per ora ci sono due possibili soluzioni alternative:
La è forzare il query planner ad usare l'indice spaziale usando il comando "SET enable_seqscan TO off;" prima di lanciare la query. Questo comando impedisce al query planner di usare lo scan sequenziale della tabella se possibile e lo forza quindi ad usare l'indice GIST. Tuttavia il comando deve essere lanciato ad ogni connessione e, per evitare di confondere il query planner in altri casi, il parametro deve essere resettato dopo l'esecuzione della query interessata con il comando "SET enable_seqscan TO on;" .
Il secondo metodo è rendere lo scan sequenziale così veloce come il query planner si aspetta che sia. Questo può essere raggiunto aggiungendo una colonna addizionale in cui salvare la bounding box di ogni geometria. Nel nostro esempio i comandi sarebbero:
SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
Ora la query deve essere modificata in modo da usare l'operatore && con la colonna bbox piuttosto che con la colonna geom_column:
SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
Ovviamente la colonna bbox deve essere mantenuta attuale quando si modificano o si aggiungono geometrie. La via più semplice per fare questo sarebbe con un trigger, oppure l'applicazione può essere modificata in modo da attualizzare anche la colonna bbox oppure si può lanciare l'UPDATE precedente dopo ogni modifica.
Per tabelle che vengono per lo più solo lette, e dove un singolo indice è usato dalla maggior parte delle query, PostgreSQL offre il comando CLUSTER. Questo comando riordina fisicamente le righe in modo che l'ordine corrisponda a quello dell'indice. Con questo metodo si migliorano le prestazioni per due motivi: primo, il numero delle ricerche nella tabella dei dati è ridotto drasticamente. Secondo, se i dati interessati dalla query sono concentrati in un piccolo intervallo sull'indice, il processo di mettere in cache sarà più efficiente perché le righe saranno distribuite all'interno di poche pagine. (si invita a leggere la documentazione di PostgreSQL riguardante il comando CLUSTER).
Attualmente però, PostgreSQL non permette di usare il clustering con indici GIST, perché gli indici GIST ignorano i valori nulli:
lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "geom" NOT NULL.
Come suggerito dal messaggio di errore, è possibile aggirare il problema aggiungendo un vincolo "NOT NULL" alla tabella:
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
Ovviamente questo non funzionerà se la colonna the_geom già contiene valori nulli. Inoltre il vincolo dev'essere definito usando il comando precedente. Usare un vincolo CHECK del tipo "ALTER TABLE blubb ADD CHECK (geometry is not null);" non funzionerà.
A volte può accadere di avere dati in 3D o 4D, ma di accederli sempre usando funzioni che danno in output solo geometrie 2D come ST_AsText() oppure ST_AsBinary(). Queste funzioni processano internamente le geometrie eseguendo ST_Force2D() e questo può risultare in un peggioramento delle prestazioni significativo nel caso di geometrie di grandi dimensioni. Per evitare questo problema è consigliabile eliminare le dimensioni non utilizzate una volte e per sempre:
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
Se la colonna di tipo geometry è stata aggiunta usando la funzione AddGeometryColumn(), verrà creato anche un vincolo dimensionale sulla geometria. Per aggirare il vincolo sarà necessario cancellarlo. Ricorda di attualizzare il record nella tabella geometry_columns e di ricreare il vincolo successivamente.
Nel caso di tabelle di grandi dimensioni, può essere opportuno dividere l'UPDATE in porzioni più piccole, vincolando l'UPDATE a una parte della tabella tramite una clausola WHERE e la chiave primaria o un altro criterio fattibile ed eseguendo un semplice "VACUUM;" tra gli UPDATE. In questo modo si riduce drasticamente la necessità di spazio temporaneo su disco. Inoltre, se si hanno geometrie di dimensioni miste, limitando l'AGGIORNAMENTO con "WHERE dimension(geom)>2" si evita di riscrivere le geometrie che sono già in 2D.