3 ms·
This is really cool! I'm curious why CTEs don't seem to be getting used here? SELECT ( SELECT ( t.data, t.created_at )::js
by chucky_z 6y ago
This is really cool!
I'm curious why CTEs don't seem to be getting used here?
SELECT
(
SELECT
(
t.data,
t.created_at
)::jsonb_x_timestamp
FROM
telemetries t
WHERE
server_id = 'a0dcc312-0623-af60-4dc0-238301cc9bf8'
AND data ? 'load_avg'
AND t.created_at >= series.target
AND t.created_at <= (series.target + INTERVAL '2592 seconds')
LIMIT 1
)
FROM
(
SELECT
target
FROM
GENERATE_SERIES(
'2020-09-01 00:00:00'::timestamp,
'2020-09-15 23:59:59'::timestamp,
INTERVAL '2592 seconds'
) target
) series
I'm curious on the performance of:
WITH series AS SELECT
target
FROM
GENERATE_SERIES(
'2020-09-01 00:00:00'::timestamp,
'2020-09-15 23:59:59'::timestamp,
INTERVAL '2592 seconds'
) target
SELECT
(
t.data,
t.created_at
)::jsonb_x_timestamp
FROM
telemetries t
WHERE
server_id = 'a0dcc312-0623-af60-4dc0-238301cc9bf8'
AND data ? 'load_avg'
AND t.created_at >= series.target
AND t.created_at <= (series.target + INTERVAL '2592 seconds')
LIMIT 1
or even shuffling some of the other selects and chaining CTEs together. I'm even curious if you could somehow use a combination of CTEs to generate something more suitable for use with SYSTEM_ROWS (TIL!! will keep this in my pocket for the future!).
CTEs are never a guarantee for performance improvements (they make things worse at times) but I see them getting ignored a lot when at times they are a huge boost.
Edit: wow the formatting is really bad, hopefully the code stuff doesn't kill it for mobile users
- alexchamberlain 6y agoCTEs effectively act as optimisation barriers; up to loading all the rows, there is essentially no push down of conditions into the CTE. This is contrasted with a sub query that will respect pushed down conditions. That being said, a CTE may be beneficial if it produces a small number of rows that are used a lot, or if you're simply using it for readability purposes.
- minxomat 6y agoCTE are no longer definitive optimization barriers, as recent Postgres versions will always inline side effect free CTE.
- alexchamberlain 6y agoI'd totally missed that; how recent us recent?
- minxomat 6y agoI think PG12. You can force no inlining using MATERIALIZED CTE.