I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant functional language, it's just a bit obtuse).

If I am careful about avoiding groupings, distincts or orderings, and only use a CMT in one following query/CMT, will Redshift dispense with the temporary table, and just treat it like a filter? Monitoring seems to suggest this to be the case.

For instance, will this query create two temp tables, or will the planmer say: "hey! no need to store any data…"?

with FilteredList as (
          select col1, col2 from Table
          where col1 is not Null
     MoreFiltering as (
           select col1, col2 from FilteredList
           where col2 is not Nill
select col1+col2 as Summo 
from MoreFiltering;

If you want to know what is going to do the planner. I would suggest to do EXPLAIN

I would say your planner will execute both CMT

| improve this answer | |

Your Answer

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

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