Details

    • Team:
      Team B
    • Sprint:
      Sprint 53 (Jun 2019), Sprint 54 (Jul 2019)
    • Story Points:
      1.5

      Description

      Today we learned that Zabbix generates a particular query that is non-optimal for TimescaleDB.

      This is because TimescaleDB does not do chunk exclusion when the constraint involves a subquery. To be performant TimescaleDB needs to scan only a subset of chunks. Please see the query below:

      SELECT * FROM history_uint h WHERE h.itemid=$1 AND h.clock=(SELECT MAX(h2.clock) FROM history_uint h2 WHERE h2.itemid=$1 AND h2.clock>$2) ORDER BY h.ns DESC LIMIT 1 

      In this user's query, there is the constraint h.clock=(SELECT ...). The problem here is that chunk pruning that would speed up this query can't happen because the constraint involves a subquery. Thus the user needs to explicitly add an extra time constraint, h2.clock>$2, to the top query.
       
      So, while the users are able to go in and tweak this query, we imagine it would be a better  experience if this was enabled by default on the generated query. This would result in the best performance out-of-the-box and you will reduce friction from any manual configuration required.
       
       

        Attachments

          Activity

            People

            • Assignee:
              averza Andrejs Verza
              Reporter:
              palivoda Rostislav Palivoda
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: