Re: Execution plan does not use index

Triggered by Michael mentioning subqueries I ended up trying

explain

  select d.short_id,mv.timestamp ,mv.I64_01

  from device d, device_configuration dc, measurement_value mv

  where mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and 

        mv.timestamp > '2020-11-06'::timestamp - (select tzn.utc_offset from pg_timezone_names tzn where tzn.name=dc.timezone) and mv.timestamp < '2020-11-07'::timestamp - (select tzn.utc_offset from pg_timezone_names tzn where tzn.name=dc.timezone)

==>

Nested Loop  (cost=25.85..84540074.64 rows=16996885 width=20)

  ->  Seq Scan on device_configuration dc  (cost=0.00..470.01 rows=615 width=30)

        Filter: latest

  ->  Nested Loop  (cost=25.85..137027.83 rows=43494 width=36)

        ->  Index Scan using device_pkey on device d  (cost=0.28..7.23 rows=1 width=20)

              Index Cond: (id = dc.device_id)

        ->  Index Scan using measurement_values_pkey on measurement_value mv  (cost=25.58..136585.66 rows=43494 width=20)

              Index Cond: ((device_id = d.short_id) AND ("timestamp" > ('2020-11-06 00:00:00'::timestamp without time zone - (SubPlan 1))) AND ("timestamp" < ('2020-11-07 00:00:00'::timestamp without time zone - (SubPlan 2))))

              SubPlan 1

                ->  Function Scan on pg_timezone_names  (cost=0.00..12.50 rows=5 width=16)

                      Filter: (name = dc.timezone)

              SubPlan 2

                ->  Function Scan on pg_timezone_names pg_timezone_names_1  (cost=0.00..12.50 rows=5 width=16)

                      Filter: (name = dc.timezone)

Now returns the 320K in less than 5sec. 

I was till now convinced that correlated subqueries or joins are equivalent. I guess I was wrong :). Wonder how stable this plan will be though