Hej,
mam problem z performancem na bazie danych.
Mam zapytanie do bazy:
explain SELECT *
FROM app.facility f
WHERE 1 = 1
AND customer_id = 3
AND EXISTS(SELECT DISTINCT ac.user_id
FROM acl.user_access_tab ac
WHERE ac.user_id = 231
AND ac.role_name <> 'SERVICE'
AND ((ac.customer_id = f.customer_id AND ac.operator_id IS NULL AND ac.facility_id IS NULL) OR
(ac.customer_id = f.customer_id AND ac.operator_id = f.operator_id AND ac.facility_id IS NULL) OR
(ac.customer_id = f.customer_id AND ac.operator_id = f.operator_id AND
ac.facility_id = f.facility_id)));
Zapytanie jako tako wydaje mi sie ze nie jest złożone.
Ale co sie dzieje to na localoscie wykonuje sie 1 sek, na prodzie 21 sek (bazy mniej wiecej podobnej wielkosci),
Lokalnie mam pg 12.8, na prod 12.6
I jeszcze explain z locala:
Nested Loop Semi Join (cost=0.86..31.76 rows=1 width=722)
Join Filter: (((ac.operator_id IS NULL) AND (ac.facility_id IS NULL)) OR ((ac.operator_id = f.operator_id) AND (ac.facility_id IS NULL)) OR ((ac.operator_id = f.operator_id) AND (ac.facility_id = f.facility_id)))
-> Nested Loop (cost=0.57..19.14 rows=1 width=690)
Join Filter: (f.operator_id = o.operator_id)
-> Nested Loop Left Join (cost=0.57..17.65 rows=1 width=512)
-> Nested Loop (cost=0.29..9.34 rows=1 width=499)
-> Index Scan using facility_tab_customer_id_index on facility_tab f (cost=0.29..8.30 rows=1 width=321)
Index Cond: (customer_id = 3)
-> Seq Scan on customer_tab c (cost=0.00..1.02 rows=1 width=182)
Filter: (customer_id = 3)
-> Index Scan using facility_data_tab_facility_id_uindex on facility_data_tab fd (cost=0.29..8.30 rows=1 width=17)
Index Cond: (facility_id = f.facility_id)
-> Seq Scan on operator_tab o (cost=0.00..1.22 rows=22 width=182)
-> Index Scan using user_access_tab_customer_id_index on user_access_tab ac (cost=0.29..4.31 rows=1 width=12)
Index Cond: (customer_id = 3)
Filter: (((role_name)::text <> 'SERVICE'::text) AND (user_id = 231))
SubPlan 1
-> Index Scan using facility_report_tab_facility_id_index on facility_report_tab fr (cost=0.28..8.29 rows=1 width=13)
Index Cond: (facility_id = f.facility_id)
Z proda
Nested Loop Left Join (cost=22.16..161971.70 rows=496 width=690)
-> Nested Loop (cost=21.87..155673.71 rows=496 width=645)
Join Filter: (f.operator_id = o.operator_id)
-> Seq Scan on operator_tab o (cost=0.00..1.01 rows=1 width=182)
-> Nested Loop (cost=21.87..155666.50 rows=496 width=467)
-> Seq Scan on customer_tab c (cost=0.00..1.01 rows=1 width=182)
Filter: (customer_id = 3)
-> Nested Loop Semi Join (cost=21.87..155660.53 rows=496 width=289)
Join Filter: (((ac.operator_id IS NULL) AND (ac.facility_id IS NULL)) OR ((ac.operator_id = f.operator_id) AND (ac.facility_id IS NULL)) OR ((ac.operator_id = f.operator_id) AND (ac.facility_id = f.facility_id)))
-> Seq Scan on facility_tab f (cost=0.00..1090.30 rows=16978 width=289)
Filter: (customer_id = 3)
-> Materialize (cost=21.87..237.26 rows=461 width=12)
-> Bitmap Heap Scan on user_access_tab ac (cost=21.87..234.95 rows=461 width=12)
Recheck Cond: (user_id = 231)
Filter: (((role_name)::text <> 'SERVICE'::text) AND (customer_id = 3))
-> Bitmap Index Scan on user_access_tab_user_id_index (cost=0.00..21.75 rows=462 width=0)
Index Cond: (user_id = 231)
-> Index Scan using facility_data_tab_facility_id_uindex on facility_data_tab fd (cost=0.29..0.40 rows=1 width=17)
Index Cond: (facility_id = f.facility_id)
SubPlan 1
-> Index Scan using facility_report_tab_facility_id_index on facility_report_tab fr (cost=0.28..12.30 rows=1 width=12)
Index Cond: (facility_id = f.facility_id)
Powiem szczerze ze nie jestem specjalista w analizie explain aczkolwiek na pierwszy rzut oka widac ze jakos inaczej lokalnie i na prodzie on sobie ogarnia te zapytania.
dzieki za pomoc.