#postgresql #stored-procedures #postgis
#postgresql #хранимые процедуры #postgis
Вопрос:
У меня есть хранимая процедура, выполнение которой занимает около 3 с половиной минут. У меня там правильные индексы, и я не могу оптимизировать их дальше.
Может кто-нибудь, пожалуйста, предложить какие-либо очевидные вещи, которые я, возможно, упускаю?
Триггер:
CREATE CONSTRAINT TRIGGER create_notifications
AFTER INSERT ON events
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
EXECUTE PROCEDURE create_notifications();
Определение функции:
CREATE OR REPLACE FUNCTION public.create_notifications()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO
notifications(device_id, event_id, status, region, watch_zones_count, watch_zones)
SELECT
w.device_id as device_id,
p.event_id as event_id,
'pending' as status,
r.region,
count(w.id) as watch_zones_count,
json_agg(w.*) as watch_zones
from
event_polygons p,
watch_zones w,
regions r
where
p.event_id = NEW.id
and (CASE
WHEN p.type='polygon' then St_dwithin(p.polygon, w.position, w.radius*1000)
WHEN (p.type='circle' and p.radius=0) then St_dwithin(p.position, w.position, w.radius*1000)
WHEN (p.type='circle' and p.radius>0) then St_dwithin(ST_BUFFER(p.position, p.radius*1000), w.position, w.radius*1000)
END)
group by
w.device_id,
p.event_id,
r.id;
RETURN NULL;
END $function$
Вот результат explain analyze из выбранной части хранимой процедуры. Я заменил NEW.id с помощью 102 для анализа выбранного
"GroupAggregate (cost=79678655.89..82662890.29 rows=33978240 width=116) (actual time=207466.320..209109.332 rows=33457 loops=1)"
" Group Key: w.device_id, p.event_id, r.id"
" -> Sort (cost=79678655.89..80105240.29 rows=170633760 width=155) (actual time=207466.231..207557.324 rows=316723 loops=1)"
" Sort Key: w.device_id, r.id"
" Sort Method: external merge Disk: 46712kB"
" -> Nested Loop (cost=0.00..2691292.15 rows=170633760 width=155) (actual time=11.270..207062.979 rows=316723 loops=1)"
" -> Nested Loop (cost=0.00..558347.40 rows=167288 width=119) (actual time=11.264..206846.622 rows=316723 loops=1)"
" Join Filter: CASE WHEN (p.type = 'polygon'::shape_type) THEN ((p.polygon amp;amp; _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" amp;amp; _st_expand(p.polygon, ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(p.polygon, w."position", ((w.radius * '1000'::numeric))::double precision, true)) WHEN ((p.type = 'circle'::shape_type) AND (p.radius = '0'::numeric)) THEN ((p."position" amp;amp; _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" amp;amp; _st_expand(p."position", ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(p."position", w."position", ((w.radius * '1000'::numeric))::double precision, true)) WHEN ((p.type = 'circle'::shape_type) AND (p.radius > '0'::numeric)) THEN ((geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)) amp;amp; _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" amp;amp; _st_expand(geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)), ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)), w."position", ((w.radius * '1000'::numeric))::double precision, true)) ELSE NULL::boolean END"
" Rows Removed by Join Filter: 17852"
" -> Seq Scan on event_polygons p (cost=0.00..8.21 rows=1 width=84) (actual time=0.017..0.025 rows=1 loops=1)"
" Filter: (event_id = 102)"
" Rows Removed by Filter: 96"
" -> Seq Scan on watch_zones w (cost=0.00..8799.75 rows=334575 width=152) (actual time=0.092..183.672 rows=334575 loops=1)"
" -> Materialize (cost=0.00..25.30 rows=1020 width=36) (actual time=0.000..0.000 rows=1 loops=316723)"
" -> Seq Scan on regions r (cost=0.00..20.20 rows=1020 width=36) (actual time=0.004..0.005 rows=1 loops=1)"
"Planning time: 0.406 ms"
"Execution time: 209119.715 ms"
Ответ №1:
Это ужасное условие соединения, которое не позволяет PostgreSQL использовать индекс GiST на watch_zones(position) .
Напишите три запроса с фильтром и простым условием объединения, например:
... WHERE p.type='polygon'
AND St_dwithin(p.polygon, w.position, w.radius*1000)
и создайте UNION ALL поверх результатов.
Каждый из этих запросов должен иметь возможность использовать индекс, и вы будете намного быстрее.
Комментарии:
1. Вы считаете, что этот случай мешает работе индекса? Я попробую то, что вы предложили, и обновлю здесь. Спасибо.
2. Является ли ОБЪЕДИНЕНИЕ лучшим вариантом, поскольку ОБЪЕДИНЕНИЕ ВСЕХ не удаляет дубликаты?
3. Привет @Laurenz, даже если я сделаю это простым (удалил все регистры и просто оставил его таким, ГДЕ event_id=102 и p.type=’polygon’ И St_dwithin(p.polygon, w.position, w.radius * 1000) ), я не вижу, чтобы использовался индекс GIST. Это без объединения ВСЕГО. Если объединение препятствует использованию индекса GIST, как я могу избавиться от него?
4. Можете ли вы добавить упрощенный запрос и план его выполнения к вопросу?