PostgreSQL: Unwanted materialize node within nested loop join

Question

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:

Some points:

• The RDS instance was initially a t3.small (2GB)
• Initial tinkering found that if I set effective_cache_size to something large (4GB) then a sample query similar to the one below started using the index.
• RDS' default for effective_cache_size is 50%, increasing this to 75% caused some of the queries run to no longer materialize however some queries still continue to behave this way.
• All other parameters remain untouched.
• I upgraded the instance to t3.medium (4GB) and queries still materialize.
• Turning enable_material off causes an example query to use the index and run fine.
• A (de-identified) dump of the database loaded onto a local dev machine causes all queries to run fine (no materialization). This local dev instance shows effective_cache_size with 4GB.
• Manually setting effective_cache_size to something a lot larger (eg 10GB) still results in materialization.

Here are the version/params:

=> select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

=> show effective_cache_size;
 effective_cache_size
----------------------
 2819888kB
(1 row)

=> show shared_buffers;
 shared_buffers
----------------
 939960kB
(1 row)

=> show work_mem;
 work_mem
----------
 1GB
(1 row)

Here's what the IOT data table looks like (I've replaced the table name prefix containing clients name with xxx) (it's managed by Django):

=> \d xxx_device_data
                                      Table "public.xxx_device_data"
  Column   |           Type           | Collation | Nullable |                        Default
-----------+--------------------------+-----------+----------+-------------------------------------------------------
 id        | integer                  |           | not null | nextval('xxx_device_data_id_seq'::regclass)
 timestamp | timestamp with time zone |           | not null |
 data      | jsonb                    |           | not null |
 device_id | integer                  |           | not null |
Indexes:
    "xxx_device_data_pkey" PRIMARY KEY, btree (id)
    "xxx_device__707d9e_idx" btree (device_id, "timestamp")
    "xxx_device_data_device_id_9fa58fda" btree (device_id)
Foreign-key constraints:
    "xxx_device_device_id_9fa58fda_fk_xxx" FOREIGN KEY (device_id) REFERENCES xxx_device(id) DEFERRABLE INITIALLY DEFERRED

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)

This is the problem query:

=> 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=184043.58..184045.58 rows=200 width=16) (actual time=2766.411..2766.815 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=5162 dirtied=7
   ->  Nested Loop Left Join  (cost=86.81..173583.85 rows=836778 width=35) (actual time=14.444..2761.165 rows=6914 loops=1)
         Output: series.series, d.data
         Join Filter: ((series.series <= d."timestamp") AND (d."timestamp" < (series.series + '01:00:00'::interval)))
         Rows Removed by Join Filter: 11300027
         Buffers: shared hit=5162 dirtied=7
         ->  Function Scan on pg_catalog.generate_series series  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.179..0.534 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)
         ->  Materialize  (cost=86.80..22972.67 rows=7531 width=35) (actual time=0.001..0.346 rows=5174 loops=2185)
               Output: d.data, d."timestamp"
               Buffers: shared hit=5162 dirtied=7
               ->  Bitmap Heap Scan on public.xxx_device_data d  (cost=86.80..22935.02 rows=7531 width=35) (actual time=1.615..11.084 rows=5174 loops=1)
                     Output: d.data, d."timestamp"
                     Recheck Cond: (d.device_id = 298)
                     Heap Blocks: exact=5139
                     Buffers: shared hit=5162 dirtied=7
                     ->  Bitmap Index Scan on xxx_device_data_device_id_9fa58fda  (cost=0.00..84.92 rows=7531 width=0) (actual time=0.912..0.912 rows=5190 loops=1)
                           Index Cond: (d.device_id = 298)
                           Buffers: shared hit=7
 Query Identifier: 2169495744770241309
 Planning:
   Buffers: shared hit=157
 Planning Time: 0.797 ms
 Execution Time: 2767.168 ms
(29 rows)

Disabling materialization causes the index to be used:

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

I'm not sure what to look at next? Looking at similar questions here I read through costsize.c to see if there are any issues I've missed but couldn't see anything. Any postgres experts out there that may know what's happening here?

Also please let me know if there's any other context I should post.

Answer 1

The fundamental problem is that it doesn't know how wide the time ranges you dynamically construct are going to be. It has to use generic assumptions, and those are grossly wrong. Since it thinks that the time range condition is not very selective, it thinks there is little benefit in using the index with that as a 2nd column, and it would get more benefit by using the materialize over the single-column index scan instead.

But I don't know what your question is. You already know how to solve the problem by disabling materialize, and it isn't clear that any other work around is going to be superior to that one.

You could rewrite the query to get all the data you want from xxx_device_data in one go, and group it by the timestamp truncated to hours. If there were any hours with no data, they would not show up, so you get them back by right joining this already aggregated data to the generate_series. But if you need to provoke Django into writing such a query for you, I don't know how to do that.