SQL Academy · Lekcja

Indeksy przestrzenne (GiST)

Przyspiesz zapytania dotyczące lokalizacji

Indeksy przestrzenne (GiST) to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Dlaczego zapytania dotyczące lokalizacji działają wolno

Załóżmy, że tabela zawiera miliony lokalizacji restauracji. Jeśli zapytanie brzmi „Znajdź wszystkie restauracje w promieniu 5 km ode mnie”, baza danych musi sprawdzić każdy wiersz, aby obliczyć odległość. Nazywa się to skanowaniem sekwencyjnym i wraz ze wzrostem tabeli staje się boleśnie wolne.

Indeksy przestrzenne rozwiązują ten problem, organizując dane geometryczne w strukturę drzewa, dzięki czemu baza danych może natychmiast pomijać duże fragmenty tabeli.

Czym jest indeks GiST

GiST to skrót od Generalized Search Tree. Jest to elastyczny mechanizm indeksowania wbudowany w PostgreSQL, który obsługuje wiele typów danych, w tym kształty geometryczne i geometrię PostGIS.

W przeciwieństwie do indeksu B-tree (który działa na wartościach, które można sortować, takich jak liczby całkowite lub ciągi znaków) GiST może indeksować dane wielowymiarowe, takie jak punkty, wielokąty i linie. PostGIS wewnętrznie używa GiST do tworzenia indeksów przestrzennych.

Tworzenie indeksu przestrzennego

Utworzenie indeksu GiST na kolumnie z geometrią jest proste. Należy użyć CREATE INDEX z klauzulą USING gist. Ta pojedyncza instrukcja może skrócić czas wykonywania zapytania z minut do milisekund.

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Jak działa GiST: prostokąty ograniczające

Przestrzenny indeks GiST nie przechowuje dokładnych geometrii. Zamiast tego przechowuje prostokąty ograniczające — najmniejsze prostokąty obejmujące poszczególne geometrie. Drzewo jest tworzone przez grupowanie zbliżonych prostokątów ograniczających na każdym poziomie.

Podczas wykonywania zapytania PostgreSQL schodzi w dół drzewa, odrzucając gałęzie, których prostokąty ograniczające nie nakładają się na obszar wyszukiwania. Następnie dokładnie sprawdzane są tylko pozostałe wiersze kandydujące. Takie dwuetapowe podejście (przeszukanie indeksu i ponowne sprawdzenie) jest niezwykle wydajne.

Konfigurowanie przykładowej tabeli

Zanim przeanalizujemy działanie indeksu, utwórzmy przykładową tabelę punktów reprezentujących miasta i wypełnijmy ją kilkoma wierszami. Kolumna geom przechowuje każde miasto jako obiekt typu Point w układzie WGS 84 (SRID 4326).

CREATE TABLE cities (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  geom GEOMETRY(Point, 4326)
);

INSERT INTO cities (name, geom) VALUES
  ('Paris',    ST_SetSRID(ST_MakePoint(2.3522,  48.8566), 4326)),
  ('Berlin',   ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)),
  ('Madrid',   ST_SetSRID(ST_MakePoint(-3.7038, 40.4168), 4326)),
  ('Rome',     ST_SetSRID(ST_MakePoint(12.4964, 41.9028), 4326)),
  ('Warsaw',   ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326));

Dodawanie indeksu GiST

Po wypełnieniu tabeli należy dodać indeks GiST dla kolumny geom. W przypadku tabel produkcyjnych zawierających miliony wierszy wykonanie tej instrukcji może potrwać kilka minut, ale trzeba ją uruchomić tylko raz. Od tego momentu każde zapytanie przestrzenne dotyczące tej kolumny będzie automatycznie korzystać z indeksu.

CREATE INDEX idx_cities_geom
  ON cities
  USING gist (geom);

-- Verify the index exists
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'cities';

Operator prostokątów ograniczających &&

PostGIS udostępnia operator && do sprawdzania, czy dwa prostokąty ograniczające nakładają się na siebie. Ten operator uwzględnia indeks — optymalizator automatycznie korzysta z indeksu GiST. Jest znacznie szybszy niż obliczanie dokładnych przecięć geometrii i często służy jako szybki filtr wstępny.

