Re: Execution plan does not use index

> 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)