Details

    • Type: Problem report
    • Status: Need info
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: 4.2.3
    • Fix Version/s: None
    • Component/s: Server (S)
    • Environment:
      Ubuntu 18.04
      PostgreSQL 11.4-1.pgdg18.04+1
      Zabbix 4.2.3

      Description

      Steps to reproduce:

      1. login to database
      2. run explain analyze query
      explain analyze select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.trapper_hosts,i.logtimefmt,i.params,i.state,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.flags,i.interfaceid,i.snmpv3_authprotocol,i.snmpv3_privprotocol,i.snmpv3_contextname,i.lastlogsize,i.mtime,i.history,i.trends,i.inventory_link,i.valuemapid,i.units,i.error,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,i.allow_traps,i.templateid,id.parent_itemid from items i inner join hosts h on i.hostid=h.hostid left join item_discovery id on i.itemid=id.itemid where h.status in (0,1) and i.flags<>2;

      Result:

      QUERY PLAN
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Hash Left Join (cost=22750.62..112925.16 rows=1338692 width=260) (actual time=1205.691..4043.411 rows=1655136 loops=1)
      Hash Cond: (i.itemid = id.itemid)
      -> Hash Join (cost=125.62..82377.02 rows=1338692 width=252) (actual time=3.567..1865.635 rows=1655136 loops=1)
      Hash Cond: (i.hostid = h.hostid)
      -> Seq Scan on items i (cost=0.00..77874.82 rows=1666907 width=252) (actual time=0.024..1322.627 rows=1668370 loops=1)
      Filter: (flags <> 2)
      Rows Removed by Filter: 180842
      -> Hash (cost=115.10..115.10 rows=3006 width=8) (actual time=3.474..3.474 rows=3006 loops=1)
      Buckets: 4096 Batches: 1 Memory Usage: 150kB
      -> Seq Scan on hosts h (cost=0.00..115.10 rows=3006 width=8) (actual time=0.025..2.710 rows=3006 loops=1)
      Filter: (status = ANY ('{0,1}'::integer[]))
      Rows Removed by Filter: 737
      -> Hash (cost=16680.20..16680.20 rows=1698515 width=16) (actual time=1190.917..1190.917 rows=1698539 loops=1)
      Buckets: 2097152 Batches: 1 Memory Usage: 96004kB
      -> Index Only Scan using item_discovery_1 on item_discovery id (cost=0.43..16680.20 rows=1698515 width=16) (actual time=0.094..753.578 rows=1698539 loops=1)
      Heap Fetches: 641940
      Planning Time: 6.035 ms
      Execution Time: 4099.055 ms
      (18 rows)

      Expected:
      query time ~ planning time

        Attachments

        1. cache.png
          cache.png
          200 kB
        2. getting.png
          getting.png
          301 kB
        3. hosts.txt
          6 kB
        4. internal.png
          internal.png
          305 kB
        5. items.txt
          8 kB
        6. perfomance.png
          perfomance.png
          231 kB
        7. pg_settings.txt
          2 kB
        8. pg_stat_all_tables.txt
          138 kB
        9. pg_stat_user_tables.txt
          76 kB
        10. pg_stat_user_tables.txt
          138 kB
        11. postgresql-zabbix-db.conf.txt
          2 kB
        12. zabbix_server.conf.txt
          0.6 kB

          Activity

            People

            • Assignee:
              edgar.akhmetshin Edgar Akhmetshin
              Reporter:
              hard Evgeny Molchanov
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: