Доброго времени суток, коллеги.
Необходима помощь в следующей ситуации: небольшой запрос в хранимой процедуре выполняется значительно медленнее, чем показывает его explain analyze.
explain analyze я вставил непосредственно в хранимую процедуру перед выполнением запроса. Вот результат pg_stat после отработки процедуры:
Это результат непосредственно запроса в pg_stat:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create temporary table TMP_J_RES on commit drop as select ps__r.CONTRACT_ID as __ID
from PRISOS ps__r
inner join CONTRACT c__r
on ps__r.CONTRACT_ID = c__r.CONTRACT_ID
inner join SUBJECT s__r
on c__r.SUBJECT_ID = s__r.SUBJECT_ID
where
lower(s__r.FULL_NAME) like '%иван%'
order by ps__r.PRISOS_SS_PROP desc
offset 0 limit 50
total_time 12790.189798
Это его explain, вызванный прямо в процедуре (в pg_stat):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain analyze select ps__r.CONTRACT_ID as __ID
from PRISOS ps__r
inner join CONTRACT c__r
on ps__r.CONTRACT_ID = c__r.CONTRACT_ID
inner join SUBJECT s__r
on c__r.SUBJECT_ID = s__r.SUBJECT_ID
where
lower(s__r.FULL_NAME) like '%иван%'
order by ps__r.PRISOS_SS_PROP desc
offset 0 limit 50
total_time 4785.438513
Сам план и анализ запроса (из процедуры):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Limit (cost=165361.82..165367.66 rows=50 width=13) (actual time=4602.238..4602.279 rows=50 loops=1)
-> Gather Merge (cost=165361.82..165379.32 rows=150 width=13) (actual time=4602.236..4602.271 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=164361.80..164361.99 rows=75 width=13) (actual time=4597.670..4597.691 rows=148 loops=3)
Sort Key: ps__r.prisos_ss_prop DESC
Sort Method: quicksort Memory: 423kB
-> Nested Loop (cost=0.86..164359.46 rows=75 width=13) (actual time=846.793..4589.192 rows=5159 loops=3)
-> Nested Loop (cost=0.43..164249.30 rows=222 width=8) (actual time=1.650..4485.833 rows=15228 loops=3)
-> Parallel Seq Scan on subject s__r (cost=0.00..162238.90 rows=240 width=8) (actual time=1.594..4291.891 rows=16446 loops=3)
Filter: (lower((full_name)::text) ~~ '%иван%'::text)
Rows Removed by Filter: 1925070
-> Index Scan using fk_subject_contract_fk on contract c__r (cost=0.43..8.37 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=49338)
Index Cond: ((subject_id)::bigint = (s__r.subject_id)::bigint)
-> Index Scan using fk_contract_prisos_fk on prisos ps__r (cost=0.43..0.49 rows=1 width=13) (actual time=0.006..0.006 rows=0 loops=45683)
Index Cond: ((contract_id)::bigint = (c__r.contract_id)::bigint)
Planning time: 1.067 ms
Execution time: 4629.548 ms
Если запрос выполнять из консоли, то время выполнения около 4.5 сек, что соответствует результату explain analyze
work_mem - 1GB
vacuum analyze выполнен прямо перед тестом.
Создание временной таблицы не может быть камнем преткновения, т.к. аналогичные таблицы в этой же процедуре создаются примерно за 3 мс.
Результат запроса - 50 строк, содержащих int8.
