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
  • @a_horse_with_no_name done! Thanks for your help! – Mariano Roberto Medina Sep 21 '18 at 12:23
  • Sorry, @ a_horse_with_no_name , I was using a short period of time. I edited the query to analize: now it takes those 108 seconds to run. After running that execution plan with analize and buffer (they actually executed the query to take the statistics), if I run it again it takes less than 3 seconds. I guess the cache system of Postgres is working fine at least. – Mariano Roberto Medina Sep 21 '18 at 13:14
  • You are obviously limited by the performance of your I/O system. The Index scan had to read 1.9GB and needed nearly 2 Minutes for that. That's a throughput of 16MB per Second if I'm not mistaken - that's horribly slow. Most probably that throughput is limited by your Heroku configuration. – a_horse_with_no_name Sep 21 '18 at 13:17
  • @a_horse_with_no_name I understand. Do you think there is another improvement is possible to do? Of course I will dig more into I/O limitations on Heroku. I hope I can change that. – Mariano Roberto Medina Sep 21 '18 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.