From: Renan Alves Fonseca <renanfonseca@gmail.com>
To: Clemens Eisserer <linuxhippy@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Any way to get nested loop index joins on CTEs?
In-Reply-To: 
 <CAFvQSYQAJWioHyUNUOjhj0_P0Z9_f_G_5B=9XyYZyPUpzsWW=A@mail.gmail.com>
	(Clemens Eisserer's message of "Tue, 15 Jul 2025 15:29:27 +0200")
References: 
 <CAFvQSYQAJWioHyUNUOjhj0_P0Z9_f_G_5B=9XyYZyPUpzsWW=A@mail.gmail.com>
User-Agent: mu4e 1.12.1; emacs 29.4
Date: Fri, 29 Aug 2025 18:38:50 +0200
Message-ID: <87seharskl.fsf@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: 
 <https://www.postgresql.org/message-id/87seharskl.fsf%40gmail.com>
Precedence: bulk

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