title: Understanding explain while using CTEs - trying to get a query to compute
body: <p>I've been wrestling with a query and experimented with variations to arrive at my desired result. But I have failed. I'm hoping that if I share the variations that I have tried along with the explain statement output, anyone might have a pointer.</p>
  
  <p>Postgres 11.6.</p>
  
  <p>For the code blocks below, dimension1 is a field that exists on all tables that I am referencing. Date only appears in sessions table, so to pull data for a particular date, I create a cte filter_sessions to get only dimension1 's that appear on a given date then join to my other tables. This allows my query to select data for a particular date, in this case February 6th.</p>
  
  <p>Here was my initial attempt. It uses a CTE which I prefer for readability and that I could get away with writing less code if it would just run, which it does not:</p>
  
  <pre><code>with 
  
  filter_sessions as (
  select 
      dimension1,
      dimension2,
      date,
      channel_grouping,
      device_category,
      user_type
  from ga_flagship_ecom.sessions
  where date &gt;= '2020-02-06'
  and date &lt;= '2020-02-06'
  ),
  
  ee as (
  select 
      e.dimension1,
      e.dimension3,
      case when sum(case when e.metric1 = 0 then 1 else 0 end) &gt; 0 then 1 else 0 end as zero_val_product, -- roll up to event level
  
      -- approximation for inferring if the product i a download and hence sees all the checkout steps
      case when sum(case when lower(product_name) ~ 'digital|download|file' then 1 else 0 end) &gt; 0 then 1 else 0 end as download
  from ga_flagship_ecom.ecom e
  join filter_sessions f on f.dimension1 = e.dimension1
  group by 1,2
  ),
  
  ecom_events as (
  select 
      ev.dimension1,
      ev.dimension3,
      ev.event_action,
      ev.event_label,
      ee.zero_val_product,
      ee.download
  from ga_flagship_ecom.events ev 
  join ee on ee.dimension1 = ev.dimension1 and ee.dimension3 = ev.dimension3
  where ev.event_category = 'ecom'
  )
  
  select 
      s.date,
      lower(s.channel_grouping) as channel_grouping,
      lower(s.device_category) as device_category,
      lower(s.user_type) as user_type,
      lower(ev.event_action) as event_action,
      lower(coalesce(ev.event_label, 'na')) as event_label,
      ev.zero_val_product,
      ev.download,
      count(distinct s.dimension1) as sessions,
      count(distinct s.dimension2) as daily_users
  from filter_sessions s
  join ecom_events ev on ev.dimension1 = s.dimension1
  group by 1,2,3,4,5,6,7,8;
  </code></pre>
  
  <p>Here is what the explain output of this query looks like:</p>
  
  <pre><code>GroupAggregate  (cost=222818.83..222818.88 rows=1 width=188)
    Group Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
    CTE filter_sessions
      -&gt;  Index Scan using sessions_date_idx on sessions  (cost=0.56..2.78 rows=1 width=76)
            Index Cond: ((date &gt;= '2020-02-06'::date) AND (date &lt;= '2020-02-06'::date))
    CTE ee
      -&gt;  GroupAggregate  (cost=47604.61..47606.29 rows=48 width=38)
            Group Key: e.dimension1, e.dimension3
            -&gt;  Sort  (cost=47604.61..47604.73 rows=48 width=51)
                  Sort Key: e.dimension1, e.dimension3
                  -&gt;  Nested Loop  (cost=0.56..47603.27 rows=48 width=51)
                        -&gt;  CTE Scan on filter_sessions f  (cost=0.00..0.02 rows=1 width=32)
                        -&gt;  Index Scan using ecom_dimension1_idx on ecom e  (cost=0.56..47602.77 rows=48 width=51)
                              Index Cond: ((dimension1)::text = (f.dimension1)::text)
    CTE ecom_events
      -&gt;  Hash Join  (cost=1.68..175209.67 rows=1 width=60)
            Hash Cond: (((ev_1.dimension1)::text = (ee.dimension1)::text) AND (ev_1.dimension3 = ee.dimension3))
            -&gt;  Seq Scan on events ev_1  (cost=0.00..150210.69 rows=3332973 width=52)
                  Filter: ((event_category)::text = 'ecom'::text)
            -&gt;  Hash  (cost=0.96..0.96 rows=48 width=48)
                  -&gt;  CTE Scan on ee  (cost=0.00..0.96 rows=48 width=48)
    -&gt;  Sort  (cost=0.08..0.08 rows=1 width=236)
          Sort Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
          -&gt;  Nested Loop  (cost=0.00..0.07 rows=1 width=236)
                Join Filter: ((s.dimension1)::text = (ev.dimension1)::text)
                -&gt;  CTE Scan on filter_sessions s  (cost=0.00..0.02 rows=1 width=164)
                -&gt;  CTE Scan on ecom_events ev  (cost=0.00..0.02 rows=1 width=104)
  </code></pre>
  
  <p>Someone suggested that cte ee was my bottleneck and that I should focus on that. I tried a subquery on cte ee rather than referencing cte filter_sessions. So change:</p>
  
  <pre><code>ee as (
  select 
      e.dimension1,
      e.dimension3,
      case when sum(case when e.metric1 = 0 then 1 else 0 end) &gt; 0 then 1 else 0 end as zero_val_product, -- roll up to event level
  
      -- approximation for inferring if the product i a download and hence sees all the checkout steps
      case when sum(case when lower(product_name) ~ 'digital|download|file' then 1 else 0 end) &gt; 0 then 1 else 0 end as download
  from ga_flagship_ecom.ecom e
  --join filter_sessions f on f.dimension1 = e.dimension1
  join (select dimension1 from ga_flagship_ecom.sessions where date &gt;= '2020-02-06' and date &lt;= '2020-02-06') f
      on f.dimension1 = e.dimension1
  group by 1,2
  ),
  </code></pre>
  
  <p>Here's explain with that small change:</p>
  
  <pre><code>GroupAggregate  (cost=107619.19..107619.24 rows=1 width=188)
    Group Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
    CTE filter_sessions
      -&gt;  Index Scan using sessions_date_idx on sessions  (cost=0.56..2.78 rows=1 width=76)
            Index Cond: ((date &gt;= '2020-02-06'::date) AND (date &lt;= '2020-02-06'::date))
    CTE ee
      -&gt;  GroupAggregate  (cost=47606.05..47606.08 rows=1 width=38)
            Group Key: e.dimension1, e.dimension3
            -&gt;  Sort  (cost=47606.05..47606.05 rows=1 width=51)
                  Sort Key: e.dimension1, e.dimension3
                  -&gt;  Nested Loop  (cost=1.12..47606.04 rows=1 width=51)
                        -&gt;  Index Only Scan using sessions_date_idx on sessions sessions_1  (cost=0.56..2.78 rows=1 width=22)
                              Index Cond: ((date &gt;= '2020-02-06'::date) AND (date &lt;= '2020-02-06'::date))
                        -&gt;  Index Scan using ecom_dimension1_idx on ecom e  (cost=0.56..47602.77 rows=48 width=51)
                              Index Cond: ((dimension1)::text = (sessions_1.dimension1)::text)
    CTE ecom_events
      -&gt;  Nested Loop  (cost=0.56..60010.25 rows=1 width=60)
            -&gt;  CTE Scan on ee  (cost=0.00..0.02 rows=1 width=48)
            -&gt;  Index Scan using events_pk on events ev_1  (cost=0.56..60010.22 rows=1 width=52)
                  Index Cond: (((dimension1)::text = (ee.dimension1)::text) AND (dimension3 = ee.dimension3))
                  Filter: ((event_category)::text = 'ecom'::text)
    -&gt;  Sort  (cost=0.08..0.08 rows=1 width=236)
          Sort Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
          -&gt;  Nested Loop  (cost=0.00..0.07 rows=1 width=236)
                Join Filter: ((s.dimension1)::text = (ev.dimension1)::text)
                -&gt;  CTE Scan on filter_sessions s  (cost=0.00..0.02 rows=1 width=164)
                -&gt;  CTE Scan on ecom_events ev  (cost=0.00..0.02 rows=1 width=104)
  </code></pre>
  
  <p>I am unsure how to interpret the numbers in explain output, but for cte ee, those numbers are practically the same so I don't think that change made much difference?     <code>CTE ee-&gt;  GroupAggregate  (cost=47606.05..47606.08 rows=1 width=38)</code></p>
  
  <p>Either way, the query still does not complete. Other things that I have tried (All failed to run, the query just runs indefinitely):</p>
  
  <p>Instead of an inner join, a where filter like so:</p>
  
  <pre><code>ee as (
  select 
      e.dimension1,
      e.dimension3,
      case when sum(case when e.metric1 = 0 then 1 else 0 end) &gt; 0 then 1 else 0 end as zero_val_product, -- roll up to event level
  
      -- approximation for inferring if the product i a download and hence sees all the checkout steps
      case when sum(case when lower(product_name) ~ 'digital|download|file' then 1 else 0 end) &gt; 0 then 1 else 0 end as download
  from ga_flagship_ecom.ecom e
  --join filter_sessions f on f.dimension1 = e.dimension1
  where e.dimension1 in (select dimension1 from filter_sessions)
  group by 1,2
  ),
  </code></pre>
  
  <p>Here is the explain output based on using a where filter instead of an inner join:</p>
  
  <pre><code>GroupAggregate  (cost=222818.84..222818.89 rows=1 width=188)
    Group Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
    CTE filter_sessions
      -&gt;  Index Scan using sessions_date_idx on sessions  (cost=0.56..2.78 rows=1 width=76)
            Index Cond: ((date &gt;= '2020-02-06'::date) AND (date &lt;= '2020-02-06'::date))
    CTE ee
      -&gt;  GroupAggregate  (cost=47604.63..47606.31 rows=48 width=38)
            Group Key: e.dimension1, e.dimension3
            -&gt;  Sort  (cost=47604.63..47604.75 rows=48 width=51)
                  Sort Key: e.dimension1, e.dimension3
                  -&gt;  Nested Loop  (cost=0.58..47603.29 rows=48 width=51)
                        -&gt;  HashAggregate  (cost=0.02..0.03 rows=1 width=32)
                              Group Key: (filter_sessions.dimension1)::text
                              -&gt;  CTE Scan on filter_sessions  (cost=0.00..0.02 rows=1 width=32)
                        -&gt;  Index Scan using ecom_dimension1_idx on ecom e  (cost=0.56..47602.77 rows=48 width=51)
                              Index Cond: ((dimension1)::text = (filter_sessions.dimension1)::text)
    CTE ecom_events
      -&gt;  Hash Join  (cost=1.68..175209.67 rows=1 width=60)
            Hash Cond: (((ev_1.dimension1)::text = (ee.dimension1)::text) AND (ev_1.dimension3 = ee.dimension3))
            -&gt;  Seq Scan on events ev_1  (cost=0.00..150210.69 rows=3332973 width=52)
                  Filter: ((event_category)::text = 'ecom'::text)
            -&gt;  Hash  (cost=0.96..0.96 rows=48 width=48)
                  -&gt;  CTE Scan on ee  (cost=0.00..0.96 rows=48 width=48)
    -&gt;  Sort  (cost=0.08..0.08 rows=1 width=236)
          Sort Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
          -&gt;  Nested Loop  (cost=0.00..0.07 rows=1 width=236)
                Join Filter: ((s.dimension1)::text = (ev.dimension1)::text)
                -&gt;  CTE Scan on filter_sessions s  (cost=0.00..0.02 rows=1 width=164)
                -&gt;  CTE Scan on ecom_events ev  (cost=0.00..0.02 rows=1 width=104)
  </code></pre>
  
  <p>I then tried to split cte ee into two parts like so:</p>
  
  <pre><code>ee_base as (
  select 
      e.dimension1,
      e.dimension3,
      e.metric1,
      lower(product_name) as product_name
  from ga_flagship_ecom.ecom e
  join filter_sessions f on f.dimension1 = e.dimension1
  ),
  
  
  ee as (
  select 
      dimension1,
      dimension3,
      case when sum(case when metric1 = 0 then 1 else 0 end) &gt; 0 then 1 else 0 end as zero_val_product, -- roll up to event level
  
      -- approximation for inferring if the product i a download and hence sees all the checkout steps
      case when sum(case when product_name ~ 'digital|download|file' then 1 else 0 end) &gt; 0 then 1 else 0 end as download
  from ee_base
  group by 1,2
  ),
  </code></pre>
  
  <p>This also failed (I was really optimistic this was going to work). Here is the explain output of this attempt:</p>
  
  <pre><code>GroupAggregate  (cost=222818.33..222818.38 rows=1 width=188)
    Group Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
    CTE filter_sessions
      -&gt;  Index Scan using sessions_date_idx on sessions  (cost=0.56..2.78 rows=1 width=76)
            Index Cond: ((date &gt;= '2020-02-06'::date) AND (date &lt;= '2020-02-06'::date))
    CTE ee_base
      -&gt;  Nested Loop  (cost=0.56..47603.39 rows=48 width=66)
            -&gt;  CTE Scan on filter_sessions f  (cost=0.00..0.02 rows=1 width=32)
            -&gt;  Index Scan using ecom_dimension1_idx on ecom e  (cost=0.56..47602.77 rows=48 width=51)
                  Index Cond: ((dimension1)::text = (f.dimension1)::text)
    CTE ee
      -&gt;  HashAggregate  (cost=1.68..2.40 rows=48 width=48)
            Group Key: ee_base.dimension1, ee_base.dimension3
            -&gt;  CTE Scan on ee_base  (cost=0.00..0.96 rows=48 width=76)
    CTE ecom_events
      -&gt;  Hash Join  (cost=1.68..175209.67 rows=1 width=60)
            Hash Cond: (((ev_1.dimension1)::text = (ee.dimension1)::text) AND (ev_1.dimension3 = ee.dimension3))
            -&gt;  Seq Scan on events ev_1  (cost=0.00..150210.69 rows=3332973 width=52)
                  Filter: ((event_category)::text = 'ecom'::text)
            -&gt;  Hash  (cost=0.96..0.96 rows=48 width=48)
                  -&gt;  CTE Scan on ee  (cost=0.00..0.96 rows=48 width=48)
    -&gt;  Sort  (cost=0.08..0.08 rows=1 width=236)
          Sort Key: s.date, (lower((s.channel_grouping)::text)), (lower((s.device_category)::text)), (lower((s.user_type)::text)), (lower((ev.event_action)::text)), (lower((COALESCE(ev.event_label, 'na'::character varying))::text)), ev.zero_val_product, ev.download
          -&gt;  Nested Loop  (cost=0.00..0.07 rows=1 width=236)
                Join Filter: ((s.dimension1)::text = (ev.dimension1)::text)
                -&gt;  CTE Scan on filter_sessions s  (cost=0.00..0.02 rows=1 width=164)
                -&gt;  CTE Scan on ecom_events ev  (cost=0.00..0.02 rows=1 width=104)
  </code></pre>
  
  <p>Something that does work is creating a temp table. But I really want to find a way around that and figure this out, in order of preference:</p>
  
  <ol>
  <li>Using CTEs only</li>
  <li>Using a combination of CTE's and sub queries</li>
  <li>Last, back up option, just use a temp table for filter_sessions</li>
  </ol>
  
  <p>Are there any other things that I can do here?</p>