Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  2. ZBX-18080

Dashboard widget "Problem by severity" non optimal query



    • Problem report
    • Status: Closed
    • Trivial
    • Resolution: Won't fix
    • 4.0.22
    • None
    • Frontend (F)
    • None
    • Team D


      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 
                  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

        Issue Links



              alexei Alexei Vladishev
              neogan Andrei Gushchin
              1 Vote for this issue
              13 Start watching this issue