Wzrost wydajności dzięki wskaźnikowi GIST dla punktu w zapytaniu wielokąta

Mam dwie tabele: lokalizacje (id, region_id, the_geom) i regiony (id, the_geom). Dla każdego punktu lokalizacji chcę określić region, w którym się znajduje:

UPDATE locations SET region_id = 
 (SELECT id FROM regions 
  WHERE ST_Within(locations.the_geom,regions.the_geom)
 );

Czy ma sens budowanie indeksu GIST na punktach lokalizacji? Zbuduję indeks na wielokątach regionu, ale nie jestem pewien co do punktów. Czy przyspieszyłoby to zapytanie?

— podmrok
źródło

Krótka odpowiedź: Nie. W przypadku tego typu zapytania UPDATE aktualizujemy każdy wiersz w locations(„Seq Scan”), a indeks GiST włączony the_geomw in regionsjest wystarczający, aby pomóc ograniczyć wiersze, od których ST_Withinwarunek może sparować odpowiedni wiersz regions.

Dłuższa odpowiedź: magią tego rozwiązania jest porównanie tego, co otrzymujesz z zapytania wyjaśniającego . W pgAdmin III znajduje się przycisk „Wyjaśnij zapytanie” w górnej części edytora zapytań lub w pgsql, po prostu poprzedź zapytanie słowem „wyjaśnij”:

postgis=# explain UPDATE locations SET region_id =
postgis-#  (SELECT id FROM regions
postgis(#   WHERE ST_Within(locations.the_geom, regions.the_geom)
postgis(#  );
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on locations  (cost=0.00..8755.54 rows=1000 width=110)
   SubPlan 1
     ->  Index Scan using regions_gist_the_geom on regions  (cost=0.00..8.52 rows=1 width=4)
           Index Cond: ($0 && the_geom)
           Filter: _st_within($0, the_geom)
(5 rows)

Nie musisz rozumieć wszystkiego, co jest tutaj zakaszlowane. Kluczową rzeczą do zobaczenia jest to, że w najbardziej wewnętrznej części (SubPlan 1) wskazuje „Indeks” (= używa indeksu, który może znacznie przyspieszyć), a nie „Skan sekwencyjny” (= skanowanie sekwencji, tj. Sprawdzanie każdego wiersz, aby zobaczyć, czy jest w środku, co może być wolniejsze). Jeśli dodasz / usuniesz indeks GiST locations, wynik tego zapytania wyjaśniającego jest dokładnie taki sam, więc wydajność zapytania powinna być taka sama.

Jeśli jednak zrobisz coś głupiego i usuniesz swój indeks GiST regions, zobaczysz inny plan zapytań z tego samego zapytania, co powyżej:

                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on locations  (cost=0.00..74288.00 rows=1000 width=110)
   SubPlan 1
     ->  Seq Scan on regions  (cost=0.00..74.05 rows=1 width=4)
           Filter: (($0 && the_geom) AND _st_within($0, the_geom))
(4 rows)

Ważną rzeczą do zobaczenia między tymi dwoma zapytaniami wyjaśniającymi są szacunkowe koszty maksymalne .. kontrast 74,05 tutaj do 8,52 wcześniej, więc można oczekiwać, że to zapytanie będzie wolniejsze.

— Mike T.
źródło