0

I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the CTE somehow so that only new rows need to be calculated?

The (slightly simplified) query is:

WITH vals AS (
    SELECT '2013-08-01 0:00'::timestamp  AS frame_start,
           '2013-09-01 0:00'::timestamp  AS frame_end,
           '1 day'::interval             AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
                   lead(start_time, 1, frame_end) OVER (ORDER BY start_time     NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS start_time,
               frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
), market_trades_ts AS (
    SELECT time_range, td.id
    FROM intervals i
    LEFT JOIN market_trades td
    ON td.timestamp >= COALESCE(lower(i.time_range), '-infinity') AND td.timestamp < COALESCE(upper(i.time_range), 'infinity')
)
SELECT time_range, count(*) AS agg
FROM market_trades_ts td
GROUP BY time_range
ORDER BY time_range;

It would be great if market_trades_ts could be cached so that, for any intervals which it's seen before before, it can pull the previous result set, and then take its union with the new rows (for intervals which it hasn't seen before).

Is this possible? It seems like it will speed up my query dramatically.

1 Answer 1

1

PostgreSQL's CTE's materialize results - they run the CTE term once and cache the output for the duration of the query. So it's already doing what you want.

CTE results cannot be cached between queries. If you want that, you should instead CREATE TEMPORARY TABLE AS SELECT ....

4
  • Right, I know all that. I realize I need a temp table to cache results between queries. The question is about merging the temp table from the last query with the temp table from the new query so that it only needs to generate the new rows in the temp table.
    – Mike S.
    Aug 24, 2014 at 12:31
  • You need to find a way to express the query such that the query only returns new rows. Then INSERT INTO cachetable SELECT ... the new rows. Without sample data and more detail hard to say how that'd work for this query. Aug 24, 2014 at 13:24
  • We can assume that the rows with a given interval do not change, so to figure out which are the new rows, the query needs to figure out which intervals are not already present in the temp table. I'm not sure how to do this without sacrificing the benefits of caching to the temp table.
    – Mike S.
    Aug 24, 2014 at 15:30
  • @MikeS. ... which is pretty hard without sample data and schema. It's also really a separate question, so I suggest posting it as such: link back to this question, provide proper sample data as create table and insert statements, and explain that you want to progressively update a temp/unlogged table being used as a cache without recalculating the whole thing for every query. Aug 25, 2014 at 2:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.