Ogranicz rzędy poprzez funkcję przestrzenną

Próbuję poprawić wydajność poniższego zapytania. Bez względu na to, jak piszę zapytanie (podzapytanie w klauzuli FROM, podzapytanie w klauzuli WHERE) postgres nalega na uruchomienie wszystkich ~ 570K wierszy przez kosztowną funkcję ST_DWITHIN, nawet jeśli jest tylko 60 wierszy, gdzie county = 24. Jak mogę filtrować postgres według hrabstwa = 24 PRZED uruchomieniem funcji postgis, która wydaje mi się znacznie szybsza i znacznie wydajniejsza? 700ms nie jest powodem do zbytniego niepokoju, ale kiedy ta tabela rośnie do 10M +, martwię się o wydajność.

Należy również zauważyć, p.id jest kluczem podstawowym, p.zipcode jest indeksem fk, z.county jest indeksem fk, a p.geom ma indeks GiST.

Pytanie:

EXPLAIN ANALYZE
  SELECT count(p.id)
  FROM point AS p
  LEFT JOIN zipcode AS z
    ON p.zipcode = z.zipcode
  WHERE z.county = 24
    AND ST_DWithin(
      p.geom, 
      ST_SetSRID(ST_Point(-121.479756008715,38.563236291512),4269), 
      16090.0,
      false
    )

OBJAŚNIJ ANALIZĘ:

Aggregate  (cost=250851.91..250851.92 rows=1 width=4) (actual time=724.007..724.007 rows=1 loops=1)
  ->  Hash Join  (cost=152.05..250851.34 rows=228 width=4) (actual time=0.359..723.996 rows=51 loops=1)
        Hash Cond: ((p.zipcode)::text = (z.zipcode)::text)
        ->  Seq Scan on point p  (cost=0.00..250669.12 rows=7437 width=10) (actual time=0.258..723.867 rows=63 loops=1)
              Filter: (((geom)::geography && '0101000020AD10000063DF8B52B45E5EC070FB752018484340'::geography) AND ('0101000020AD10000063DF8B52B45E5EC070FB752018484340'::geography && _st_expand((geom)::geography, 16090::double precision)) AND _st_dwithin((g (...)
              Rows Removed by Filter: 557731
        ->  Hash  (cost=151.38..151.38 rows=54 width=6) (actual time=0.095..0.095 rows=54 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 3kB
              ->  Bitmap Heap Scan on zipcode z  (cost=4.70..151.38 rows=54 width=6) (actual time=0.023..0.079 rows=54 loops=1)
                    Recheck Cond: (county = 24)
                    Heap Blocks: exact=39
                    ->  Bitmap Index Scan on fki_zipcode_county_foreign_key  (cost=0.00..4.68 rows=54 width=0) (actual time=0.016..0.016 rows=54 loops=1)
                          Index Cond: (county = 24)
Planning time: 0.504 ms
Execution time: 724.064 ms

Możesz zobaczyć problem z oczekiwaną a rzeczywistą liczbą wierszy. Planista uważa, że ​​istnieje 7437 wierszy, jednak jest ich tylko 63. Statystyki są wyłączone. Co ciekawe, nie korzysta z wyszukiwania indeksu (indeksu) obwiedni, z którym DWithinmożna wkleić wynik \d point. Jaka wersja PostGIS i PostgreSQL?

Spróbuj uruchomić ANALYZE point. Czy otrzymujesz ten sam plan, gdy podnosisz stan w górę?

JOIN zipcode AS z
  ON p.zipcode = z.zipcode
  AND z.county = 24

Na marginesie, istnieje uzasadniona szansa, że ​​to zachowanie zostanie zmodyfikowane w PostGIS 2.3.0, jeśli chcesz nazwać to błędem.

• Problem z Githubem
• Problem z Trac

Z dokumentacji na PostgreSQL

Liczba dodatnia podająca szacowany koszt wykonania funkcji, w jednostkach cpu_operator_cost. Jeśli funkcja zwraca zestaw, jest to koszt za zwrócony wiersz. Jeśli koszt nie zostanie określony, zakłada się 1 jednostkę dla języka C i funkcji wewnętrznych oraz 100 jednostek dla funkcji we wszystkich innych językach. Większe wartości powodują, że planista stara się unikać oceny funkcji częściej niż to konieczne.

Tak więc domyślny koszt wynosił 1 (bardzo tanio). D_Withinkorzystanie z indeksu GIST jest bardzo tanie. Ale to zostało zwiększone do 100 (przez proxy wewnętrzne _ST_DWithin).

Sam nie jestem wielkim fanem metody CTE. CTE to płot optymalizacyjny. Tak więc robienie tego w taki sposób eliminuje potencjalną przestrzeń do przyszłej optymalizacji. Jeśli saner defaults to naprawi, wolę uaktualnić. Pod koniec dnia musimy wykonać zadanie i ta metoda najwyraźniej działa dla Ciebie.

Dzięki podpowiedzi Johna Powella poprawiłem zapytanie, aby umieścić warunek ograniczenia hrabstwa w zapytaniu z / CTE, a to poprawiło wydajność nieco do 222 ms w porównaniu z 700. Nadal daleko mi do 0,74 ms, kiedy otrzymuję dane własny stół. Nadal nie jestem pewien, dlaczego planista nie ogranicza zbioru danych przed uruchomieniem drogiej funkcji Postgis, i będę musiał spróbować z większymi zestawami danych, gdy je mam, ale wydaje się, że na razie jest to rozwiązanie tej wyjątkowej sytuacji.

with points as (
   select p.id, p.geom from point p inner join zipcode z
   on p.zipcode = z.zipcode
   where county = 24
   ) 


SELECT count(points.id)
FROM points
WHERE ST_DWITHIN(points.geom, (ST_SetSRID(ST_Point(-121.479756008715,38.563236291512),4269)), 16090.0, false)

Powinieneś utworzyć indeks na zipcode(county, zipcode), który powinien dać indeks tylko skanować na z.

Można też poeksperymentować z btree_gistrozszerzeniem tworząc zarówno point(zipcode, geom)indeks lub point(geom, zipcode)i zipcode(zipcode, county)indeks.