What type of bug is this?
Performance issue
What subsystems and features are affected?
Query planner
What happened?
query planner fails to prune chunks
when using a value computed at runtime from a cte
if i made first query to get the created_at and passed it as static it works as expected
idk if its real bug or mis understanding by me
TimescaleDB version affected
2.23.0
PostgreSQL version used
18.4
What operating system did you use?
Ubuntu 22.04 x64
What installation method did you use?
Source
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
yes lol i used paste it to paste in the plan , its 9k lines
https://justpaste.it/b41wt
How can we reproduce the bug?
WITH latest_collection AS (
SELECT *
FROM pgmetrics_collections
WHERE server_id = 'uuid'
ORDER BY created_at DESC
LIMIT 1
),
database_stats AS (
SELECT
COUNT(DISTINCT d.name) AS total_databases,
SUM(d.blks_hit) AS total_blks_hit,
SUM(d.blks_read) AS total_blks_read
FROM pgmetrics_databases d
JOIN latest_collection lc
ON d.server_id = lc.server_id
AND d.created_at = lc.created_at
),
backend_stats AS (
SELECT COUNT(*) AS total_backends
FROM pgmetrics_backends b
JOIN latest_collection lc
ON b.server_id = lc.server_id
AND b.created_at = lc.created_at
),
statement_stats AS (
SELECT COUNT(DISTINCT s.queryid) AS total_statements
FROM pgmetrics_statements s
JOIN latest_collection lc
ON s.server_id = lc.server_id
AND s.created_at = lc.created_at
)
SELECT *
FROM latest_collection lc
LEFT JOIN database_stats ds ON true
LEFT JOIN backend_stats bs ON true
LEFT JOIN statement_stats ss ON true;
What type of bug is this?
Performance issue
What subsystems and features are affected?
Query planner
What happened?
query planner fails to prune chunks
when using a value computed at runtime from a cte
if i made first query to get the created_at and passed it as static it works as expected
idk if its real bug or mis understanding by me
TimescaleDB version affected
2.23.0
PostgreSQL version used
18.4
What operating system did you use?
Ubuntu 22.04 x64
What installation method did you use?
Source
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
yes lol i used paste it to paste in the plan , its 9k lines https://justpaste.it/b41wtHow can we reproduce the bug?
WITH latest_collection AS ( SELECT * FROM pgmetrics_collections WHERE server_id = 'uuid' ORDER BY created_at DESC LIMIT 1 ), database_stats AS ( SELECT COUNT(DISTINCT d.name) AS total_databases, SUM(d.blks_hit) AS total_blks_hit, SUM(d.blks_read) AS total_blks_read FROM pgmetrics_databases d JOIN latest_collection lc ON d.server_id = lc.server_id AND d.created_at = lc.created_at ), backend_stats AS ( SELECT COUNT(*) AS total_backends FROM pgmetrics_backends b JOIN latest_collection lc ON b.server_id = lc.server_id AND b.created_at = lc.created_at ), statement_stats AS ( SELECT COUNT(DISTINCT s.queryid) AS total_statements FROM pgmetrics_statements s JOIN latest_collection lc ON s.server_id = lc.server_id AND s.created_at = lc.created_at ) SELECT * FROM latest_collection lc LEFT JOIN database_stats ds ON true LEFT JOIN backend_stats bs ON true LEFT JOIN statement_stats ss ON true;