Przyspieszenie zapytań OpenStreetMap PostGIS

12

Mam dane OpenStreetMap dla Holandii załadowane do bazy danych PostGIS (PostgreSQL 8.3 / PostGIS 1.3.3) przy użyciu schematu osmozy . Oznacza to, że wszystkie tagi są przechowywane w polu hstore . Oprócz indeksu GIST, który osmoza tworzy na polu geometrii, utworzyłem dodatkowy indeks GIST na polu tagów.

Próbując wykonać zapytanie przy użyciu zarówno ograniczenia przestrzennego, jak i ograniczenia w polu znaczników, stwierdzam, że jest on wolniejszy niż chciałbym. Zapytanie takie jak to:

SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n 
  INNER JOIN users AS u ON n.user_id = u.id 
  WHERE tags->'man_made'='surveillance' 
  AND ST_Within(geom, ST_GeomFromText('POLYGON((4.0 52.0,5.0 52.0,5.0 53.0,4.0 53.0,4.0 52.0))',4326));

Zwrócenie 78 rekordów zajmuje 22 sekundy.

W tej tabeli znajduje się około 53 milionów rekordów.

Czy istnieje sposób, aby znacznie to przyspieszyć? Słyszałem, że hstore jest znacznie lepiej zaimplementowany w PostgreSQL 9, czy aktualizacja pomogłaby?

— mvexel
źródło

Ponieważ wydaje się, że jest to pytanie zorientowane na bazę danych, zachęcam do zadawania pytań na stronie dba.stackexchange.com

— jcolebrand

Aktualizacja na 2015 r. - PostGIS wprowadził znaczną poprawę wydajności od czasu zadania tego pytania, więc weź to pod uwagę, podobnie jak aktualizacja PostgreSQL.

— Toby Speight

5

Jedną z metod byłoby zapytanie o interesujące cię tagi i umieszczenie tych rekordów w nowej tabeli. W takim razie wystarczy zapytać nową tabelę zamiast wszystkich 53 milionów rekordów. Jeśli próbujesz aktualizować bazę danych, możesz uruchomić to zapytanie za każdym razem, gdy otrzymujesz nowe dane z OSM.

— jvangeld
źródło

2

Zamiast tworzenia nowej tabeli można rozważyć utworzenie WIDOKU, w ten sposób „zapytanie” jest na żywo powiązane z oryginalnymi danymi źródłowymi bez dosłownego powielania danych.

— RyanKDalton

7

Widok niekoniecznie poprawi wydajność zapytania, chyba że jest to widok zmaterializowany lub równoważny (zobacz pytanie SO na ten temat). Nie sądzę, aby Postgresql bezpośrednio wspiera zmaterializowane widoki , ale można je zaimplementować za pomocą wyzwalaczy.

— Adam Armor

2

Jest to obejście, którego obecnie używam. Po aktualizacji tabel osmozy ponownie tworzę kilka tabel zoptymalizowanych pod kątem zapytań, które chcę uruchomić. Po prostu czuję, że musi być lepszy sposób. Intryguje mnie temat wyzwalaczy i to, jak można ich użyć do implementacji widoków materiałów. @Adam Armor, czy jest szansa, że podzielisz się tym spostrzeżeniem?

— mvexel

4

@mvexel Zapoznaj się z tym artykułem wiki , który obejmuje podstawy zmaterializowanych widoków i szczegółowe informacje na temat ich implementacji w PostgreSQL.

— Adam Armor

5

Możesz spróbować utworzyć indeks dla kolumny hstore,

CREATE INDEX nodes_tags_idx ON nodes USING GIST(tags)

a następnie użyj ?operatora, aby ograniczyć zapytanie tylko do tych wierszy:

SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n 
  INNER JOIN users AS u ON n.user_id = u.id 
  WHERE tags ? 'man_made'
  AND tags->'man_made'='surveillance' 
  AND ST_Within(geom, ST_GeomFromText('POLYGON((4.0 52.0,5.0 52.0,5.0 53.0,4.0 53.0,4.0 52.0))',4326));

