[Bug]: Query with CTE seems to hold onto data of decompressed chunks

[wrobell]

What type of bug is this?

Other

What subsystems and features are affected?

Compression, Query executor

What happened?

There is no issues when running the SQL query for the compressed table message

select
    time_bucket('1 day', time) as period,
    count(*) / 1e6 as count
from message
where time > '2022-09-01'
group by period

But when I use the query within CTE, then the storage of my system slowly fills up, and the query execution fails once my system runs out of storage space

with daily as materialized (
    select
        time_bucket('1 day', time) as period,
        count(*) / 1e6 as count
    from message
    where time > '2022-09-01'
    group by period
)
select
    time_bucket('1 month', period) as pm,
    round(avg(count), 4) as avg_count
from daily
group by pm
order by pm

I would expect both queries to use the same strategy to decompress, and then recompress chunks of a table.

TimescaleDB version affected

2.12.2

PostgreSQL version used

15.4

What operating system did you use?

ArchLinux

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

1. Create a compressed hypertable.
2. Populate table with data for multiple chunks (i.e. 1GB each, 100 chunks).
3. Compress chunks of the table.
4. Run query without CTE, observe the storage space changes - should be negligible.
5. Run query with CTE, and observe significant storage space changes, which might cause a system to run out of space.

[akuzm]

I would expect both queries to use the same strategy to decompress, and then recompress chunks of a table.

SELECTs on a compressed table do not persistently decompress or recompress any chunks, the decompression is done in memory.

Probably the materialized CTE is spilling to disk, what does explain (analyze, buffers) say? Is the space reclaimed after the query finishes?

[wrobell]

Apologies. This issue has nothing to do with CTEs. New bug, with requested info, here: #6310.