Как ускорить запрос выборки геоданных в PostgreSQL?

Question

[furyon]

К примеру есть таблица адресов компаний из 3-х полей: company_id, lat, lon.
Необходимо получить расстояния до ближайшего адреса каждой из компаний.

Для работы с геоданными использую расширения cube и earthdistance.
Выполняю запрос:

SELECT
 company_id,
 MIN(earth_distance(ll_to_earth(lat,lon), ll_to_earth(53.96,83.96))) AS distance
FROM companies
GROUP BY company_id;

Запрос делает что надо, но медленно. GIST индекс типа

CREATE INDEX i_name on companies USING gist(ll_to_earth(lat, lon));

не помогает.

Есть ли способы ускорения такого запроса?

PS
Задал вопрос на SO https://stackoverflow.com/questions/48146004/how-t...
Ответ отмеченный как решение позволил увеличить скорость скрипта в 3 раза.

Answer 1

[Fixid]

Используйте PostGIS.

Comments

[furyon]

А как там это делается?

[Fixid]

furyon, gis-lab.info/qa/postgis-work.html
У меня поиск 10 ближайших среди 100 миллионов записей занимает 2мс с пространственным индексом. При желании можно ускорить

[furyon]

Ближайшие записи к точке я могу по индексу искать, это у меня делается быстро относительно, а вот как быстро агрегировать и выбрать минимальное расстояние не знаю.

[furyon]

Удалось найти только агрегацию гео-объектов в PostGIS. Как ее применить для моего случая непонятно.

[Melkij]

Ближайшие записи к точке я могу по индексу искать, это у меня делается быстро относительно, а вот как быстро агрегировать и выбрать минимальное расстояние не знаю.

А в чём разница между найти ближайшего и найти минимальное расстояние?
Берёте список уникальных company_id (можно по loose index scan, если уникальных company_id нет в отдельной табличке, но уникальных их не слишком много относительно всей таблицы), затем делаете к этому списку lateral join коррелирующим подзапросом поиска ближайшего соседа с limit 1

[furyon]

Melkij, разница в том, что в одном случае надо просто искать ближайшие к точке, а в другом случае надо найти ближайшие для каждого из company_id.

[freeExec]

SELECT company_id
FROM companies
ORDER BY company_geom <-> ST_POINT(53.96,83.96) ASC
LIMIT 1;

[furyon]

freeExec, мне нужно для каждого из company_id в таблице получить расстояние. Если я правильно понимаю, то этот запрос даст мне просто ближайшую запись.

[freeExec]

furyon, Сделайте его подзапросом для каждой вашей компании.

[furyon]

Не думаю что это будет быстрее чем мой вариант, и тем более чем вариант предложенный на SO (обновил тему).

[Fixid]

furyon, за счет postgis будет реально быстрее

[furyon]

Fixid, на чем основана данная сентенция? Суть ответа из SO в том чтобы заранее высчитывать результат функции ll_to_eath, как следствие функция earth_distance быстрее вернет результат. Никаким манипулированием данных при запросе заниматься не приходится, почему PostGIS должен быть быстрее я не понимаю.

[Melkij]

разница в том, что в одном случае надо просто искать ближайшие к точке, а в другом случае надо найти ближайшие для каждого из company_id

См. сразу же упомянутый мной lateral join. Если вы можете быстро найти ближайшего соседа для заданного company_id - вы совершенно так же можете быстро найти соседей для списка company_id.

почему PostGIS должен быть быстрее я не понимаю.

Потому что KNN по gist индексу отлично считается за один index scan (+ gist_btree для включения company_id в индекс). И да, кучка разных index scan может быть сильно быстрее одного fullscan. См. упомянутый мной же сразу loose index scan - просто потому, что оптимизатор postgres не умеет его делать автоматически.

[Melkij]

Разумеется сообщение выше актуально, если count(*) сильно больше count(distinct company_id). Если много разных company_id и читать надо все company_id - то группировка всего будет эффективнее и выгоднее экстенсивно поднимать set local work_mem в этой транзакции (глобально не надо).

[furyon]

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

[furyon]

Попробовал PostGIS способом из 2-го ответа на SO, execution time из explain analyze примерно такой же.

[Fixid]

furyon, индексы добавили?

[furyon]

В общем делаю так:

ALTER TABLE companies ADD COLUMN geog geography(POINT 4326);
UPDATE companies SET geog = ST_MakePoint(lon,lat)::geogprahy;
CREATE INDEX idx_name ON companies USING GIST (company_id, geog);
EXPLAIN ANALYSE SELECT DISTINCT ON (company_id)
  company_id
  , ST_Distance(geog, CAST(ST_Point(83.96, 53.96) AS GEOGRAPHY)) as distance
FROM companies
ORDER BY company_id, distance DESC;

Что я делаю не так?