У меня есть графики таблицы со столбцом 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)
Конечно, я обновил вопрос с более медленным запросом. Мне нужно выполнить много таких запросов, поэтому общее время увеличивается. Каждое улучшение приветствуется.
Что я тоже не понимаю: первый запрос использует несколько индексов (bbox_index, bbox_diagonale_idx), а второй использует только один индекс (bbox_diagonale_idx)
Попробуйте избавиться от OR.
Является ли ST_CONTAINS = ST_Equals, если площадь полигона одинакова?
Что касается st_contains и st_equals, можно увидеть, что матрица пересечения для equals является подмножеством матрицы для contains, поэтому ожидается, что геометрия будет содержать другую равную геометрию.
Похоже, postgis оценивает количество перекрывающихся фигур и использует эту оценку в качестве оценки для st_contains. Это печально для вас, так как чем больше форма, тем больше вещей может ее перекрывать, но тем меньше вещей может ее содержать. Но эта оценка в некотором роде разумна, так как нужно будет посетить эти строки только для того, чтобы затем их отклонить.
Когда он резко переоценивает количество строк, соответствующих st_contains, он думает, что будет лучше использовать индекс для чтения строк, уже упорядоченных BBOX_Diagonale, а затем остановиться после первой. Вот что он делает, хотя никогда не находит, перед чем остановиться. Если вы перепишете запрос, чтобы он выглядел как ORDER BY BBOX_Diagonale+0, это заставит его не использовать этот индекс.
Не могли бы вы также поделиться планом запроса для медленных запросов? Этот не такой медленный, около 9 миллисекунд на выполнение.