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.