Один и тот же скрипт с разным условием выбирает разные методы выполнения и как следствие разное время на выходе, как это исправить оставив метод с минимальным временем выполнения?
Инфа:
Скрипт 1:
explain analyze SELECT count(DISTINCT c.card_id)
FROM card c
JOIN attribute_value av99 ON (
(av99.card_id = c.card_id)
AND ( av99.attribute_code IN ('NAME'))
AND ( av99.string_value IS NOT NULL)
AND UPPER(av99.string_value) like UPPER('%пись%')
)
INNER JOIN card_access as ca ON (
ca.object_id = c.status_id AND
ca.template_id = c.template_id AND
ca.permission_type in (2)
)
LEFT JOIN attribute_value as avUsr ON (
avUsr.card_id = c.card_id AND
avUsr.attribute_code = ca.person_attribute_code AND
avUsr.number_value = 7820
)
WHERE (1=1)
AND(
(ca.role_code IS NULL AND ca.person_attribute_code IS NULL)
OR (
(
ca.role_code is NULL
OR EXISTS(
select 1
from person_role pr left join person_role_template prt
on pr.prole_id = prt.prole_id
where
COALESCE(prt.template_id, ca.template_id) = ca.template_id
and pr.role_code = ca.role_code
and pr.person_id = 7820
) -- OR EXISTS
) --
AND (
ca.person_attribute_code IS NULL
OR avUsr.attribute_code is not NULL
) -- AND
) -- OR
) -- AND
AND c.template_id = (2300)
AND c.status_id IN (4,7);
Результат:
"Aggregate (cost=1511.95..1511.96 rows=1 width=6) (actual time=7435.362..7435.362 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=51.90..1511.95 rows=1 width=6) (actual time=6291.725..7434.568 rows=1 loops=1)"
" Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
" -> Nested Loop (cost=51.33..1417.82 rows=1 width=33) (actual time=6291.717..7434.560 rows=1 loops=1)"
" -> Nested Loop (cost=51.05..1416.65 rows=1 width=16) (actual time=6291.684..7434.523 rows=1 loops=1)"
" -> Bitmap Heap Scan on attribute_value av99 (cost=50.62..1213.49 rows=24 width=6) (actual time=1260.930..3970.662 rows=582949 loops=1)"
" Recheck Cond: ((upper((string_value)::text) ~~ '%ПИСЬ%'::text) AND ((attribute_code)::text = 'NAME'::text))"
" Rows Removed by Index Recheck: 10"
" Filter: (string_value IS NOT NULL)"
" -> Bitmap Index Scan on test_pg_trgm_string_value_idx (cost=0.00..50.61 rows=293 width=0) (actual time=1098.093..1098.093 rows=584965 loops=1)"
" Index Cond: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
" -> Index Scan using test_card2_id_idx on card c (cost=0.43..8.46 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=582949)"
" Index Cond: (card_id = av99.card_id)"
" Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 2300::numeric))"
" Rows Removed by Filter: 1"
" -> Index Scan using card_access_idx_tmpl_objid on card_access ca (cost=0.29..1.17 rows=1 width=32) (actual time=0.019..0.023 rows=1 loops=1)"
" Index Cond: ((object_id = c.status_id) AND (template_id = 2300::numeric))"
" Filter: (permission_type = 2::numeric)"
" Rows Removed by Filter: 3"
" -> Index Scan using xif6attribute_value on attribute_value avusr (cost=0.57..16.36 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=1)"
" Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
" Filter: (number_value = 7820::numeric)"
" SubPlan 1"
" -> Nested Loop Left Join (cost=0.70..77.76 rows=1 width=0) (never executed)"
" Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
" -> Index Scan using xif1person_role on person_role pr (cost=0.28..12.76 rows=1 width=6) (never executed)"
" Index Cond: (person_id = 7820::numeric)"
" Filter: ((role_code)::text = (ca.role_code)::text)"
" -> Index Scan using xif2person_role_template on person_role_template prt (cost=0.42..64.60 rows=32 width=12) (never executed)"
" Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 7435.589 ms"
Скрипт 2:
explain analyze SELECT count(DISTINCT c.card_id)
FROM card c
JOIN attribute_value av99 ON (
(av99.card_id = c.card_id)
AND ( av99.attribute_code IN ('NAME'))
AND ( av99.string_value IS NOT NULL)
AND UPPER(av99.string_value) like UPPER('%пис%')
)
INNER JOIN card_access as ca ON (
ca.object_id = c.status_id AND
ca.template_id = c.template_id AND
ca.permission_type in (2)
)
LEFT JOIN attribute_value as avUsr ON (
avUsr.card_id = c.card_id AND
avUsr.attribute_code = ca.person_attribute_code AND
avUsr.number_value = 7820
)
WHERE (1=1)
AND(
(ca.role_code IS NULL AND ca.person_attribute_code IS NULL)
OR (
(
ca.role_code is NULL
OR EXISTS(
select 1
from person_role pr left join person_role_template prt
on pr.prole_id = prt.prole_id
where
COALESCE(prt.template_id, ca.template_id) = ca.template_id
and pr.role_code = ca.role_code
and pr.person_id = 7820
) -- OR EXISTS
) --
AND (
ca.person_attribute_code IS NULL
OR avUsr.attribute_code is not NULL
) -- AND
) -- OR
) -- AND
AND c.template_id = (2300)
AND c.status_id IN (4,7);
Результат:
"Aggregate (cost=3300.11..3300.12 rows=1 width=6) (actual time=2.329..2.329 rows=1 loops=1)"
" -> Nested Loop Left Join (cost=14.68..3300.10 rows=1 width=6) (actual time=0.257..2.292 rows=6 loops=1)"
" Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
" -> Nested Loop (cost=14.12..3205.98 rows=1 width=33) (actual time=0.250..2.280 rows=6 loops=1)"
" -> Nested Loop (cost=13.44..887.25 rows=184 width=33) (actual time=0.114..0.555 rows=214 loops=1)"
" -> Bitmap Heap Scan on card_access ca (cost=4.45..43.07 rows=16 width=32) (actual time=0.022..0.027 rows=29 loops=1)"
" Recheck Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
" -> Bitmap Index Scan on card_access_idx_tmpl_perm_objid (cost=0.00..4.45 rows=16 width=0) (actual time=0.016..0.016 rows=29 loops=1)"
" Index Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
" -> Bitmap Heap Scan on card c (cost=8.99..52.65 rows=11 width=16) (actual time=0.011..0.015 rows=7 loops=29)"
" Recheck Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
" -> Bitmap Index Scan on template_and_status_idx (cost=0.00..8.98 rows=11 width=0) (actual time=0.006..0.006 rows=7 loops=29)"
" Index Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
" -> Index Scan using attribute_value_name_string_value_idx on attribute_value av99 (cost=0.68..12.59 rows=1 width=6) (actual time=0.008..0.008 rows=0 loops=214)"
" Index Cond: ((card_id = c.card_id) AND (string_value IS NOT NULL))"
" Filter: (upper((string_value)::text) ~~ '%ПИС%'::text)"
" Rows Removed by Filter: 1"
" -> Index Scan using xif6attribute_value on attribute_value avusr (cost=0.57..16.36 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=6)"
" Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
" Filter: (number_value = 7820::numeric)"
" SubPlan 1"
" -> Nested Loop Left Join (cost=0.70..77.76 rows=1 width=0) (never executed)"
" Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
" -> Index Scan using xif1person_role on person_role pr (cost=0.28..12.76 rows=1 width=6) (never executed)"
" Index Cond: (person_id = 7820::numeric)"
" Filter: ((role_code)::text = (ca.role_code)::text)"
" -> Index Scan using xif2person_role_template on person_role_template prt (cost=0.42..64.60 rows=32 width=12) (never executed)"
" Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 2.462 ms"
Индексы:
(данный индекс создавался для другого похожего запроса и оправдал себя в нём на 2000%, в отличие от данного)
CREATE INDEX test_pg_trgm_string_value_idx
ON attribute_value
USING gist
(upper(string_value::text) COLLATE pg_catalog."default" gist_trgm_ops)
WHERE attribute_code::text = 'NAME'::text;
CREATE INDEX attribute_value_name_string_value_idx
ON attribute_value
USING btree
(card_id, string_value COLLATE pg_catalog."default")
WHERE attribute_code::text = 'NAME'::text;
Разница в скриптах в отличие от времени их выполнения смешная:
%пись%
и
%пис%
.