Thread

1. Any way to get nested loop index joins on CTEs?

   Clemens Eisserer <linuxhippy@gmail.com> — 2025-07-15T13:29:27Z

   Hi,
   
   I am using generate_series + a join to group time-series data into
   buckets, which works well as long as I do this only for one
   aggregation hierarchy: The index on the timestamp of the table with
   the actual time-series data is used for a nested loop index join.
   
   However with more aggregation levels (one aggregation stage consuming
   the output of the previous one) chained together using CTEs, there is
   no index available and postgresql falls back to (no-index) nested loop
   joins.
   
   Example, with the actual access to the data-table replaced, as there
   is no index it triggers the nested loop join immediatly (despite
   MATERIALIZED + ORDER BY):
   
   WITH series1h AS MATERIALIZED (SELECT generate_series AS ts FROM
   generate_series('1990-01-01 00:00', '1990-12-31 23:59', INTERVAL '1
   hour') ORDER BY ts),
   series15m AS MATERIALIZED (SELECT generate_series AS ts FROM
   generate_series('1990-01-01 00:00', '1990-12-31 23:59', INTERVAL '15
   minutes') ORDER BY ts)
   SELECT count(*) FROM (SELECT h1.ts, count(*) FROM series1h h1 JOIN
   series15m m15 ON (m15.ts > (h1.ts - INTERVAL '1 hour') AND  m15.ts <=
   h1.ts ) GROUP BY h1.ts ORDER BY h1.ts);
   
   date_bin would allow to join on equality, however according to the
   docs  it doesn't support months/years: The stride interval must be
   greater than zero and cannot contain units of month or larger.
   
   For now I am using temporary tables which can be indexed, are there
   ways to avoid them?
   
   Thanks, Clemens
   
   
   
   

2. Re: Any way to get nested loop index joins on CTEs?

   Jean-Christophe BOGGIO <postgresql@thefreecat.org> — 2025-07-16T11:24:54Z

   Following up on this, I very often have to create PLPgSql functions to 
   workaround this problem: create one (or several) temp table(s) (with ON 
   COMMIT DROP), analyze it/them and create indices on some field(s).
   
   Being able to write something like:
   
   WITH xxx AS MATERIALIZED ANALYZED INDEXED ON field1 (
        SELECT DISTINCT myfield AS field1
        FROM table1
   )
   SELECT field2
   FROM table2
   JOIN xxx USING(field1);
   
   would help a lot in some cases. Of course, the syntax is just a thought 
   but the general idea is there.
   
   
   
   
   
   

3. Re: Any way to get nested loop index joins on CTEs?

   Renan Alves Fonseca <renanfonseca@gmail.com> — 2025-08-29T16:38:50Z

   Hi,
   
   it is definitively possible to get nested loop joins on successively
   aggregated CTEs. However, for the index to be used, it must exist. And
   you can only create the index on a real table, not on the intermediate
   CTEs.
   
   > WITH series1h AS MATERIALIZED (SELECT generate_series AS ts FROM
   > generate_series('1990-01-01 00:00', '1990-12-31 23:59', INTERVAL '1
   > hour') ORDER BY ts),
   > series15m AS MATERIALIZED (SELECT generate_series AS ts FROM
   > generate_series('1990-01-01 00:00', '1990-12-31 23:59', INTERVAL '15
   > minutes') ORDER BY ts)
   > SELECT count(*) FROM (SELECT h1.ts, count(*) FROM series1h h1 JOIN
   > series15m m15 ON (m15.ts > (h1.ts - INTERVAL '1 hour') AND  m15.ts <=
   > h1.ts ) GROUP BY h1.ts ORDER BY h1.ts);
   
   Here is an example based on the query above:
   
   
   CREATE TEMP TABLE series15m AS
       (SELECT
           generate_series('1990-01-01 00:00',
                           '1990-12-31 23:59',
                           INTERVAL '15 minutes')::timestamp as ts,
           random() as your_data
       ORDER BY 1);
   
   CREATE INDEX short_period ON series15m (ts);
   CREATE INDEX long_period ON series15m (date_trunc('hour',ts));
   
   EXPLAIN (costs 'off')
   WITH series1h AS (SELECT date_trunc('hour',ts) as ts FROM series15m)
   SELECT
     h1.ts,
     sum(your_data)
   FROM series1h h1
   JOIN series15m m15 ON
     (m15.ts >(h1.ts - INTERVAL '1 hour') AND  m15.ts <= h1.ts )
   GROUP BY h1.ts
   ORDER BY h1.ts ;
   
                           QUERY PLAN                                                                       
   -------------------------------------------------------------------
    GroupAggregate
      Group Key: date_trunc('hour'::text, series15m.ts)
      ->  Nested Loop
            ->  Index Scan using long_period on series15m
            ->  Index Scan using short_period on series15m m15
                  Index Cond: (...)
   
   
   So, the general idea is to create functional indexes (long_period in
   this example) on the base table that will cover the aggregate keys of
   intermediate CTEs. This approach works as long as the aggregate keys
   depend only on one table.
   
   Best regards,
   Renan Fonseca