Можно ли оптимизировать эту комбинацию запроса/таблицы? (поиск bbox/геометрия)

У меня есть графики таблицы со столбцом bbox:

             Column             |            Type             | Collation | Nullable |              Default               | Storage  | Compression | Stats target | Description 
--------------------------------+-----------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
 id                             | integer                     |           | not null | nextval('graphs_id_seq'::regclass) | plain    |             |              | 
 bbox_diagonale                 | double precision            |           |          |                                    | plain    |             |              | 
 bbox                           | geometry(Polygon,4326)      |           |          |                                    | external |             |              | 
Indexes:
    "graphs_pkey" PRIMARY KEY, btree (id)
    "bbox_diagonale_idx" btree (bbox_diagonale)
    "bbox_index" gist (bbox) CLUSTER
Access method: heap

Моя цель - найти запись в графах таблицы, где bbox либо совпадает с тем, который я ищу, либо максимально в 1,4 раза больше запрошенного и содержит запрошенный bbox.

Для этого я использую, например, следующую команду sql:

SELECT id, bbox,BBOX_Diagonale FROM graphs 
WHERE (
ST_CONTAINS(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326)) 
OR 
ST_Equals(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326))) 
AND  BBOX_Diagonale <= 91   
order by BBOX_Diagonale ASC LIMIT 1;

Для ускорения поиска я создал следующие индексы:

CREATE INDEX bbox_index ON graphs USING gist(bbox);

CREATE INDEX BBOX_Diagonale_idx ON graphs (
  BBOX_Diagonale ASC
);

Вот распечатка объяснения, если я запущу этот запрос:

EXPLAIN (ANALYZE, BUFFERS) SELECT id, bbox,BBOX_Diagonale FROM graphs 
    WHERE (
    ST_CONTAINS(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326)) 
    OR 
    ST_Equals(bbox,ST_MakeEnvelope( 11.71540516 , 47.77092524 , 12.32288277 , 48.17883335 , 4326))) 
    AND  BBOX_Diagonale <= 91   
    order by BBOX_Diagonale ASC LIMIT 1;
                                                                                                                                                                                                                                                                     QUERY PLAN                                                                                                                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4476.66..4476.66 rows=1 width=132) (actual time=9.484..9.488 rows=1 loops=1)
   Buffers: shared hit=506
   ->  Sort  (cost=4476.66..4477.21 rows=219 width=132) (actual time=9.483..9.486 rows=1 loops=1)
         Sort Key: bbox_diagonale
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=506
         ->  Bitmap Heap Scan on graphs  (cost=591.39..4475.56 rows=219 width=132) (actual time=9.454..9.458 rows=1 loops=1)
               Recheck Cond: ((st_contains(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)) AND (bbox_diagonale <= '91'::double precision))
               Filter: (st_contains(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry))
               Heap Blocks: exact=1
               Buffers: shared hit=503
               ->  BitmapAnd  (cost=591.39..591.39 rows=76 width=0) (actual time=9.383..9.385 rows=0 loops=1)
                     Buffers: shared hit=502
                     ->  BitmapOr  (cost=6.69..6.69 rows=216 width=0) (actual time=3.165..3.166 rows=0 loops=1)
                           Buffers: shared hit=256
                           ->  Bitmap Index Scan on bbox_index  (cost=0.00..3.29 rows=108 width=0) (actual time=2.417..2.418 rows=2618 loops=1)
                                 Index Cond: (bbox ~ '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)
                                 Buffers: shared hit=128
                           ->  Bitmap Index Scan on bbox_index  (cost=0.00..3.29 rows=108 width=0) (actual time=0.745..0.746 rows=0 loops=1)
                                 Index Cond: (bbox ~= '0103000020E61000000100000005000000E62DCB95496E274000BBA2ADADE24740E62DCB95496E274091D7DE02E41648400C2FF3E350A5284091D7DE02E41648400C2FF3E350A5284000BBA2ADADE24740E62DCB95496E274000BBA2ADADE24740'::geometry)
                                 Buffers: shared hit=128
                     ->  Bitmap Index Scan on bbox_diagonale_idx  (cost=0.00..584.39 rows=38117 width=0) (actual time=6.068..6.068 rows=37983 loops=1)
                           Index Cond: (bbox_diagonale <= '91'::double precision)
                           Buffers: shared hit=246
 Planning:
   Buffers: shared hit=247
 Planning Time: 28.373 ms
 Execution Time: 9.762 ms

Поиск больших ящиков занимает много времени. Можно ли оптимизировать эту комбинацию запроса/таблицы?

ОБНОВЛЕНИЕ (более медленный запрос)

EXPLAIN (ANALYZE, BUFFERS) SELECT id, bbox,BBOX_Diagonale FROM graphs 
WHERE (
ST_CONTAINS(bbox,ST_MakeEnvelope( 9.272461,48.019324,12.700195,51.034486 , 4326)) 
OR 
ST_Equals(bbox,ST_MakeEnvelope( 9.272461,48.019324,12.700195,51.034486, 4326))) 
AND  BBOX_Diagonale <= 410   
order by BBOX_Diagonale ASC LIMIT 1;

                                                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..229.62 rows=1 width=132) (actual time=127.426..127.426 rows=0 loops=1)
   Buffers: shared hit=83455
   ->  Index Scan using bbox_diagonale_idx on graphs  (cost=0.42..4513494.64 rows=19692 width=132) (actual time=127.424..127.424 rows=0 loops=1)
         Index Cond: (bbox_diagonale <= '410'::double precision)
         Filter: (st_contains(bbox, '0103000020E61000000100000005000000F4DE1802808B22409203763579024840F4DE1802808B2240BE1589096A8449403CA583F57F662940BE1589096A8449403CA583F57F6629409203763579024840F4DE1802808B22409203763579024840'::geometry) OR st_equals(bbox, '0103000020E61000000100000005000000F4DE1802808B22409203763579024840F4DE1802808B2240BE1589096A8449403CA583F57F662940BE1589096A8449403CA583F57F6629409203763579024840F4DE1802808B22409203763579024840'::geometry))
         Rows Removed by Filter: 89567
         Buffers: shared hit=83455
 Planning Time: 0.299 ms
 Execution Time: 127.457 ms
