Intereting Posts
Как использовать необработанный стандартный sql в ASP.NET MVC без linq? Вычисление размера столбца в Postgresql это столбец идентификаторов, действительно необходимый в SQL? Oracle select query дает приоритет значению столбца Уточнение пакета Java / SQLite и автоматическая фиксация MAX (id) с использованием SqlDataReader C # моделирование деревьев или графиков SQL: как отделить свойства информации о узлах и узлах Выберите имена столбцов, соответствующие критериям (MySQL) нахождение последней последовательной строки SQL: выберите "до" Запуск скрипта для создания таблиц с помощью HSQLDB Чрезвычайно медленный запрос PostgreSQL с предложениями ORDER и LIMIT Не получать список часов из Fromdate to Todate с помощью SQL-сервера SQL: один внешний ключ ссылается на первичный ключ в одной из нескольких таблиц Запрос PostgreSQL для удаления записей с перекрывающимися моментами при сохранении самого раннего?

Пространственный запрос на большой таблице с несколькими сеансами самообслуживания, выполняющими медленные

Я работаю над запросами на большой таблице в Postgres 9.3.9. Это пространственный набор данных и он пространственно проиндексирован. Скажем, мне нужно найти 3 типа объектов: A, B и C. Критерии того, что B и C находятся на определенном расстоянии от A, скажем, 500 метров.

Мой запрос выглядит так:

select school.osm_id as school_osm_id, school.name as school_name, school.way as school_way, restaurant.osm_id as restaurant_osm_id, restaurant.name as restaurant_name, restaurant.way as restaurant_way, bar.osm_id as bar_osm_id, bar.name as bar_name, bar.way as bar_way from ( select osm_id, name, amenity, way, way_geo from planet_osm_point where amenity = 'school') as school, (select osm_id, name, amenity, way, way_geo from planet_osm_point where amenity = 'restaurant') as restaurant, (select osm_id, name, amenity, way, way_geo from planet_osm_point where amenity = 'bar') as bar where ST_DWithin(school.way_geo, restaurant.way_geo, 500, false) and ST_DWithin(school.way_geo, bar.way_geo, 500, false); 

Этот запрос дает мне то, что я хочу, но это занимает очень много времени, например, 13 секунд для выполнения. Мне интересно, есть ли другой способ написать запрос и сделать его более эффективным.

План запроса:

 Nested Loop (cost=74.43..28618.65 rows=1 width=177) (actual time=33.513..11235.212 rows=10591 loops=1) Buffers: shared hit=530967 read=8733 -> Nested Loop (cost=46.52..28586.46 rows=1 width=174) (actual time=31.998..9595.212 rows=4235 loops=1) Buffers: shared hit=389863 read=8707 -> Bitmap Heap Scan on planet_osm_point (cost=18.61..2897.83 rows=798 width=115) (actual time=7.862..150.607 rows=8811 loops=1) Recheck Cond: (amenity = 'school'::text) Buffers: shared hit=859 read=5204 -> Bitmap Index Scan on idx_planet_osm_point_amenity (cost=0.00..18.41 rows=798 width=0) (actual time=5.416..5.416 rows=8811 loops=1) Index Cond: (amenity = 'school'::text) Buffers: shared hit=3 read=24 -> Bitmap Heap Scan on planet_osm_point planet_osm_point_1 (cost=27.91..32.18 rows=1 width=115) (actual time=1.064..1.069 rows=0 loops=8811) Recheck Cond: ((way_geo && _st_expand(planet_osm_point.way_geo, 500::double precision)) AND (amenity = 'restaurant'::text)) Filter: ((planet_osm_point.way_geo && _st_expand(way_geo, 500::double precision)) AND _st_dwithin(planet_osm_point.way_geo, way_geo, 500::double precision, false)) Rows Removed by Filter: 0 Buffers: shared hit=389004 read=3503 -> BitmapAnd (cost=27.91..27.91 rows=1 width=0) (actual time=1.058..1.058 rows=0 loops=8811) Buffers: shared hit=384528 read=2841 -> Bitmap Index Scan on idx_planet_osm_point_waygeo (cost=0.00..9.05 rows=137 width=0) (actual time=0.193..0.193 rows=64 loops=8811) Index Cond: (way_geo && _st_expand(planet_osm_point.way_geo, 500::double precision)) Buffers: shared hit=146631 read=2841 -> Bitmap Index Scan on idx_planet_osm_point_amenity (cost=0.00..18.41 rows=798 width=0) (actual time=0.843..0.843 rows=6291 loops=8811) Index Cond: (amenity = 'restaurant'::text) Buffers: shared hit=237897 -> Bitmap Heap Scan on planet_osm_point planet_osm_point_2 (cost=27.91..32.18 rows=1 width=115) (actual time=0.375..0.383 rows=3 loops=4235) Recheck Cond: ((way_geo && _st_expand(planet_osm_point.way_geo, 500::double precision)) AND (amenity = 'bar'::text)) Filter: ((planet_osm_point.way_geo && _st_expand(way_geo, 500::double precision)) AND _st_dwithin(planet_osm_point.way_geo, way_geo, 500::double precision, false)) Rows Removed by Filter: 1 Buffers: shared hit=141104 read=26 -> BitmapAnd (cost=27.91..27.91 rows=1 width=0) (actual time=0.368..0.368 rows=0 loops=4235) Buffers: shared hit=127019 -> Bitmap Index Scan on idx_planet_osm_point_waygeo (cost=0.00..9.05 rows=137 width=0) (actual time=0.252..0.252 rows=363 loops=4235) Index Cond: (way_geo && _st_expand(planet_osm_point.way_geo, 500::double precision)) Buffers: shared hit=101609 -> Bitmap Index Scan on idx_planet_osm_point_amenity (cost=0.00..18.41 rows=798 width=0) (actual time=0.104..0.104 rows=779 loops=4235) Index Cond: (amenity = 'bar'::text) Buffers: shared hit=25410 Total runtime: 11238.605 ms 

