1

Say I have the following query:

WITH
    d AS
    ( SELECT x, y FROM test WHERE x > 0 AND y > 0
    )
  , sc_x AS
    ( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE x >= 5
    )
  , sc_y AS
    ( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE y >= 5
    )
SELECT
    SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
    sc_x
  , sc_y

Let's pretend those calculations are super complex and make sense to have in separate CTEs (I know these aren't). I need to query each of the CTE results and the final results for output for a program. I am thinking the best way to do this is to remove the CTEs and do SELECT ?? INTO like this for each CTE:

DROP TABLE d IF EXISTS;
SELECT x, y into d FROM test WHERE x > 0 AND y > 0;
DROP TABLE sc_x IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_x FROM d WHERE x >= 5;
DROP TABLE sc_y IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_y FROM d WHERE y >= 5;
SELECT SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
    sc_x
  , sc_y;

Then query these tables separately. Is that the best strategy? I don't see another way to make the CTE results available without recalculating them. Speed is an issue, so I don't want to do all of the processing again that occurs before the CTE I want for output. Hopefully that makes sense.

PostgreSQL CTE page: https://www.postgresql.org/docs/current/static/queries-with.html

I have Postgres 9.6.

  • What do you mean by recalculating? That the result set of a CTE is once built, and if you query it twice, it's built twice? This latter is not the case. – dezso Aug 30 '17 at 13:39
  • Thanks for helping. The context is that I always need the final results/select. If I also want CTE sc_x for output and I don't do the Select Into stuff, I have to run the query for CTE d and CTE sc_x over again. In the real world these take a long time. So creating the intermediate tables using Select Into makes all of the results available...but doing it with the select intos I have to create a bunch of tables, which is a bit messy in my opinion. – mountainclimber Aug 30 '17 at 13:46
  • 3
    Well, if performance is really a matter, a bit of mess in the code cannot hurt :) Otherwise, you can create those tables as temporary ones, adjust work_mem and temp_buffers, wrap the whole thing in a function and hopefully done. Otherwise, seeing an actual execution plan of your query could help coming up with better ideas. – dezso Aug 30 '17 at 13:53
  • 1
    Unrelated, but: it is recommended to use create table new_table as select instead of the non-standard select ... into new_table ... – a_horse_with_no_name Aug 30 '17 at 19:51
  • 1
    @a_horse_with_no_name - how would the syntax work for that? I got with my_cte as ([query here]) select * into new_table from ... to work, but not with my_cte as ([query here]) create table as select ... – mountainclimber Aug 31 '17 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.