Current PostgreSQL versions suffer from a query optimizer weakness regarding TOAST tables. TOAST tables are a kind of "extension room" used to store large (in the sense of data size) values that do not fit into normal data pages (like long texts, images or complex geometries with lots of vertices), see the PostgreSQL Documentation for TOAST for more information).
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.
The PostgreSQL CLUSTER command physically reorders table rows according to an index. For a mostly read-only table whose queries frequently use the same spatial index, this can reduce I/O and improve cache locality.
PostgreSQL supports clustering on GiST indices, including indices on nullable geometry columns. To cluster a table using a geometry index:
CLUSTER my_table USING my_geom_index;
CLUSTER
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.