Jestem początkującym w postgis i mam problem z wydajnością zapytań.

To moje zapytanie:

SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes here',4326),position) 
ORDER BY userid, timestamp desc

problem polega na tym, że mój wielokąt zawiera BARDZO duże wielokąty (600 stron długich w dokumencie doc!), a wykonanie zajęło ponad 2 godziny!

Czy istnieje sposób na zoptymalizowanie mojego zapytania lub użycie innego sposobu?

Proszę bardzo o pomoc!

To, co powinieneś zrobić, to umieścić swój duży multipolygon w tabeli jako pojedyncze wielokąty (z ST_Dump) i umieścić na nim indeks. Coś jak:

CREATE TABLE big_polygon as
SELECT (ST_Dump( ST_GeomFromText('a multiypolygon geom goes here',4326))).geom as geom;

-- It is always great to put a primary key on the table
ALTER table big_polygon ADD Column gid serial PRIMARY KEY;

-- Create the index
CREATE INDEX idx_big_polygon_geom
on big_polygon
USING gist(geom);

-- To give the database some information about how the index looks
analyze big_polygon;

-- Then you go:
SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1, big polygon WHERE ST_Intersects ( big_polygon.geom,position) 
ORDER BY userid, timestamp desc;

Powinno to być znacznie szybsze z kilku powodów.

To zależy od tego, jakiej jakości - precyzji potrzebujesz. Możesz oczywiście uprościć wielokąty, używając: http://postgis.net/docs/ST_Simplify.html

To, co często robiłem podczas opracowywania mojej aplikacji GIS, to zastanawianie się, jak najlepiej zminimalizować dane. Na przykład. na przykład wstępnie zaznacz wielokąty w polu granicy. - W zależności od poziomu powiększenia nie potrzebujesz ultra precyzyjnych wyników (st_simplify), więc ...

Mam nadzieję, że ci trochę pomogło!

W zależności od twojej postgresu i / lub wiedzy SQL masz kilka opcji:

1. przeanalizuj zapytanie za pomocą polecenia EXPLAIN, aby dowiedzieć się, czy napotkasz określone wąskie gardło. Ostrzeżenie: czasami wynik działania WYJAŚNIENIA może być trudny do zrozumienia

2. jeśli spodziewasz się, że większość lub znaczna część geometrii w tabeli 1 NIE przecina wieloboku, możesz spróbować zastosować warunek wstępny wobec prostszego wielokąta (tj. rozbijając multiploygon na mniejsze kawałki), a następnie uruchomić cięższe przecięcie wieloboku tylko na te wyniki. Przykład poniżej.

3. wtedy i tylko wtedy, gdy wąskim gardłem jest procesor (tzn. serwer utknął w obliczeniach) Tępo sugeruję, aby uzyskać większy, szybszy, mocniejszy procesor lub wynająć jednorazową instancję o wysokiej wydajności poza EC2 Amazon i zniszczyć go, gdy jesteś gotowy

Przykładowe zapytanie do pozycji 2:

SELECT DISTINCT ON (st1.userid) st1.userid ,ST_AsText(st1.position), st1.timestamp  
FROM (
    select userid, position, timestamp from table1 
    WHERE ST_Intersects ( YOUR_MULTIPOL_BOUNDS_HERE,position)
) as st1 
WHERE ST_Intersects ( ST_GeomFromText('a multiypolygon geom goes     here',4326),st1.position) 
ORDER BY st1.userid, st1.timestamp desc

Aby poprawić wydajność, możesz również tymczasowo zmaterializować podselekcję st1 jako tabelę, abyś mógł ją indeksować.

@Nicklas słusznie podkreśla w komentarzach, że przykład sugestii 2 nie powinien pomóc. Ma rację, ale myślę, że ja też (częściowo) mam rację.

W rzeczywistości wydaje się, że bardzo podobne pytanie zostało zadane (i udzielono odpowiedzi) w listopadzie zeszłego roku na postgis ML:

http://postgis.refractions.net/pipermail/postgis-users/2011-November/031344.html

i okazuje się, że sugeruje się rozbicie wielokąta, aby indeks mógł najskuteczniej odfiltrować fałszywe przecięcia, które w innym przypadku zostałyby uruchomione przez proste sprawdzenie granicy.

Za pomocą ST_SubDivide()

W wersji 2.2 Postgis można użyć ST_SubDivide.

ST_Subdivide - Zwraca zestaw geometrii, w którym żadna geometria w zestawie nie ma więcej niż określoną liczbę wierzchołków.

setof geometry ST_Subdivide(geometry geom, integer max_vertices=256);

Ty też możesz

• użyj tabeli temp
• indeks

Tutaj używamy ST_SubDividedo rozbicia wielokąta na subpoligony o 10 lub mniejszej liczbie wierzchołków.

CREATE TEMP TABLE divided AS
SELECT ST_SubDivide(bigmultipolygon,10)::geometery AS t(geom);

CREATE INDEX divided_idx ON divided USING gist(geom);

Następnie

SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp  
FROM table1
JOIN divided AS d
  ON ST_Intersects( d.geom, position )
ORDER BY userid, timestamp desc;

Nie rób powyższego, wprowadza błędy zaokrąglania

Ogólne strojenie

Zobacz także sekcję zatytułowaną Wskazówki dotyczące wydajności w dokumentach. Upewnij się, że jesteś odpowiednio dostrojony. Rozważ podniesienie, max_parallel_workers_per_gatheraby skorzystać z równoległości (obecnie domyślnie jest wyłączone).