Можно ли ускорить этот оператор postgress/postgis?

У меня есть база данных Postgres с установленным расширением postgis, и она заполняется данными открытой карты улиц.

Со следующим оператором SQL:

SELECT                                                                
    l.osm_id,
    sum(
        st_area(st_intersection(ST_Buffer(l.way, 30), p.way))
        /
        st_area(ST_Buffer(l.way, 30))
    ) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, ST_Buffer(p.way,30))
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id;

Я рассчитываю «зеленый» балл.

Моя цель — создать «зеленую» оценку для каждого osm_id.

Что значит; какая часть дороги проходит рядом с водой, лесом или чем-то подобным.

Для этого:

Я создаю 30-метровый буфер вокруг каждого пути и вычисляю пересечение между этим буфером и любыми зелеными объектами поблизости.

Я использую «зеленые объекты» для обозначения полигонов в базе данных OpenStreetMap, таких как парк.

Можно ли ускорить этот расчет?

Одна вещь, которую я хотел создать, это создать 2 индекса в надежде ускорить расчет:

CREATE INDEX way_index_2 on planet_osm_polygon USING gist(way) WHERE "natural" IN ('water','wood','forest','hill','valley');
CREATE INDEX way_index_3 on planet_osm_polygon USING gist(way) WHERE "landuse" IN ('forest');

Вот «объяснение» к этому утверждению:

EXPLAIN (ANALYZE, BUFFERS) SELECT                                                                
    l.osm_id,
    sum(
        st_area(st_intersection(ST_Buffer(l.way, 30), p.way))
        /
        st_area(ST_Buffer(l.way, 30))
    ) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, ST_Buffer(p.way,30))
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id limit 1;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17816.83..133789235.22 rows=1 width=16) (actual time=1575643.737..1575651.862 rows=1 loops=1)
   Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
   ->  GroupAggregate  (cost=17816.83..1435958589062981.00 rows=10734420 width=16) (actual time=1575643.723..1575651.847 rows=1 loops=1)
         Group Key: l.osm_id
         Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
         ->  Nested Loop  (cost=17816.83..1433802261939271.50 rows=28652652777 width=448) (actual time=978502.788..1575648.857 rows=8 loops=1)
               Join Filter: st_intersects(l.way, st_buffer(p.way, '30'::double precision, ''::text))
               Rows Removed by Join Filter: 6528525
               Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
               ->  Index Scan using osm_id_idx on planet_osm_line l  (cost=0.44..1242021.57 rows=22671610 width=247) (actual time=5.963..6.181 rows=6 loops=1)
                     Buffers: shared hit=5 read=3
               ->  Materialize  (cost=17816.39..1445364.98 rows=1263812 width=201) (actual time=85.181..4605.942 rows=1088089 loops=6)
                     Buffers: shared hit=380236 read=220348, temp read=313661 written=73938
                     ->  Gather  (cost=17816.39..1403253.92 rows=1263812 width=201) (actual time=510.609..1066.182 rows=1250378 loops=1)
                           Workers Planned: 4
                           Workers Launched: 4
                           Buffers: shared hit=380236 read=220348
                           ->  Parallel Bitmap Heap Scan on planet_osm_polygon p  (cost=16816.39..1275872.72 rows=315953 width=201) (actual time=447.168..9410.838 rows=250076 loops=5)
                                 Recheck Cond: (("natural" = ANY ('{water,wood,forest,hill,valley}'::text[])) OR (landuse = 'forest'::text))
                                 Rows Removed by Index Recheck: 2554266
                                 Filter: (("natural" = 'water'::text) OR (landuse = 'forest'::text))
                                 Rows Removed by Filter: 53217
                                 Heap Blocks: lossy=1
                                 Buffers: shared hit=380236 read=220348
                                 ->  BitmapOr  (cost=16816.39..16816.39 rows=1554297 width=0) (actual time=491.891..491.893 rows=0 loops=1)
                                       Buffers: shared hit=7797
                                       ->  Bitmap Index Scan on way_index_2  (cost=0.00..7822.79 rows=750359 width=0) (actual time=413.690..413.690 rows=737741 loops=1)
                                             Buffers: shared hit=3758
                                       ->  Bitmap Index Scan on way_index_3  (cost=0.00..8361.69 rows=803938 width=0) (actual time=78.198..78.198 rows=783702 loops=1)
                                             Buffers: shared hit=4039
 Planning Time: 0.315 ms
 Execution Time: 1575673.609 ms
