public inbox for [email protected]
help / color / mirror / Atom feedAny way to get nested loop index joins on CTEs?
3+ messages / 3 participants
[nested] [flat]
* Any way to get nested loop index joins on CTEs?
@ 2025-07-15 13:29 Clemens Eisserer <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Clemens Eisserer @ 2025-07-15 13:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Any way to get nested loop index joins on CTEs?
@ 2025-07-16 11:24 Jean-Christophe BOGGIO <[email protected]>
parent: Clemens Eisserer <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Jean-Christophe BOGGIO @ 2025-07-16 11:24 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Any way to get nested loop index joins on CTEs?
@ 2025-08-29 16:38 Renan Alves Fonseca <[email protected]>
parent: Clemens Eisserer <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Renan Alves Fonseca @ 2025-08-29 16:38 UTC (permalink / raw)
To: Clemens Eisserer <[email protected]>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-29 16:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-15 13:29 Any way to get nested loop index joins on CTEs? Clemens Eisserer <[email protected]>
2025-07-16 11:24 ` Jean-Christophe BOGGIO <[email protected]>
2025-08-29 16:38 ` Renan Alves Fonseca <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox