I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int which is partitioned by month (e.g.: observations_201903). There are approximately 150k sensors, providing data every 5 minutes. There is often no data, so we have models for each sensor and day of week which will fill in data for every hour of day for that model: sensor_id text, isodow int, model_id int. I created a view model_view that joins models to a table of model_values which contains model_id int, trange timerange, model_val int.

I created the following view of gap-filled data:


SELECT master_lookup.sensor_id, obs_ts, COALESCE(sensor_val, model_val) as sensor_val

FROM (SELECT sensor_id, 
             isodow, (dt + INTERVAL '1 day' * (isodow -1) + ref_time::TIME)::TIMESTAMP as obs_ts, ref_time::TIME
      FROM generate_series('2012-01-01 00:00'::timestamp, '2012-01-01 23:55'::timestamp, INTERVAL '5 minutes') as ref_time
      CROSS JOIN generate_series(1,7) as isodow
      CROSS JOIN generate_series('2011-12-26'::DATE, '2019-12-31'::DATE, INTERVAL '1 week') as dt
      CROSS JOIN sensors
      ) as master_lookup
LEFT OUTER JOIN observations USING (sensor_id, obs_ts)
LEFT OUTER JOIN model_view  ON model_view.sensor_id = master_lookup.sensor_id
                        AND master_lookup.ref_time <@ model_view.trange
                        AND model_view.isodow = master_lookup.isodow

If I query based on sensor_id and obs_ts it returns a result in 500 ms.

SELECT sensor_val FROM obs_filled
WHERE sensor_id = '29492817F' AND obs_ts = '2019-03-12 09:00:00'
Successfully run. Total query runtime: 512 msec.

But if I try to get values for every sensor at a single time-point, the query explodes. How could I make this view better? Should I rethink my approach?

EXPLAIN SELECT sensor_val FROM obs_filled
WHERE obs_ts = '2019-03-12 09:00:00'

'Merge Left Join  (cost=231638890234.76..609842717569.01 rows=604900000000 width=4)'
'  Merge Cond: (sensors.sensor_id = observations.sensor_id)'
'  Join Filter: ((((dt.dt + ('1 day'::interval * ((isodow.isodow - 1))::double precision)) + ((ref_time.ref_time)::time without time zone)::interval))::timestamp without time zone = observations.obs_ts)'
'  ->  Merge Left Join  (cost=231638864458.32..606294054292.57 rows=604900000000 width=34)'
'        Merge Cond: ((sensors.sensor_id = models.sensor_id) AND (isodow.isodow = models.isodow))'
'        Join Filter: ((ref_time.ref_time)::time without time zone <@ model_values.trange)'
'        ->  Sort  (cost=230237690211.37..231749940211.37 rows=604900000000 width=30)'
'              Sort Key: sensors.sensor_id, isodow.isodow'
'              ->  Nested Loop  (cost=0.01..10556158855.26 rows=604900000000 width=30)'
'                    ->  Nested Loop  (cost=0.01..39905520.01 rows=5000000 width=20)'
'                          Join Filter: ((((dt.dt + ('1 day'::interval * ((isodow.isodow - 1))::double precision)) + ((ref_time.ref_time)::time without time zone)::interval))::timestamp without time zone = '2019-03-12 09:00:00'::timestamp without time zone)'
'                          ->  Function Scan on generate_series dt  (cost=0.01..10.01 rows=1000 width=8)'
'                          ->  Materialize  (cost=0.01..29893.01 rows=1000000 width=12)'
'                                ->  Nested Loop  (cost=0.01..20010.01 rows=1000000 width=12)'
'                                      ->  Function Scan on generate_series ref_time  (cost=0.00..10.00 rows=1000 width=8)'
'                                      ->  Function Scan on generate_series isodow  (cost=0.00..10.00 rows=1000 width=4)'
'                    ->  Materialize  (cost=0.00..4228.70 rows=120980 width=10)'
'                          ->  Seq Scan on sensors  (cost=0.00..3032.80 rows=120980 width=10)'
'        ->  Materialize  (cost=1401174246.96..1422383782.89 rows=4241907186 width=41)'
'              ->  Sort  (cost=1401174246.96..1411779014.92 rows=4241907186 width=41)'
'                    Sort Key: models.sensor_id, models.isodow'
'                    ->  Hash Join  (cost=52266.24..70407680.95 rows=4241907186 width=41)'
'                          Hash Cond: (models.model_id = model_values.model_id)'
'                          ->  Seq Scan on models  (cost=0.00..732204.28 rows=44729928 width=19)'
'                          ->  Hash  (cost=24720.44..24720.44 rows=1424544 width=30)'
'                                ->  Seq Scan on model_values  (cost=0.00..24720.44 rows=1424544 width=30)'
'  ->  Sort  (cost=13276.44..13303.59 rows=10861 width=22)'
'        Sort Key: observations.sensor_id'
'        ->  Append  (cost=0.00..12548.38 rows=10861 width=22)'
'              ->  Seq Scan on ta  (cost=0.00..0.00 rows=1 width=44)'
'                    Filter: (obs_ts = '2019-03-12 09:00:00'::timestamp without time zone)'
'              ->  Index Scan using observations_201903_obs_ts_idx on observations_201903  (cost=0.56..12548.38 rows=10860 width=22)'
'                    Index Cond: (obs_ts = '2019-03-12 09:00:00'::timestamp without time zone)'

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.