I have the problem of very slow queries, probably because of the database schema.
It’s about an IoT platform with many devices that send consumption values.
At the moment I store an unique identifier as tag and the consumption values in a field.
When I now want to query for the last 7 days over ALL devices the query will look like this:
SELECT id, "values" from consumptionValues WHERE time > now() - 7d AND device = '63368c6440c885002887631d' AND device = '63368c6440c885002887630d' AND device = '63368c6440c885002887632d' AND device = '63368c6440c885002887634d' AND device = '63368c6440c885002887633d' AND device = '63368c6440c885002887638d' AND device = '63368c6440c885002887635d' AND device = '63368c6440c885002887639d' AND device = '63368c6440c885002887636d' AND device = '63368c6440c885002887630d' AND device = '63368c6440c885002887637d' [......]
It takes several minutes with ~4000 devices.
The problem is that there is a complex hierarchical permission set behind, so I cannot group the devices to build a shorter query.
Any advice how to improve this?