Nuvarande PostgreSQL-versioner (inklusive 9.6) lider av en svaghet i frågeoptimeraren angående TOAST-tabeller. TOAST-tabeller är ett slags "utökningsrum" som används för att lagra stora (i betydelsen datastorlek) värden som inte passar in på normala datasidor (som långa texter, bilder eller komplexa geometrier med många hörn), se PostgreSQL-dokumentationen för TOAST för mer 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.
För tabeller som mestadels är skrivskyddade och där ett enda index används för majoriteten av frågorna erbjuder PostgreSQL CLUSTER-kommandot. Detta kommando ordnar fysiskt om alla datarader i samma ordning som indexkriterierna, vilket ger två prestandafördelar: För det första, för indexintervallskanningar, minskas antalet sökningar i datatabellen drastiskt. För det andra, om din arbetsuppsättning koncentreras till vissa små intervall i indexen, får du en effektivare cachelagring eftersom dataraderna sprids över färre datasidor. (Känn dig inbjuden att läsa CLUSTER-kommandodokumentationen från PostgreSQL-manualen vid denna tidpunkt.)
Men för närvarande tillåter PostgreSQL inte klustring på PostGIS GIST-index eftersom GIST-index helt enkelt ignorerar NULL-värden, du får ett felmeddelande som:
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.
Som HINT-meddelandet säger kan man komma runt den här bristen genom att lägga till en "not null"-begränsning i tabellen:
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
Det här fungerar naturligtvis inte om du faktiskt behöver NULL-värden i din geometrikolumn. Dessutom måste du använda ovanstående metod för att lägga till begränsningen, att använda en CHECK-begränsning som "ALTER TABLE blubb ADD CHECK (geometry is not null);" fungerar inte.
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.