Re: Execution plan does not use index

On Tue, Nov 10, 2020 at 1:25 AM Peter Coppens <peter(dot)coppens(at)datylon(dot)com>
wrote:

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

Curious, what is seq_page_cost and random_page_cost? Any idea of your cache
hits for indexes? If they are very high and/or you have SSD or similar fast
storage, then maybe random_page_cost should be 1.1-2 and not default 4
(assuming seq_page_cost is still 1). The planner will be more likely to use
an index scan if the expected cost for scanning an index (random) is closer
to a sequential read. Sorry if this explanation is completely superfluous
and you have already configured this.

It would be interesting to see explain (analyze buffers) output so we can
see the actual counts for these nodes. I'm rather surprised that the query
I provided didn't use the timestamp index unless the inclusion of 3 days
worth of range meant that it was estimated to be too high a fraction of the
table. If you just execute only the subquery, is the index used by chance?

Top posting (reply with all previous copied below) is discouraged on these
lists. I think because it makes the archives rather messy.