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
±168 million
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) |