(32 rows)

ОБНОВЛЯТЬ:

Вот краткая схема обеих таблиц;

                                                Table 

"public.planet_osm_line"
       Column       |           Type            | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------+---------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 osm_id             | bigint                    |           |          |         | plain    |             |              | 
 access             | text                      |           |          |         | extended |             |              | 
 addr:housename     | text                      |           |          |         | extended |             |              | 
 addr:housenumber   | text                      |           |          |         | extended |             |              | 
 addr:interpolation | text                      |           |          |         | extended |             |              | 
 admin_level        | text                      |           |          |         | extended |             |              | 
....
natural            | text                      |           |          |         | extended |             |              | 



Indexes:
    "highway_idx" btree (highway)
    "motorway_idx" gist (way) WHERE highway = 'motorway'::text
    "motorway_trunk_primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['motorway'::text, 'trunk'::text, 'primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
    "name_idx" btree (name)
    "osm_id_idx" btree (osm_id)
    "planet_osm_line_osm_id_idx" btree (osm_id)
    "planet_osm_line_way_idx" gist (way)
    "primary_idx" gist (way) WHERE highway = 'primary'::text
    "primary_secondary_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text])
    "primary_secondary_tertiary_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text, 'tertiary'::text])
    "primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
    "secondary_idx" gist (way) WHERE highway = 'secondary'::text
    "secondary_tertiary_idx" gist (way) WHERE highway = ANY (ARRAY['secondary'::text, 'tertiary'::text])
    "tertiary_idx" gist (way) WHERE highway = 'tertiary'::text
    "tertiary_secondary_idx" gist (way) WHERE highway = ANY (ARRAY['tertiary'::text, 'unclassified'::text])
    "trunk_idx" gist (way) WHERE highway = 'trunk'::text
    "trunk_primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['trunk'::text, 'primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
    "unclassified_idx" gist (way) WHERE highway = 'unclassified'::text
    "way_idx" gist (way)
    "way_index_1" gist (way)
    "way_index_4" gist (way) WHERE "natural" = ANY (ARRAY['water'::text, 'wood'::text, 'forest'::text, 'hill'::text, 'valley'::text])
    "way_index_5" gist (way) WHERE landuse = 'forest'::text
Triggers:
    planet_osm_line_osm2pgsql_valid BEFORE INSERT OR UPDATE ON planet_osm_line FOR EACH ROW EXECUTE FUNCTION planet_osm_line_osm2pgsql_valid()
Access method: heap

И:

                                                 Table "public.planet_osm_polygon"
       Column       |          Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 osm_id             | bigint                  |           |          |         | plain    |             |              | 
 access             | text                    |           |          |         | extended |             |              | 
 addr:housename     | text                    |           |          |         | extended |             |              | 
 addr:housenumber   | text                    |           |          |         | extended |             |              | 
 addr:interpolation | text                    |           |          |         | extended |             |              | 
 admin_level        | text                    |           |          |         | extended |             |              | 
 aerialway          | text                    |           |          |         | extended |             |              | 
 aeroway            | text                    |           |          |         | extended |             |              | 
 amenity            | text                    |           |          |         | extended |             |              | 
 area               | text                    |           |          |         | extended |             |              | 
 barrier            | text                    |           |          |         | extended |             |              | 
landuse            | text                    |           |          |         | extended |             |              | 


Indexes:
    "fuel_toilet_parking_restaurant_idex" gist (way) WHERE amenity = ANY (ARRAY['fuel'::text, 'toilets'::text, 'parking'::text, 'restaurant'::text, 'cafe'::text, 'pub'::text, 'ice_cream'::text, 'biergarten'::text])
    "planet_osm_polygon_osm_id_idx" btree (osm_id)
    "planet_osm_polygon_way_idx" gist (way)
    "viewpoint_attraction_guest_house_idex" gist (way) WHERE tourism = ANY (ARRAY['viewpoint '::text, 'attraction'::text, 'guest_house'::text])
    "way_index_2" gist (way) WHERE "natural" = ANY (ARRAY['water'::text, 'wood'::text, 'forest'::text, 'hill'::text, 'valley'::text])
    "way_index_3" gist (way) WHERE landuse = 'forest'::text
