> Curious, how accurate is that row count of 1.2 million records for 3 days?
Not to bad actually
select count(mv_inner.*)
from measurement_value AS mv_inner
where mv_inner.timestamp > '2020-11-06'::timestamp - interval '1 day' and
mv_inner.timestamp < '2020-11-07'::timestamp + interval '1 day'
offset 0
==> 1128736
> How many total records in the table?
±168 million
> If you disable sequential scan, does it choose the index and what cost does
> it show?
It chooses the index, but apparently to create some intermediate structure that
then later still needs to be joined on the device_id. Probably requires
scanning all pages of the index, which might explain why the performance is
still not ok
set enable_seqscan to false;
explain
select d.short_id,mv.timestamp,mv.I64_01
from
device d
, device_configuration dc
, (
select mv_inner.*
from measurement_value AS mv_inner
where mv_inner.timestamp > '2020-11-06'::timestamp - interval '1 day'
and mv_inner.timestamp < '2020-11-07'::timestamp + interval '1 day'
offset 0
) mv
, pg_timezone_names tzn
where mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and
dc.timezone=tzn.name and
mv.timestamp > '2020-11-06'::timestamp - tzn.utc_offset and
mv.timestamp < '2020-11-07'::timestamp - tzn.utc_offset
==>
Hash Join (cost=6677594.18..9545649.57 rows=434126 width=20)
Hash Cond: (mv_inner.device_id = d.short_id)
Join Filter: ((mv_inner."timestamp" > ('2020-11-06 00:00:00'::timestamp
without time zone - pg_timezone_names.utc_offset)) AND (mv_inner."timestamp" <
('2020-11-07 00:00:00'::timestamp without time zone -
pg_timezone_names.utc_offset)))
-> Bitmap Heap Scan on measurement_value mv_inner
(cost=6676540.29..9446603.90 rows=1220458 width=1006)
Recheck Cond: (("timestamp" > '2020-11-05 00:00:00'::timestamp without
time zone) AND ("timestamp" < '2020-11-08 00:00:00'::timestamp without time
zone))
-> Bitmap Index Scan on measurement_values_pkey
(cost=0.00..6676235.18 rows=1220458 width=0)
Index Cond: (("timestamp" > '2020-11-05 00:00:00'::timestamp
without time zone) AND ("timestamp" < '2020-11-08 00:00:00'::timestamp without
time zone))
-> Hash (cost=1026.55..1026.55 rows=2187 width=20)
-> Hash Join (cost=471.95..1026.55 rows=2187 width=20)
Hash Cond: (dc.timezone = pg_timezone_names.name)
-> Hash Join (cost=449.45..903.76 rows=615 width=18)
Hash Cond: (dc.device_id = d.id)
-> Bitmap Heap Scan on device_configuration dc
(cost=242.72..688.58 rows=615 width=30)
Filter: latest
-> Bitmap Index Scan on
device_configuration_device_latest_idx (cost=0.00..242.57 rows=615 width=0)
Index Cond: (latest = true)
-> Hash (cost=198.19..198.19 rows=683 width=20)
-> Index Scan using device_short_id_key on device d
(cost=0.28..198.19 rows=683 width=20)
-> Hash (cost=10.00..10.00 rows=1000 width=48)
-> Function Scan on pg_timezone_names (cost=0.00..10.00
rows=1000 width=48)
