Dashboard widget "Problem by severity" non optimal query

XMLWordPrintable
• Problem report
• Resolution: Won't fix
• Trivial
• None
• 4.0.22
• None

Query for that widget:

```SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity
FROM problem p,functions f,items i,hosts_groups hg
WHERE
p.source='0' AND p.object='0'
AND NOT EXISTS (
SELECT NULL FROM functions f,items i,hosts_groups hgg
LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (11,13,990)
WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid
GROUP BY i.hostid
HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid
AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid=1491
AND NOT EXISTS (
SELECT NULL FROM event_suppress es
WHERE es.eventid=p.eventid)
AND (p.r_eventid IS NULL OR p.r_clock>1594396440)
ORDER BY p.eventid DESC
LIMIT 1001;
```

EXPLAIN ANALIZE result

```mandatory | optional
-----------+----------
4000000 |  4000006
(1 row)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=22750295.90..22750313.42 rows=1001 width=54) (actual time=120315.121..120315.240 rows=212 loops=1)
->  Unique  (cost=22750295.90..22750388.25 rows=5277 width=54) (actual time=120315.120..120315.225 rows=212 loops=1)
->  Sort  (cost=22750295.90..22750309.10 rows=5277 width=54) (actual time=120315.119..120315.148 rows=425 loops=1)
Sort Key: p.eventid, p.objectid, p.clock, p.ns, p.name, p.severity
Sort Method: quicksort  Memory: 83kB
->  Nested Loop Anti Join  (cost=24906.20..22749969.64 rows=5277 width=54) (actual time=3881.222..120313.235 rows=425 loops=1)
->  Hash Join  (cost=24905.78..22747423.27 rows=5712 width=54) (actual time=3881.176..120308.987 rows=425 loops=1)
Hash Cond: (p.objectid = f.triggerid)
->  Seq Scan on problem p  (cost=0.00..22718453.29 rows=534278 width=54) (actual time=1.012..119942.861 rows=1084013 loops=1)
Filter: (((r_eventid IS NULL) OR (r_clock > 1594396440)) AND (source = 0) AND (object = 0) AND (NOT (SubPlan 1)))
Rows Removed by Filter: 97
SubPlan 1
->  HashAggregate  (cost=21.02..21.14 rows=8 width=12) (actual time=0.110..0.110 rows=0 loops=1084013)
Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
Rows Removed by Filter: 1
->  Nested Loop Left Join  (cost=1.56..20.94 rows=8 width=12) (actual time=0.009..0.106 rows=18 loops=1084013)
->  Nested Loop  (cost=1.27..16.09 rows=8 width=16) (actual time=0.006..0.020 rows=18 loops=1084013)
->  Nested Loop  (cost=0.85..15.01 rows=2 width=8) (actual time=0.005..0.011 rows=3 loops=1084013)
->  Index Scan using functions_1 on functions f_1  (cost=0.43..6.10 rows=2 width=8) (actual time=0.002..0.002 rows=3 loops=1084013)
Index Cond: (p.objectid = triggerid)
->  Index Scan using items_pkey on items i_1  (cost=0.43..4.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3114565)
Index Cond: (itemid = f_1.itemid)
->  Index Only Scan using hosts_groups_1 on hosts_groups hgg  (cost=0.41..0.50 rows=4 width=16) (actual time=0.001..0.002 rows=6 loops=3114565)
Index Cond: (hostid = i_1.hostid)
Heap Fetches: 40993
->  Index Scan using rights_2 on rights r  (cost=0.29..0.60 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=19846514)
Index Cond: (id = hgg.groupid)
Filter: (groupid = ANY ('{11,13,990}'::bigint[]))
Rows Removed by Filter: 12
->  Hash  (cost=24773.70..24773.70 rows=10566 width=8) (actual time=96.521..96.521 rows=17729 loops=1)
Buckets: 2048  Batches: 1  Memory Usage: 693kB
->  Nested Loop  (cost=1.27..24773.70 rows=10566 width=8) (actual time=0.241..92.784 rows=17729 loops=1)
->  Nested Loop  (cost=0.84..20072.31 rows=8936 width=8) (actual time=0.176..53.113 rows=14246 loops=1)
->  Index Scan using hosts_groups_2 on hosts_groups hg  (cost=0.29..111.17 rows=64 width=8) (actual time=0.057..0.138 rows=63 loops=1)
Index Cond: (groupid = 1491)
->  Index Scan using items_1 on items i  (cost=0.55..310.24 rows=165 width=16) (actual time=0.019..0.812 rows=226 loops=63)
Index Cond: (hostid = hg.hostid)
->  Index Scan using functions_2 on functions f  (cost=0.43..0.51 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=14246)
Index Cond: (itemid = i.itemid)
->  Index Only Scan using event_suppress_1 on event_suppress es  (cost=0.42..0.47 rows=2 width=8) (actual time=0.008..0.008 rows=0 loops=425)
Index Cond: (eventid = p.eventid)
Heap Fetches: 0
Total runtime: 120315.893 ms
(43 rows)
```

If graphical view https://explain.depesz.com/s/DAKc

1. query_cost.png
98 kB
2. zbx-18080-4.0.26.patch
10 kB
3. zbx-18080-5.0.4.patch
10 kB