-- Find cities whose bounding box overlaps a search rectangle
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Wyszukiwanie najbliższego sąsiada za pomocą <->

Operator <-> zwraca odległość między dwiema geometriami i również korzysta z przyspieszenia GiST. Połączenie go z ORDER BY ... LIMIT umożliwia niezwykle szybkie zapytanie k-najbliższych sąsiadów (KNN) — bez konieczności skanowania całej tabeli.

-- Find the 3 cities closest to a reference point (Brussels)
SELECT name,
       ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)::geography
       ) / 1000 AS distance_km
FROM   cities
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(4.3517, 50.8503), 4326)
LIMIT  3;

Sprawdzanie użycia indeksu za pomocą EXPLAIN

Zawsze należy używać EXPLAIN lub EXPLAIN ANALYZE, aby potwierdzić, że optymalizator rzeczywiście korzysta z indeksu. W danych wyjściowych należy szukać elementu Bitmap Index Scan lub Index Scan using idx_cities_geom. Jeśli zamiast tego widoczny jest element Seq Scan, tabela może być zbyt mała, aby optymalizator preferował indeks.

EXPLAIN
SELECT name
FROM   cities
WHERE  geom && ST_MakeEnvelope(-5, 40, 15, 50, 4326);

Równoczesne tworzenie indeksu

Tworzenie dużego indeksu przestrzennego za pomocą standardowego polecenia CREATE INDEX blokuje zapisy w tabeli. W środowisku produkcyjnym należy użyć CREATE INDEX CONCURRENTLY, aby utworzyć indeks bez blokowania operacji wstawiania i aktualizowania danych. Kompromis polega na tym, że operacja trwa dłużej i nie może być wykonywana wewnątrz bloku transakcji.

-- Safe for production tables (no write lock)
CREATE INDEX CONCURRENTLY idx_restaurants_geom
  ON restaurants
  USING gist (geom);

Konserwacja indeksu przestrzennego

Z czasem duża liczba operacji wstawiania, aktualizowania i usuwania danych może powodować rozrost indeksu — indeks staje się pofragmentowany i mniej wydajny. Należy użyć REINDEX, aby odbudować go w uporządkowany sposób, lub zaplanować okresowe wykonywanie VACUUM ANALYZE, aby aktualizować statystyki i umożliwić optymalizatorowi podejmowanie lepszych decyzji.

-- Rebuild the index to remove bloat
REINDEX INDEX idx_cities_geom;

-- Update planner statistics for the table
ANALYZE cities;

Szybkie sprawdzenie: indeksy GiST

Sprawdź swoją wiedzę na temat indeksów przestrzennych GiST w PostGIS.

Podsumowanie: indeksy przestrzenne GiST

W tej lekcji poznali Państwo, dlaczego indeksy przestrzenne są niezbędne dla wydajnych zapytań dotyczących lokalizacji oraz jak GiST umożliwia ich stosowanie w PostgreSQL i PostGIS.

Najważniejsze informacje:

GiST (Generalized Search Tree) to elastyczny typ indeksu obsługujący wielowymiarowe dane geometryczne.
Indeks przestrzenny należy utworzyć za pomocą CREATE INDEX ... USING gist (geom).
GiST przechowuje prostokąty ograniczające i odrzuca gałęzie drzewa wyszukiwania, unikając skanowania całej tabeli.
Operator && (nakładanie się prostokątów ograniczających) oraz operator <-> (odległość/KNN) korzystają z przyspieszenia GiST.
Należy używać EXPLAIN do sprawdzania użycia indeksu, a w środowisku produkcyjnym CREATE INDEX CONCURRENTLY, aby uniknąć blokad zapisu.
Indeksy należy konserwować za pomocą REINDEX i ANALYZE, aby zapytania pozostawały szybkie przez długi czas.

Często zadawane pytania

Czy lekcja „Indeksy przestrzenne (GiST)” jest bezpłatna?

Tak — pełny tekst „Indeksy przestrzenne (GiST)” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Indeksy przestrzenne (GiST)”?

Przyspiesz zapytania dotyczące lokalizacji Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Indeksy przestrzenne (GiST)”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do SQL Academy