-
Type:
Incident report
-
Resolution: Commercial support required
-
Priority:
Critical
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Environment:Production
Hi,
We've been facing a lot of performance issues, specially related to this query:
SELECT COUNT(DISTINCT t.triggerid) AS cnt,
t.status,
t.value
FROM triggers t
WHERE NOT EXISTS(SELECT f.functionid
FROM functions f JOIN items i ON f.itemid=i.itemid
JOIN hosts h ON i.hostid=h.hostid
WHERE f.triggeid=t.triggerid AND (i.status<>0 OR h.status<>0))
AND t.flags IN (0,4)
GROUP BY t.status,t.value;
We have two environments, one that used to be the stand and the productive one. However, the explain plan are different and the execution time is way better in our QA environment , both databases have the same size. Is there any thing to do at statistics level or is there any way to improve or change the explain plan on production?
On Production .
GroupAggregate (cost=429103.20..438077.45 rows=4 width=16)
Group Key: t.status, t.value"
-> Sort (cost=429103.20..431346.75 rows=897421 width=16)
Sort Key: t.status, t.value
-> Hash Anti Join (cost=189277.53..325031.79 rows=897421 width=16)
Hash Cond: (t.triggerid = f.triggerid)
-> Seq Scan on triggers t (cost=0.00..119841.86 rows=947064 width=16)
Filter: (flags = ANY ('{0,4}'::integer[]))
-> Hash (cost=188577.56..188577.56 rows=55998 width=8)
-> Hash Join (cost=168704.83..188577.56 rows=55998 width=8)
Hash Cond: (i.hostid = h.hostid)
Join Filter: ((i.status <> 0) OR (h.status <> 0))
-> Hash Join (cost=153676.57..170917.92 rows=140340 width=20)
Hash Cond: (f.itemid = i.itemid)
-> Seq Scan on functions f (cost=0.00..2462.40 rows=140340 width=16)
-> Hash (cost=119364.14..119364.14 rows=1868914 width=20)
-> Seq Scan on items i (cost=0.00..119364.14 rows=1868914 width=20)
-> Hash (cost=15009.17..15009.17 rows=1528 width=12)
-> Index Scan using hosts_pkey on hosts h (cost=0.40..15009.17 rows=1528 width=12)
On QA:
"GroupAggregate (cost=189594.78..190825.65 rows=4 width=16)" " Group Key: t.status, t.value" " -> Sort (cost=189594.78..189902.49 rows=123083 width=16)" " Sort Key: t.status, t.value" " -> Hash Anti Join (cost=162927.01..179188.56 rows=123083 width=16)" " Hash Cond: (t.triggerid = f.triggerid)" " -> Seq Scan on triggers t (cost=0.00..10818.44 rows=172075 width=16)" " Filter: (flags = ANY ('{0,4}'::integer[]))" " -> Hash (cost=162253.26..162253.26 rows=53900 width=8)" " -> Hash Join (cost=143341.61..162253.26 rows=53900 width=8)" " Hash Cond: (i.hostid = h.hostid)" " Join Filter: ((i.status <> 0) OR (h.status <> 0))" " -> Hash Join (cost=119910.89..135154.24 rows=139745 width=20)" " Hash Cond: (f.itemid = i.itemid)" " -> Seq Scan on functions f (cost=0.00..2450.45 rows=139745 width=16)" " -> Hash (cost=92865.62..92865.62 rows=1473062 width=20)" " -> Seq Scan on items i (cost=0.00..92865.62 rows=1473062 width=20)" " -> Hash (cost=17419.76..17419.76 rows=480876 width=12)" " -> Seq Scan on hosts h (cost=0.00..17419.76 rows=480876 width=12)"
Thanks in advance
Daniel