Goal: find the time of the earliest(first) record, and the most recent(last) record matching some criteria in a performant way.
Background: We have ~4000 devices with ~30 ports each where we collect the daily in/egress traffic stats and store them in the daily_if_traffic measurement. Retention on this table is 300 days, so max series would be 120.000 series with 300 datapoints each. Currently it holds half (~18M records) of the 36M records which would be the max.
The need is to find the time of the first data for a given device and any port, and for each port the last time last they had any traffic.
Current queries:
select first("OutUcPkts") as "FOutUcPkts" FROM "daily_if_traffic" WHERE "hostname" =~ /{host}/
and
select last("InUcPkts") as "LInUcPkts" FROM "daily_if_traffic" WHERE "hostname" =~ /{host}/ and ("InUcPkts" > 0 or "OutUcPkts" > 0) GROUP BY "port"'
My issue is that each of these queries will take between 4-14 seconds (14 seconds is when data isn’t cached - subsequent queries are ~4 seconds). And I think both queries will do a full table scan, and if its possible to execute both queries in a single query or otherwise optimise the execution I’d really love to hear about it.
