Производительность запросов PostGIS Spatial Relationships

Question

У меня очень большой набор данных, содержащий более 700 миллионов точек и полигональный набор данных в качестве буферной зоны.

Моя задача - извлечь все точки внутри буферной зоны и создать новую таблицу.

Ниже мой код. Я тестирую его с небольшим набором точечных данных, и он отлично работает.

create table schema1.result as

select point.* from

schema1.site as point, schema2.buffer as poly

Where ST_Intersects(point.geo_loc,poly.wkb_geometry);

К сожалению, запрос длился 1 день и не показал никаких признаков завершения.

Есть ли какой-нибудь совет по оптимизации моего кода для ускорения запроса?

Обновление: результат Explain

«Вложенный цикл (стоимость = 0,41..17773703,88 строк = 6789472 ширина = 208)»

"-> Последовательное сканирование на буферном полигоне (стоимость = 0,00..18,50 строк = 850 ширина = 32)"

"-> Сканирование индекса с использованием idx_site в точке сайта (стоимость = 0,41..20902,23 строк = 799 ширина = 208)"

"Индекс Cond: (geo_loc && poly.wkb_geometry)"

"Фильтр: st_intersects (geo_loc, poly.wkb_geometry)"

"JIT:"

«Функции: 6»

«Параметры: вставка истина, оптимизация - истина, выражения - истина, деформирование - истина»

Answer 1

Я думаю, что здесь происходит следующее: когда вы пишете:

from schema1.site as point, schema2.buffer as poly

PostgreSQL выполняет CROSS JOINпромежуточную операцию между двумя таблицами. Когда в FROMпредложении перечислено несколько таблиц, postgres использует CROSS JOIN источник, который приводит к таблице с числом строк, равным декартовому произведению двух таблиц источника .

чтобы избежать этого, вы можете использовать WHERE EXISTSкак:

SELECT
    *
FROM schema1.site AS point_table
WHERE EXISTS(
    SELECT 1 
    FROM schema2.buffer AS poly_table
    where ST_Intersects(point_table.geom, poly_table.geom)
);

Это позволяет PostgreSQL вычислять только пространственное соединение и не создавать временную таблицу полного перекрестного соединения, которое занимает вечность.

Answer 2

Возможно, будет быстрее использовать полигоны в качестве управляющей таблицы, используя индекс таблицы точек для фильтрации большого количества записей. Это позволяет PostGIS оптимизировать ST_Intersectsпространственный предикат путем подготовки каждого многоугольника.

Это можно сделать принудительно, используя LATERAL:

SELECT pt.* FROM schema2.buffer AS poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.wkb_geometry, pt.geo_loc);

Если многоугольники имеют большое количество вершин, их можно быстрее ST_Subdivideфрагментировать перед выполнением точечного запроса:

WITH poly AS (
  SELECT ST_Subdivide(wkb_geometry) AS geom FROM schema2.buffer 
)
SELECT pt.* FROM poly
JOIN LATERAL (SELECT * FROM schema1.site) AS pt 
  ON ST_Intersects(poly.geom, pt.geo_loc);