Блог

Главная - Вопросы по программированию - Оптимизация большого запроса PostGIS

Оптимизация большого запроса PostGIS

#postgresql #postgis

#postgresql #postgis

Вопрос:

В настоящее время у меня есть большая таблица mivehdetailedtrajectory (25B строк) и маленькая таблица cell_data_tower (400 строк), к которым мне нужно присоединиться с помощью PostGIS. В частности, мне нужно выполнить этот запрос:

 SELECT COUNT(traj.*), tower.id
FROM cell_data_tower tower LEFT OUTER JOIN mivehdetailedtrajectory traj
ON ST_Contains(tower.geom, traj.location)
GROUP BY tower.id
ORDER BY tower.id;

Выдает ошибку из-за того, что не может выполнить запись на диск. Это показалось странным для ВЫБОРА, поэтому я побежал ОБЪЯСНЯТЬ:
ОБРАТИТЕ ВНИМАНИЕ: gserialized_gist_joinsel: тип соединения 1 не поддерживается

                                                      QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Sort  (cost=28905094882.25..28905094883.25 rows=400 width=120)
   Sort Key: tower.id
   ->  HashAggregate  (cost=28905094860.96..28905094864.96 rows=400 width=120)
         ->  Nested Loop Left Join  (cost=0.00..28904927894.80 rows=33393232 width=120)
               Join Filter: ((tower.geom amp;amp; traj.location) AND _st_contains(tower.geom, traj.location))
               ->  Seq Scan on cell_data_tower tower  (cost=0.00..52.00 rows=400 width=153)
               ->  Materialize  (cost=0.00..15839886.96 rows=250449264 width=164)
                     ->  Seq Scan on mivehdetailedtrajectory traj  (cost=0.00..8717735.64 rows=250449264 width=164)

Я не понимаю, почему postgres считает, что он должен материализовать внутреннюю таблицу. Кроме того, я не понимаю план в целом, если честно. Похоже, что он должен сохранять таблицу cell_data_tower в памяти и выполнять итерации по таблице mivehdetailedtractory. Любые мысли о том, как я могу оптимизировать это, чтобы (а) выполнить, (б) сделать это за разумное время. В частности, кажется, что это должно быть выполнимо менее чем за 1 день.

Редактировать: Postgres версии 9.3

Комментарии:

1. Пожалуйста, добавьте версию PostgreSQL.

2. Возможно, глупый вопрос, но у вас есть пространственный индекс в mivehdetailedtrajectory? В такого рода запросах вам на самом деле не нужно писать left outer join, поскольку, по сути, вы все равно выполняете полное объединение, но ограничиваете результаты с помощью «пространственного» объединения. Если вы замените левое внешнее соединение запятой, вы можете получить другой план.

3. У меня были похожие проблемы с нехваткой дискового пространства с помощью хэш-соединений. Вы можете попробовать установить enable_hashagg=off и / или enable_hashjoin=off. Может показаться, что это ухудшает план запроса (так и есть), но он переключится на использование соединения с вложенным циклом, которое, хотя и медленнее, не будет занимать дисковое пространство. В конечном счете, если у вас нет пространственного индекса, то вы все равно выполняете двойной цикл и просто проверяете ST_Contains каждый раз. Я взял на себя смелость добавить tower.id на ваш выбор, как указал Якуб.

4. У меня действительно есть пространственный индекс в mivehdetailedtrajectory. Проблема, похоже, в строках 25B, ничего разумного. Возможно, мне нужно начать искать что-то, более подходящее для масштаба данных, с которыми я работаю.

5. @SteveY. Да, 25 миллиардов — это довольно много для запроса Postgres. Являются ли ваши геометрии cell_data_tower по существу кругами? Потому что, если это так, было бы легко переписать contains как простое вычисление евклидова расстояния, что сделало бы это очень удобным для map-reduce, после чего вы могли бы начать думать о Hadoop (или чем-то подобном).

Ответ №1:

Запросы, требующие большого объема памяти, — это те редкие места, где коррелированные подзапросы выполняются лучше ( LATERAL JOIN тоже должны работать, но это выше моего понимания). Также обратите внимание, что вы не выбрали tower.id таким образом, ваш результат был бы не слишком полезен.

 SELECT tower.id, (SELECT COUNT(traj.*) 
                  FROM mivehdetailedtrajectory traj
                  WHERE ST_Contains(tower.geom, traj.location))
FROM cell_data_tower tower
ORDER BY tower.id;

Попробуйте сначала запустить его с LIMIT 1 . Общее время выполнения должно соответствовать времени выполнения для одной башни * количество башен.

Ответ №2:

У меня нет такой большой базы данных, как у вас, всего 80M. Но в моем случае я создаю поле LinkId, чтобы знать, где находится каждый геометрический объект, и вычисляю, какой из них является ближайшим LinkId при вставке новой записи.

Когда я обнаружил, что один LinkId занимает 30 мс, а выполнение этого 80 миллионов раз займет 27 дней, я перешел от предварительного вычисления этих значений.

Также я не храню все записи, я храню только месяц в любое время.