Сейчас я использую только одну таблицу с 1 372 711 строк . Он имеет 73 столбца :

  Column | Type | Modifiers --------------------+----------------------+--------------------------- osm_id | bigint | access | text | addr:housename | text | addr:housenumber | text | addr:interpolation | text | admin_level | text | aerialway | text | aeroway | text | amenity | text | area | text | barrier | text | bicycle | text | brand | text | bridge | text | boundary | text | building | text | capital | text | construction | text | covered | text | culvert | text | cutting | text | denomination | text | disused | text | ele | text | embankment | text | foot | text | generator:source | text | harbour | text | highway | text | historic | text | horse | text | intermittent | text | junction | text | landuse | text | layer | text | leisure | text | lock | text | man_made | text | military | text | motorcar | text | name | text | natural | text | office | text | oneway | text | operator | text | place | text | poi | text | population | text | power | text | power_source | text | public_transport | text | railway | text | ref | text | religion | text | route | text | service | text | shop | text | sport | text | surface | text | toll | text | tourism | text | tower:type | text | tunnel | text | water | text | waterway | text | wetland | text | width | text | wood | text | z_order | integer | tags | hstore | way | geometry(Point,4326) | way_geo | geography | gid | integer | not null default nextval('... Indexes: "planet_osm_point_pkey1" PRIMARY KEY, btree (gid) "idx_planet_osm_point_amenity" btree (amenity) "idx_planet_osm_point_waygeo" gist (way_geo) "planet_osm_point_index" gist (way) "planet_osm_point_pkey" btree (osm_id) 

Есть 8811, 6291, 779 рядов в школе, в ресторане и баре соответственно.

Этот запрос должен пройти долгий путь (быть намного быстрее):

 WITH school AS ( SELECT s.osm_id AS school_id, text 'school' AS type, s.osm_id, s.name, s.way_geo FROM planet_osm_point s , LATERAL ( SELECT 1 FROM planet_osm_point WHERE ST_DWithin(way_geo, s.way_geo, 500, false) AND amenity = 'bar' LIMIT 1 -- bar exists -- most selective first if possible ) b , LATERAL ( SELECT 1 FROM planet_osm_point WHERE ST_DWithin(way_geo, s.way_geo, 500, false) AND amenity = 'restaurant' LIMIT 1 -- restaurant exists ) r WHERE s.amenity = 'school' ) SELECT * FROM ( TABLE school -- schools UNION ALL -- bars SELECT s.school_id, 'bar', x.* FROM school s , LATERAL ( SELECT osm_id, name, way_geo FROM planet_osm_point WHERE ST_DWithin(way_geo, s.way_geo, 500, false) AND amenity = 'bar' ) x UNION ALL -- restaurants SELECT s.school_id, 'rest.', x.* FROM school s , LATERAL ( SELECT osm_id, name, way_geo FROM planet_osm_point WHERE ST_DWithin(way_geo, s.way_geo, 500, false) AND amenity = 'restaurant' ) x ) sub ORDER BY school_id, (type <> 'school'), type, osm_id; 

Это не то же самое, что и исходный запрос, а скорее то, что вы на самом деле хотите, в соответствии с обсуждением в комментариях :

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

Таким образом, этот запрос возвращает список этих школ, за которыми следуют бары и рестораны поблизости. Каждый набор строк удерживается вместе osm_id школы в столбце school_id .

Теперь используем LATERAL соединения, чтобы использовать пространственный индекс GiST.

TABLE school – это только сокращение для SELECT * FROM school :

  • Есть ли ярлык для SELECT * FROM в psql?

Выражение (type <> 'school') сначала заказывает школу в каждом наборе, потому что:

  • SQL выбирает порядок запросов по дням и месяцам

Подзапрос sub в финальном SELECT нужен только для этого выражения. Запрос UNION ограничивает прикрепленный список ORDER BY только столбцами, а не выражениями.

Я сосредотачиваюсь на запросе, который вы представили для целей этого ответа, – игнорируя расширенное требование фильтровать любой из других 70 текстовых столбцов. Это действительно ошибка дизайна. Критерии поиска должны быть сосредоточены в нескольких столбцах. Или вам придется индексировать все 70 столбцов, и многоколоночные индексы, как я собираюсь предложить, вряд ли являются вариантом. Все еще возможно, хотя …

Индекс

В дополнение к существующим:

 "idx_planet_osm_point_waygeo" gist (way_geo) 

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

 CREATE INDEX planet_osm_point_bar_idx ON planet_osm_point (amenity, name, osm_id) 

Postgres 9.5

Предстоящие Postgres 9.5 вносят существенные улучшения, которые, как правило, направлены на ваше дело:

  • Разрешить запросы для точной фильтрации расстояний объектов с индексом (полигоны, круги) с использованием индексов GiST (Александр Коротков, Хейкки Линнакангас)

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

  • Разрешить индексы GiST выполнять сканирование только по индексу (Анастасия Лубенникова, Хейкки Линнакангас, Андреас Карлссон)

Это особенно интересно для вас. Теперь у вас может быть один многоколоночный (охватывающий) индекс GiST:

 CREATE INDEX reservations_range_idx ON reservations USING gist(amenity, way_geo, name, osm_id) 

А также:

  • Улучшение производительности сканирования растрового изображения (Теодор Сигаев, Том Лейн)

А также:

  • Добавить функции GROUP BY для анализа GROUPING SETS , CUBE и ROLLUP (Andrew Gierth, Atri Sharma)

Зачем? Поскольку ROLLUP упростит запрос, который я предложил. Связанный ответ:

  • Соответствие Grouping () в PostgreSQL?

Первая альфа-версия была выпущена 2 июля 2015 года . Ожидаемый график релиза:

Это альфа-версия версии 9.5, указывающая на то, что некоторые изменения в функциях все еще возможны до выпуска. Проект PostgreSQL выпустит 9,5 бета-версии 1 в августе, а затем периодически выпускает дополнительные бета-версии, необходимые для тестирования до финальной версии в конце 2015 года.

основы

Конечно, не забывайте об основных принципах:

  • Страница вопросов медленных запросов в Wiki PostgreSQL

3 подвыбора, которые вы используете, очень неэффективны. Напишите их как предложения LEFT JOIN и запрос должен быть намного более эффективным:

 SELECT school.osm_id AS school_osm_id, school.name AS school_name, school.way AS school_way, restaurant.osm_id AS restaurant_osm_id, restaurant.name AS restaurant_name, restaurant.way AS restaurant_way, bar.osm_id AS bar_osm_id, bar.name AS bar_name, bar.way AS bar_way FROM planet_osm_point school LEFT JOIN planet_osm_point restaurant ON restaurant.amenity = 'restaurant' AND ST_DWithin(school.way_geo, restaurant.way_geo, 500, false) LEFT JOIN planet_osm_point bar ON bar.amenity = 'bar' AND ST_DWithin(school.way_geo, bar.way_geo, 500, false) WHERE school.amenity = 'school' AND (restaurant.osm_id IS NOT NULL OR bar.osm_id IS NOT NULL); 

Но это даст слишком много результатов, если в школе есть несколько ресторанов и баров. Вы можете упростить запрос следующим образом:

 SELECT school.osm_id AS school_osm_id, school.name AS school_name, school.way AS school_way, a.osm_id AS amenity_osm_id, a.amenity AS amenity_type, a.name AS amenity_name, a.way AS amenity_way, FROM planet_osm_point school JOIN planet_osm_point a ON ST_DWithin(school.way_geo, a.way_geo, 500, false) WHERE school.amenity = 'school' AND a.amenity IN ('bar', 'restaurant'); 

Это даст каждому бару и ресторану для каждой школы. Школы без ресторана или бара в пределах 500 м не указаны.

Не имеет значения, используете ли вы явные объединения?

 SELECT a.id as a_id, a.name as a_name, a.geog as a_geog, b.id as b_id, b.name as b_name, b.geog as b_geog, c.id as c_id, c.name as c_name, c.geog as c_geog FROM table1 a JOIN table1 b ON b.type = 'B' AND ST_DWithin(a.geog, b.geog, 100) JOIN table1 c ON c.type = 'C' AND ST_DWithin(a.geog, c.geog, 100) WHERE a.type = 'A'; 

Попробуйте это с помощью синтаксиса внутреннего соединения и сравните результаты, не должно быть дубликатов. Я предполагаю, что это займет 1/3 раза или лучше исходного запроса:

 select a.id as a_id, a.name as a_name, a.geog as a_geo, b.id as b_id, b.name as b_name, b.geog as b_geo, c.id as c_id, c.name as c_name, c.geog as c_geo from table1 as a INNER JOIN table1 as b on b.type='B' INNER JOIN table1 as c on c.type='C' WHERE a.type='A' and (ST_DWithin(a.geo, b.geo, 100) and ST_DWithin(a.geo, c.geo, 100))