Triggers:
    planet_osm_polygon_osm2pgsql_valid BEFORE INSERT OR UPDATE ON planet_osm_polygon FOR EACH ROW EXECUTE FUNCTION planet_osm_polygon_osm2pgsql_valid()
Access method: heap

ОБНОВЛЯТЬ:

Planet_osm_polygon

wood               | text                    |           |          |                                                     | extended |             |              | 
 z_order            | integer                 |           |          |                                                     | plain    |             |              | 
 way_area           | real                    |           |          |                                                     | plain    |             |              | 
 way                | geometry(Geometry,3857) |           |          |                                                     | main     |             |              | 
 way_buffer_30      | geometry(Polygon)       |           |          |                                                     | external |             |              | 
 way_buffer_30_area | numeric                 |           |          | generated always as (st_area(way_buffer_30)) stored | main     |             |              | 

И

planet_osm_line

way_area           | real                      |           |          |         | plain    |             |              | 
 way                | geometry(LineString,3857) |           |          |         | main     |             |              | 

Вы должны запустить EXPLAIN (ANALYZE, BUFFERS) SELECT ... и добавить этот вывод в свой вопрос.

Adrian Klaver 08.01.2023 18:00

Привет, Адриан, я обновил вопрос выводом EXPLAIN (ANALYZE, BUFFERS).

Andreas 08.01.2023 19:06

Попробуйте обычный индекс btree на planet_osm_polygon(natural, landuse) для предложения where и пространственный индекс на planet_osm_line using gist(way) для соединения. Индекс planet_osm_line(osm_id) может помочь группе. Вы также можете попробовать dba.SE и gis.SE.

Schwern 08.01.2023 20:16

Пространственный индекс на planet_osm_line уже на месте. СОЗДАЙТЕ ИНДЕКС way_idx ON planet_osm_line, ИСПОЛЬЗУЯ gist(way); Также указатель на planet_osm_line(osm_id). Насколько я понимаю, они не используются или игнорируются.

Andreas 08.01.2023 21:15

Вы уже запускали АНАЛИЗ на столах?

Adrian Klaver 08.01.2023 21:37

Ага. Мне было интересно, могу ли я ограничить область bbox чем-нибудь с помощью planet_osm_line.way && ST_Transform(ST_MakeEnvelope(9.07805053255787, 47.4781579714091, 12.0860304569466, 49.478071667293,4326),3857);"?

Andreas 08.01.2023 22:18

@Andreas Можете ли вы показать полную схему каждой таблицы? Это скажет нам, что такое индексы и типы.

Schwern 08.01.2023 22:22

@Schwern Конечно: я обновил вопрос, включив в него большую часть схемы таблиц.

Andreas 08.01.2023 22:33

@Andreas Андреас Не могли бы вы отредактировать DDL, чтобы показать определения столбцов planet_osm_polygon.way и planet_osm_line.way? Кроме того, похоже, что вы создаете довольно много индексов - вы можете удалить те, которые не помогают каким-либо часто выполняемым запросам. Если вы часто выполняете сложные запросы, которые никогда не повторяете, вы можете создавать собственные клоны этих таблиц с пользовательскими индексами. Нередко при работе с огромными наборами геометрий быстрее построить и настроить клон, чем повторно использовать исходную таблицу.

Zegarek 08.01.2023 22:54

Упс, извините, упустил важный момент :) Я обновил вопрос, включив в него определения столбцов planet_osm_polygon.way и planet_osm_line.way.

Andreas 08.01.2023 23:02

Может быть, я чего-то не понимаю в основных указателях, но многие из этих указателей кажутся избыточными.

Schwern 09.01.2023 04:00

Всегда помните о ST_Subdivide при работе с большими геометриями: stackoverflow.com/a/68622815/2275388 ура!

Jim Jones 09.01.2023 13:48
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
12
93
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

На самом деле это не столько ответ, сколько куча комментариев, слишком длинных для формата комментариев:

alter system set default_toast_compression=lz4;

alter table planet_osm_polygon 
    add column way_buffer_30 geometry, 
    alter column way_buffer_30 set storage external,
    add column way_buffer_30_area numeric 
        generated always as (st_area(way_buffer_30)) stored;
update planet_osm_polygon 
    set way_buffer_30=st_buffer(way,30,'quad_segs=1');
create index osmp_way_buffer_30_gix on planet_osm_polygon 
    using gist(way_buffer_30) with (fillfactor=100);
cluster verbose planet_osm_polygon using osmp_way_buffer_30_gix;

