Return to Question

The index that the query normally uses is xxx_device__707d9e_idx:

=> \di+ xxx_device__707d9e_idx
                                                             List of relations
 Schema |          Name          | Type  | Owner  |      Table      | Persistence | Access method |  Size  | Description
--------+------------------------+-------+--------+-----------------+-------------+---------------+--------+-------------
 public | xxx_device__707d9e_idx | index | xxx    | xxx_device_data | permanent   | btree         | 286 MB |
(1 row)

I have a peculiar situation where PostgreSQL is choosing to materialize rather than use an index for a query where I join a generate_series() against a table storing streamed IOT data with timestamps:

I have a peculiar situation where PostgreSQL is choosing to materialize rather than use a particular index I've created for a query where I join a generate_series() against a table storing streamed IOT data with timestamps:

=> set enable_material = 'off';
SET

=> explain (analyze, buffers, verbose)
    SELECT series.timestamptz,
           coalesce(sum((data->'count')::int/2), 0) as total

    FROM xxx_device_data d
    RIGHT JOIN generate_series(
        '2023-01-19'::date::timestamp at time zone 'Australia/Sydney',
        '2023-04-19'::date::timestamp at time zone 'Australia/Sydney' + '23 hours'::interval,
        '1 hour'::interval
    ) series ON
        series.timestamptz <= d.timestamp
        AND d.timestamp < (series.timestamptz + '1 hour'::interval)
        AND d.device_id in (298)

    GROUP BY series.timestamptz;
                                                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=411702.29..411704.29 rows=200 width=16) (actual time=15.220..16.408 rows=2185 loops=1)
   Output: series.series, COALESCE(sum((((d.data -> 'count'::text))::integer / 2)), '0'::bigint)
   Group Key: series.series
   Batches: 1  Memory Usage: 385kB
   Buffers: shared hit=11712 dirtied=1
   ->  Nested Loop Left Join  (cost=0.44..401242.56 rows=836778 width=35) (actual time=0.143..11.117 rows=6915 loops=1)
         Output: series.series, d.data
         Buffers: shared hit=11712 dirtied=1
         ->  Function Scan on pg_catalog.generate_series series  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.122..0.309 rows=2185 loops=1)
               Output: series.series
               Function Call: generate_series('2023-01-18 13:00:00+00'::timestamp with time zone, ('2023-04-18 14:00:00+00'::timestamp with time zone + '23:00:00'::interval), '01:00:00'::interval)
         ->  Index Scan using master_port_device__707d9e_idx on public.xxx_device_data d  (cost=0.44..392.86 rows=837 width=35) (actual time=0.002..0.004 rows=2 loops=2185)
               Output: d.data, d."timestamp"
               Index Cond: ((d.device_id = 298) AND (d."timestamp" >= series.series) AND (d."timestamp" < (series.series + '01:00:00'::interval)))
               Buffers: shared hit=11712 dirtied=1
 Query Identifier: 2169495744770241309
 Planning Time: 0.138 ms
 Execution Time: 17.096 ms
(18 rows)

=> set enable_material = 'off';
SET

=> explain (analyze, buffers, verbose)
    SELECT series.timestamptz,
           coalesce(sum((data->'count')::int/2), 0) as total

    FROM xxx_device_data d
    RIGHT JOIN generate_series(
        '2023-01-19'::date::timestamp at time zone 'Australia/Sydney',
        '2023-04-19'::date::timestamp at time zone 'Australia/Sydney' + '23 hours'::interval,
        '1 hour'::interval
    ) series ON
        series.timestamptz <= d.timestamp
        AND d.timestamp < (series.timestamptz + '1 hour'::interval)
        AND d.device_id in (298)

    GROUP BY series.timestamptz;
                                                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=411702.29..411704.29 rows=200 width=16) (actual time=15.220..16.408 rows=2185 loops=1)
   Output: series.series, COALESCE(sum((((d.data -> 'count'::text))::integer / 2)), '0'::bigint)
   Group Key: series.series
   Batches: 1  Memory Usage: 385kB
   Buffers: shared hit=11712 dirtied=1
   ->  Nested Loop Left Join  (cost=0.44..401242.56 rows=836778 width=35) (actual time=0.143..11.117 rows=6915 loops=1)
         Output: series.series, d.data
         Buffers: shared hit=11712 dirtied=1
         ->  Function Scan on pg_catalog.generate_series series  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.122..0.309 rows=2185 loops=1)
               Output: series.series
               Function Call: generate_series('2023-01-18 13:00:00+00'::timestamp with time zone, ('2023-04-18 14:00:00+00'::timestamp with time zone + '23:00:00'::interval), '01:00:00'::interval)
         ->  Index Scan using xxx_device__707d9e_idx on public.xxx_device_data d  (cost=0.44..392.86 rows=837 width=35) (actual time=0.002..0.004 rows=2 loops=2185)
               Output: d.data, d."timestamp"
               Index Cond: ((d.device_id = 298) AND (d."timestamp" >= series.series) AND (d."timestamp" < (series.series + '01:00:00'::interval)))
               Buffers: shared hit=11712 dirtied=1
 Query Identifier: 2169495744770241309
 Planning Time: 0.138 ms
 Execution Time: 17.096 ms
(18 rows)