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

Dashboard widget "Problem by severity" non optimal query



    • Type: Problem report
    • Status: Confirmed
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: 4.0.22
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Labels:
    • Team:
      Team D
    • Sprint:
      Sprint 66 (Jul 2020), Sprint 67 (Aug 2020), Sprint 68 (Sep 2020), Sprint 69 (Oct 2020), Sprint 70 (Nov 2020), Sprint 71 (Dec 2020), Sprint 72 (Jan 2021), Sprint 73 (Feb 2021), Sprint 74 (Mar 2021), Sprint 75 (Apr 2021), Sprint 76 (May 2021)


      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



              sasha Alexander Vladishev
              neogan Andrei Gushchin
              1 Vote for this issue
              12 Start watching this issue