alter table planet_osm_line
    add column way_buffer_30 geometry, 
    alter column way_buffer_30 set storage external,
    add column way_buffer_30_area numeric 
        generated always as (st_area(way_buffer_30)) stored; 
update planet_osm_line
    set way_buffer_30=st_buffer(way,30,'quad_segs=1');
create index osml_way_buffer_30_gix on planet_osm_line
    using gist(way_buffer_30) with (fillfactor=100);
cluster verbose planet_osm_polygon using osml_way_buffer_30_gix;

SELECT                                                                
    l.osm_id,
    sum(st_area(st_intersection(l.way_buffer_30, p.way))
        / l.way_buffer_30_area
    ) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, p.way_buffer_30)
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id;
  1. default_toast_compression=lz4 Вы работаете с полигонами, которые, скорее всего, будут сжаты и обработаны TOAST. По умолчанию default_toast_compression=pglz обычно медленнее, чем lz4. Обратите внимание, что вам необходимо принудительно перезаписать эти таблицы/столбцы после изменения параметра, иначе он будет применяться по умолчанию только с этого момента, не затрагивая ничего задним числом.
  2. внешнее хранилище пропускает один шаг при извлечении данных, если фигуры достаточно большие/сложные. Может быть стоит в зависимости от вашей версии PostGIS.
  3. st_buffer(way,30,'quad_segs=1') вы можете настроить третий параметр, чтобы получить более простую форму, которую легче сравнивать. Значение по умолчанию quad_segs=8 может привести к тому, что ваш буфер будет иметь в 8 раз больше вершин, чем вход.
  4. with (fillfactor=100) Я предполагаю, что это статическая исходная таблица, которую вы заменяете при публикации новой версии, а не поддерживаете текущую самостоятельно. Следовательно, индексы также можно сделать статическими (по умолчанию fillfactor=90 предназначен для учета новых строк, вставленных в таблицу).
  5. кластер выравнивает страницы таблицы с индексом, ускоряя выборку кучи.
  6. Индекс конечной формы буфера, которую вы хотите использовать, будет лучше, чем индекс столбца, который вы планируете использовать для его создания с помощью ST_Buffer(). То же самое касается ST_Transform() — примените его к столбцу перед индексацией, а не к запросу.
  7. ST_Разделить все вещи.
  8. Вы можете проверить, ускоряет ли ST_DWithin() соединение по сравнению с вашим текущим кодом и кодом, предложенным выше. Обязательно протестируйте с подходящим индексом. Имейте в виду, что единица измерения расстояния зависит от столбца SRID — если вы используете метрику, вы получите аномалии от систем в имперских/морских/градусах.
    INNER JOIN planet_osm_polygon AS p ON ST_DWithin(l.way,p.way,30)
    
  9. Вы можете проверить, стоит ли вообще ваш частичный индекс, и посмотреть, будет ли он работать лучше, если вы сузите его для целей этого запроса.
    create index way_buffer_30_gix on planet_osm_polygon 
    using gist(way_buffer_30) with (fillfactor=100)
    where natural='water' or landuse='forest';
    
  10. Большая часть вышеперечисленного имеет смысл только в том случае, если это разовая операция или если вы можете клонировать целевые таблицы, чтобы вам не приходилось иметь дело с другими индексами и настройками, которые должны улучшить производительность операций, которые вы выполняете на этой таблице. чаще. Вы также можете отфильтровать все, что вас не интересует на данном этапе, используя это предложение where.
  11. Если это действительно медленно, извлеките подмножество строк из обеих таблиц и запустите свои тесты на них. Чтобы получить более надежные результаты, используйте pgbench для запуска тестов.

Спасибо, я попробую ваши предложения и сообщу. Да, это разовая операция. Я использую SELECT PostGIS_Version(); 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (1 строка)

Andreas 08.01.2023 22:27

У меня возникнет ошибка, если я выполню шаг 3. (ОШИБКА: тип геометрии (MultiPolygon) не соответствует типу столбца (Polygon))

Andreas 08.01.2023 22:44

Вы можете обойти это, удалив (POLYGON) из определения столбца, сделав его общим geometry. Это также хорошее время, чтобы перепроверить, должен ли ваш ввод быть MULTILINESTRING для начала, и если вы обычно обрабатываете его как таковой.

Zegarek 08.01.2023 22:46

Другие вопросы по теме