Le attuali versioni di PostgreSQL (compresa la versione 8.0) sono affette da un difetto del query optimizer riguardante le tabelle TOAST. Le tabelle TOAST sono una sorta di "spazio aggiuntivo" usato per memorizzare valori di grandi dimensioni (nel senso di dimensioni dei dati) che non possono essere salvati in normali pagine (per esempio lunghi testi, immagini o geometrie con molti vertici). Vedi la documentazione PostgreSQL sulle tabelle TOAST per maggiori informazioni).
Il problema si presenta nel caso di tabelle contenenti geometrie di grandi dimensioni ma poche righe (per esempio una tabella contenente i confini di tutti gli stati europei ad alta risoluzione). In questo caso infatti la tabella in se è di piccole dimensioni ma usa molto spazio TOAST. Nel nostro esempio la tabella conteneva circa 80 righe e utilizzava solo 3 pagine di dati, ma la tabella TOAST ne utilizzava 8225.
Ora si lanci una query che usi l'operatore && e che [MATCHES] solo poche righe. Il
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
and newer thread on 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 grandi tabelle può essere sensato dividere l'UPDATE in porzioni più piccole, usando la clausola WHERE con la chiave primaria o un altro criterio flessibile per attualizzare solo una parte della tabella , ed eseguendo un semplice "VACUUM;" tra gli UPDATE. Questo accorgimento ridurrà drasticamente il bisogno lo spazio temporaneo sul disco. Inoltre, se la colonna geometrica contiene geometrie con diverse dimensioni, si può limitare l'UPDATE con "WHERE dimension(the_geom)>2", evitando così di riscrivere le geometrie che sono già in 2D.