Subject: Any way to get nested loop index joins on CTEs?

From: Clemens Eisserer <[email-masked]>
Date: Tue, 15 Jul 2025 15:29:27 +0200

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

---

From: Jean-Christophe BOGGIO <[email-masked]>
Date: Wed, 16 Jul 2025 13:24:54 +0200

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.

---

From: Renan Alves Fonseca <[email-masked]>
Date: Fri, 29 Aug 2025 18:38:50 +0200

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.

> 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