This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc) from devices in factories and warehouses. Each row has:
- a timestamp value, which is a BIGINT
- a deviceid, which is a varchar (I know, this is not good)
- a metricid, which is a varchar too
- a recvalue, which is a float (this is what we want)
Other fields not important, that need to be removed.
The table is so big that performing a query with dates in a range of one month, retrieves more than 900000 records in the result set.
select recvalue from devicesmetrics where deviceid = 'adpackca2_t_axm' and metricid = 'EP_TOTAL_kWh' and timestamp between 1532571651000 and 1537509599000;
Of course that takes a lot! Several minutes. Even though we already created an index like this:
create index dm_deviceid_metricid_timestamp on devicesmetrics (deviceid, metricid, timestamp)
And also, the explain plan says that the query is using it.
We expected that. The solution we found was to retrieve averages of recvalue, retrieving only a certain number of points over the time. So, for example, for a month, we decided to use a query like this:
select max("timestamp") as time_metric, metricid, deviceid, avg(cast(recvalue as float)), avg(cast(calcvalue as float))
from devicesmetrics where deviceid = 'adpackca2_t_axm'
and metricid = 'EP_TOTAL_kWh'
and "timestamp" between 1532571651000 and 1537509599000
group by deviceid, metricid, "timestamp"/((1537509599000-1532571651000)/1000)
This is still taking a lot, unfortunately, around 2 minutes. Even though the number of records retrieve is lower than 500.
Suggestions on how to get a better solutions for this?
It is a standard-4 Heroku-PostgreSQL instance (~30GB RAM and 500 connections). As another detail, I can show how the explain (analize, buffers) looks like, for that last query:
GroupAggregate (cost=75179.55..75561.41 rows=38182 width=62) (actual time=108265.296..108445.322 rows=802 loops=1) Group Key: deviceid, metricid, (("timestamp" / '4937948'::bigint)) Buffers: shared hit=8765 read=228254 I/O Timings: read=107118.279 -> Sort (cost=75179.55..75198.64 rows=38189 width=58) (actual time=108265.172..108277.890 rows=237853 loops=1) Sort Key: (("timestamp" / '4937948'::bigint)) Sort Method: quicksort Memory: 39593kB Buffers: shared hit=8765 read=228254 I/O Timings: read=107118.279 -> Index Scan using dm_deviceid_metricid_timestamp on devicesmetrics (cost=0.14..74598.28 rows=38189 width=58) (actual time=2.677..108082.026 rows=237853 loops=1) Index Cond: (((deviceid)::text = 'adpackca2_t_axm'::text) AND ((metricid)::text = 'EP_TOTAL_kWh'::text) AND ("timestamp" >= '1532571651000'::bigint) AND ("timestamp" <= '1537509599000'::bigint)) Buffers: shared hit=8765 read=228254 I/O Timings: read=107118.279 Planning time: 0.220 ms Execution time: 108448.051 ms