— olt
źródło

Dzięki! Już utworzyłem ten indeks, tylko że go nie używałem. Przyspiesza tylko niektóre operacje. W PostgreSQL 8.3 (którego używam) jest to tylko @> i? , w wersji 9.0 to @>,?,? i? | .

— mvexel

1

Dla przypomnienia, zapytanie przy użyciu ?operatora zajęło 48 sekund w porównaniu do 88 sekund na moje zapytanie (nie wiem, jak wczoraj otrzymałem 72 sekundy, być może maszyna tym razem robiła coś skomplikowanego podczas wykonywania zapytań). Wciąż nie szukam wydajności, ale lepiej zrozumiałem, w jaki sposób indeksy GIST działają na kolumnach hstore. Nadal będę musiał wybrać inne rozwiązanie polegające na stworzeniu zmaterializowanego widoku, aby uzyskać żądaną wydajność.

— mvexel

3

Funkcje st_within i _st_within nie są znane ze swojej szybkości. Operator && może pomóc, ponieważ sprawdzi bbox zamiast geometrii

Możesz spróbować:

SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n 
  INNER JOIN users AS u ON n.user_id = u.id 
  WHERE tags ? 'man_made'
  AND tags->'man_made'='surveillance' 
  AND geom && ST_SetSRID('BOX3D(4 52,5 53)'::box3d,4326);

Więcej wskazówek dotyczących wydajności można znaleźć na stronie: http://postgis.refractions.net/docs/ch06.html

— milovanderlinden
źródło

2

Problem z zapytaniem stanowi tags->'man_made'='surveillance'klauzula. Zmusza to Postgres do rozszerzenia tagów hstore i nie pozwala na korzystanie z indeksu. Jeśli przepiszesz to za pomocą @>(zawiera), pozwoli to na użycie indeksu.

Ponieważ &&pytasz o prostokąt, możesz użyć zamiast ST_Within. Będzie to miało niewielki zysk, ponieważ ST_Within nie jest tak skomplikowany do oceny, a ST_Within niejawnie &&sprawdza.

Dodatkowym wzrostem prędkości byłoby użycie indeksu GIN na tagach zamiast indeksu GIST. Indeksy GIN trwają dłużej, ale są szybsze.

Całe zapytanie byłoby

SELECT n.geom,n.tags,n.tstamp,u.name FROM nodes AS n INNER JOIN users AS u ON n.user_id = u.id WHERE tags @> hstore('man_made', 'surveillance') AND geom && ST_GeomFromText('POLYGON((4.0 52.0,5.0 52.0,5.0 53.0,4.0 53.0,4.0 52.0))',4326);

Jeśli wiesz, że często wysyłasz zapytanie do określonego znacznika, możesz utworzyć na nim częściowy indeks CREATE INDEX ON nodes ( tags->'man_made' ) WHERE (tags->'man_made' IS NOT NULL);.

Umożliwi to warunek WHERE tags->'man_made'='surveillance'użycie indeksu. Niestety, ten indeks nie może pomóc @>zapytaniom, a indeksy GIN lub GIST nie mogą pomóc tags->'foo'zapytaniom, więc musisz dopasować zapytania do posiadanych indeksów.

— Paul Norman
źródło

Dzięki radzie, tags @>hstore()znacznie poprawiłem moje zapytanie, dzięki.

— alphabetasoup

1

spróbuj tego zamiast tego:

WYBIERZ n.geom, n.tagi, n.tampa, u.nazwa z węzłów AS n INNER JOIN users AS u ON n.user_id = u.id GDZIE tagi @> 'man_made => monitoring' :: hstore AND ST_Within (geom , ST_GeomFromText („POLYGON ((4,0 52,0,5,0 52,0,5,0 53,0,0,0 53,0,0,0 52,0))”, 4326));

— LR1234567
źródło