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).
Problemet uppstår om du råkar ha en tabell med ganska stora geometrier, men inte alltför många rader av dem (som en tabell som innehåller gränserna för alla europeiska länder i hög upplösning). Då är själva tabellen liten, men den använder massor av TOAST-utrymme. I vårt exempel hade tabellen i sig cirka 80 rader och använde bara 3 datasidor, men TOAST-tabellen använde 8225 sidor.
Ställ nu en fråga där du använder geometrioperatorn & & för att söka efter en avgränsningsbox som bara matchar mycket få av dessa rader. Nu ser frågeoptimeraren att tabellen bara har 3 sidor och 80 rader. Den uppskattar att en sekventiell skanning på en så liten tabell är mycket snabbare än att använda ett index. Och så beslutar den att ignorera GIST-indexet. Vanligtvis är denna uppskattning korrekt. Men i vårt fall måste operatorn hämta varje geometri från disken för att jämföra begränsningsrutorna, vilket innebär att alla TOAST-sidor också måste läsas.
För att se om du lider av den här frågan, använd kommandot "EXPLAIN ANALYZE" postgresql. För mer information och de tekniska detaljerna kan du läsa tråden på PostgreSQL-prestandans e-postlista: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
och nyare tråd om PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html
PostgreSQL-folket försöker lösa detta problem genom att göra frågeuppskattningen TOAST-medveten. För närvarande är här två lösningar:
Den första lösningen är att tvinga frågeplaneraren att använda indexet. Skicka "SET enable_seqscan TO off;" till servern innan du skickar frågan. Detta tvingar i princip frågeplaneraren att undvika sekventiella skanningar när det är möjligt. Så den använder GIST-indexet som vanligt. Men den här flaggan måste ställas in för varje anslutning, och det gör att frågeplaneraren gör felbedömningar i andra fall, så du bör "SET enable_seqscan TO on;" efter frågan.
Den andra lösningen är att göra den sekventiella skanningen så snabb som frågeplaneraren tror. Detta kan uppnås genom att skapa en extra kolumn som "cachelagrar" bboxen och matchar mot denna. I vårt exempel ser kommandona ut så här:
SELECT AddGeometryColumn('myschema', 'mytable', 'bbox', '4326', 'GEOMETRY', '2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
Ändra nu din fråga för att använda operatorn & & mot bbox istället för geom_column, som:
SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
Om du ändrar eller lägger till rader i min tabell måste du naturligtvis hålla bbox "synkroniserad". Det mest transparenta sättet att göra detta skulle vara triggers, men du kan också ändra din applikation för att hålla bbox-kolumnen aktuell eller köra UPDATE-frågan ovan efter varje ändring.
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
Ibland råkar du ha 3D- eller 4D-data i din tabell, men du kommer alltid åt dem med OpenGIS-kompatibla ST_AsText()- eller ST_AsBinary()-funktioner som bara matar ut 2D-geometrier. De gör detta genom att internt anropa funktionen ST_Force2D(), som medför en betydande overhead för stora geometrier. För att undvika denna overhead kan det vara möjligt att i förväg släppa dessa extra dimensioner en gång för alla:
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
Observera att om du har lagt till din geometrikolumn med AddGeometryColumn() kommer det att finnas en begränsning för geometridimensionen. För att kringgå den måste du släppa begränsningen. Kom ihåg att uppdatera posten i geometry_columns-tabellen och återskapa begränsningen efteråt.
När det gäller stora tabeller kan det vara klokt att dela upp denna UPDATE i mindre delar genom att begränsa UPDATE till en del av tabellen via en WHERE-klausul och din primära nyckel eller andra genomförbara kriterier och köra en enkel "VACUUM;" mellan dina UPDATE. Detta minskar drastiskt behovet av tillfälligt diskutrymme. Om du har geometrier med blandade dimensioner kan du dessutom begränsa UPDATE med "WHERE dimension(geom)>2" och slippa skriva om geometrier som redan finns i 2D.