(9 rows)

Не могли бы вы также поделиться планом запроса для медленных запросов? Этот не такой медленный, около 9 миллисекунд на выполнение.

Frank Heikens 10.01.2023 22:04

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

Andreas 10.01.2023 22:42

Что я тоже не понимаю: первый запрос использует несколько индексов (bbox_index, bbox_diagonale_idx), а второй использует только один индекс (bbox_diagonale_idx)

Andreas 10.01.2023 23:03

Попробуйте избавиться от OR.

Laurenz Albe 10.01.2023 23:40

Является ли ST_CONTAINS = ST_Equals, если площадь полигона одинакова?

Andreas 10.01.2023 23:53

Что касается st_contains и st_equals, можно увидеть, что матрица пересечения для equals является подмножеством матрицы для contains, поэтому ожидается, что геометрия будет содержать другую равную геометрию.

JGH 11.01.2023 15:04
Руководство для начинающих по веб-разработке на React.js
Руководство для начинающих по веб-разработке на React.js
Веб-разработка - это захватывающая и постоянно меняющаяся область, которая постоянно развивается благодаря новым технологиям и тенденциям. Одним из...
Разница между Angular и React
Разница между Angular и React
React и AngularJS - это два самых популярных фреймворка для веб-разработки. Оба фреймворка имеют свои уникальные особенности и преимущества, которые...
Инструменты для веб-скрапинга с открытым исходным кодом: Python Developer Toolkit
Инструменты для веб-скрапинга с открытым исходным кодом: Python Developer Toolkit
Веб-скрейпинг, как мы все знаем, это дисциплина, которая развивается с течением времени. Появляются все более сложные средства борьбы с ботами, а...
Калькулятор CGPA 12 для семестра
Калькулятор CGPA 12 для семестра
Чтобы запустить этот код и рассчитать CGPA, необходимо сохранить код как HTML-файл, а затем открыть его в веб-браузере. Для этого выполните следующие...
ONLBest Online HTML CSS JAVASCRIPT Training In INDIA 2023
ONLBest Online HTML CSS JAVASCRIPT Training In INDIA 2023
О тренинге HTML JavaScript :HTML (язык гипертекстовой разметки) и CSS (каскадные таблицы стилей) - две основные технологии для создания веб-страниц....
Как собрать/развернуть часть вашего приложения Angular
Как собрать/развернуть часть вашего приложения Angular
Вам когда-нибудь требовалось собрать/развернуть только часть вашего приложения Angular или, возможно, скрыть некоторые маршруты в определенных средах?
0
6
61
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

Похоже, postgis оценивает количество перекрывающихся фигур и использует эту оценку в качестве оценки для st_contains. Это печально для вас, так как чем больше форма, тем больше вещей может ее перекрывать, но тем меньше вещей может ее содержать. Но эта оценка в некотором роде разумна, так как нужно будет посетить эти строки только для того, чтобы затем их отклонить.

Когда он резко переоценивает количество строк, соответствующих st_contains, он думает, что будет лучше использовать индекс для чтения строк, уже упорядоченных BBOX_Diagonale, а затем остановиться после первой. Вот что он делает, хотя никогда не находит, перед чем остановиться. Если вы перепишете запрос, чтобы он выглядел как ORDER BY BBOX_Diagonale+0, это заставит его не использовать этот индекс.

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