#postgresql #performance
#postgresql #Производительность
Вопрос:
Я выполняю некоторую настройку производительности, и в журнале postgres появляется 1 конкретный запрос.
Запрос вставлен ниже:
select distinct sc.CAPABILITY_NAME as "CAPABILITY" from SYSTEM_CAPABILITY sc join ROLE_CAPABILITY urc on urc.CAPABILITY=sc.CAPABILITY_NAME join ROLE_CAPABILITY grc on grc.CAPABILITY=urc.CAPABILITY join GROUP_USER gu on gu.ROLE=urc.ROLE_NAME join GROUP_ROLE gr on gr.ROLE_NAME=grc.ROLE_NAME where gu.USERNAME='TEST123' and (gu.EXPIRY_DATE is null or gu.EXPIRY_DATE>'2020-11-17 00:00:00 01') and gr.ID=gu.GROUP_ID and gu.GROUP_ID='ABC123'
Когда я запускаю этот запрос непосредственно к базе данных, это занимает миллисекунды, однако, когда он запускается из приложения Java, это занимает более 2 секунд.
Я включил приведенное ниже в файле конфигурации postgres. session_preload_libraries = ‘auto_explain’ auto_explain.log_min_duration = ‘1s’
При выполнении запроса из моего локального я вижу, что при сравнении с запуском из Java-приложения, в котором мой локальный использует хэш-соединение, где при запуске из java-приложения используется вложенный цикл.
Затем я включил параметр enable_hashjoin = on только в качестве теста, но похоже, что тест через Java-приложение все еще использует вложенный цикл. Я запустил запрос одновременно с Java-приложением, но он занимает всего 17 мс и использует hash_join .
Во-первых, разумно ли вносить такие изменения в конфигурационный файл postgres. Я понимаю, что это также может повлиять на другие запросы, на данный момент это скорее тестовая фаза для оценки воздействия.
Во-вторых, кроме установки enable_hashjoin = on, есть ли что-нибудь еще, что мне нужно сделать, чтобы не допустить, чтобы вложенные циклы были первой точкой вызова для запросов.
Результаты запроса из auto_explain ниже:
Java-приложение
< 2020-11-18 10:55:57.012 CET >LOG: duration: 2212.992 ms execute S_107: select distinct sc.CAPABILITY_NAME as "CAPABILITY" from SYSTEM_CAPABILITY sc join ROLE_CAPABILITY urc on urc.CAPABILITY=sc.CAPABILITY_NAME join ROLE_CAPABILITY grc on grc.CAPABILITY=urc.CAPABILITY join GROUP_USER gu on gu.ROLE=urc.ROLE_NAME join GROUP_ROLE gr on gr.ROLE_NAME=grc.ROLE_NAME where gu.USERNAME=$1 and (gu.EXPIRY_DATE is null or gu.EXPIRY_DATE>$2) and gr.ID=gu.GROUP_ID and gu.GROUP_ID=$3
< 2020-11-18 10:55:57.012 CET >DETAIL: parameters: $1 = 'TEST123', $2 = '2020-11-17 00:00:00 01', $3 = 'ABC123'
< 2020-11-18 10:55:57.014 CET >LOG: duration: 2212.984 ms plan:
Query Text: select distinct sc.CAPABILITY_NAME as "CAPABILITY" from SYSTEM_CAPABILITY sc join ROLE_CAPABILITY urc on urc.CAPABILITY=sc.CAPABILITY_NAME join ROLE_CAPABILITY grc on grc.CAPABILITY=urc.CAPABILITY join GROUP_USER gu on gu.ROLE=urc.ROLE_NAME join GROUP_ROLE gr on gr.ROLE_NAME=grc.ROLE_NAME where gu.USERNAME=$1 and (gu.EXPIRY_DATE is null or gu.EXPIRY_DATE>$2) and gr.ID=gu.GROUP_ID and gu.GROUP_ID=$3
HashAggregate (cost=78.40..78.44 rows=4 width=19)
Group Key: sc.capability_name
-> Nested Loop (cost=5.49..78.39 rows=4 width=19)
-> Nested Loop (cost=5.22..77.12 rows=4 width=38)
Join Filter: ((gu.role)::text = (urc.role_name)::text)
-> Nested Loop (cost=4.93..62.56 rows=30 width=36)
-> Nested Loop (cost=0.42..9.82 rows=1 width=85)
-> Index Only Scan using idx_gp_usr_gp_usrnm_rl_exp_dt on group_user gu (cost=0.42..8.46 rows=1 width=26)
Index Cond: ((group_id = ($3)::text) AND (username = ($1)::text))
Filter: ((expiry_date IS NULL) OR (expiry_date > $2))
-> Seq Scan on group_role gr (cost=0.00..1.35 rows=1 width=112)
Filter: ((id)::text = ($3)::text)
-> Bitmap Heap Scan on role_capability grc (cost=4.51..52.44 rows=30 width=33)
Recheck Cond: ((role_name)::text = (gr.role_name)::text)
-> Bitmap Index Scan on pk_role_capability (cost=0.00..4.51 rows=30 width=0)
Index Cond: ((role_name)::text = (gr.role_name)::text)
-> Index Scan using idx_role_capability_capability on role_capability urc (cost=0.28..0.42 rows=5 width=33)
Index Cond: ((capability)::text = (grc.capability)::text)
-> Index Only Scan using idx_system_capability_capability_name on system_capability sc (cost=0.28..0.31 rows=1 width=19)
Index Cond: (capability_name = (urc.capability)::text)
Запуск с моего локального компьютера, подключающегося к БД
< 2020-11-18 10:58:50.297 CET >LOG: duration: 16.070 ms plan:
Query Text:
select distinct sc.CAPABILITY_NAME as "CAPABILITY" from SYSTEM_CAPABILITY sc join ROLE_CAPABILITY urc on urc.CAPABILITY=sc.CAPABILITY_NAME join ROLE_CAPABILITY grc on grc.CAPABILITY=urc.CAPABILITY join GROUP_USER gu on gu.ROLE=urc.ROLE_NAME join GROUP_ROLE gr on gr.ROLE_NAME=grc.ROLE_NAME where gu.USERNAME='TEST123' and (gu.EXPIRY_DATE is null or gu.EXPIRY_DATE>'2020-11-17 00:00:00 01') and gr.ID=gu.GROUP_ID and gu.GROUP_ID='ABC123'
HashAggregate (cost=93.77..94.27 rows=50 width=19) (actual time=15.997..16.021 rows=200 loops=1)
Group Key: sc.capability_name
-> Hash Join (cost=16.86..93.65 rows=50 width=19) (actual time=0.325..15.295 rows=2329 loops=1)
Hash Cond: ((urc.role_name)::text = (gu.role)::text)
-> Nested Loop (cost=5.07..78.20 rows=169 width=77) (actual time=0.139..14.106 rows=5861 loops=1)
Join Filter: ((sc.capability_name)::text = (urc.capability)::text)
-> Nested Loop (cost=4.79..63.64 rows=30 width=82) (actual time=0.132..5.677 rows=1076 loops=1)
-> Nested Loop (cost=4.51..54.09 rows=30 width=63) (actual time=0.124..0.447 rows=1076 loops=1)
-> Seq Scan on group_role gr (cost=0.00..1.35 rows=1 width=112) (actual time=0.015..0.018 rows=1 loops=1)
Filter: ((id)::text = 'ABC123'::text)
Rows Removed by Filter: 27
-> Bitmap Heap Scan on role_capability grc (cost=4.51..52.44 rows=30 width=33) (actual time=0.107..0.302 rows=1076 loops=1)
Recheck Cond: ((role_name)::text = (gr.role_name)::text)
Heap Blocks: exact=37
-> Bitmap Index Scan on pk_role_capability (cost=0.00..4.51 rows=30 width=0) (actual time=0.098..0.098 rows=1076 loops=1)
Index Cond: ((role_name)::text = (gr.role_name)::text)
-> Index Only Scan using idx_system_capability_capability_name on system_capability sc (cost=0.28..0.31 rows=1 width=19) (actual time=0.004..0.005 rows=1 loops=1076)
Index Cond: (capability_name = (grc.capability)::text)
Heap Fetches: 1076
-> Index Scan using idx_role_capability_capability on role_capability urc (cost=0.28..0.42 rows=5 width=33) (actual time=0.005..0.007 rows=5 loops=1076)
Index Cond: ((capability)::text = (grc.capability)::text)
-> Hash (cost=11.62..11.62 rows=13 width=26) (actual time=0.179..0.179 rows=279 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 24kB
-> Index Only Scan using idx_gp_usr_gp_usrnm_rl_exp_dt on group_user gu (cost=0.42..11.62 rows=13 width=26) (actual time=0.023..0.110 rows=279 loops=1)
Index Cond: ((group_id = 'ABC123'::text) AND (username = 'TEST123'::text))
Filter: ((expiry_date IS NULL) OR (expiry_date > '2020-11-16 23:00:00 00'::timestamp with time zone))
Heap Fetches: 4
Я использую postgres версии 9.5.
Любая помощь очень ценится.
Комментарии:
1. Попытайтесь собрать ОБЪЯСНЕНИЕ (ПРОАНАЛИЗИРОВАТЬ), а не просто ОБЪЯСНИТЬ, по каждому из них. Обратите внимание, что auto_explain.log_analyze может быть очень дорогостоящим на старом оборудовании или ядрах, поэтому вы можете не захотеть оставлять его установленным постоянно. Но это также очень полезно.
2. @jjanes Я не знал, что эта функция существует, попробую и посмотрю, что я раскопаю. Спасибо