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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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;

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