explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8RaEN

Settings
# exclusive inclusive rows x rows loops node
1. 48.342 3,874.667 ↓ 80.0 80 1

Nested Loop (cost=766,465.52..766,465.59 rows=1 width=101) (actual time=3,784.758..3,874.667 rows=80 loops=1)

  • Join Filter: (events_2019_11_01.domain_sessionid = session_campaign.domain_sessionid)
  • Rows Removed by Join Filter: 482160
2.          

CTE session_campaign

3. 3.469 840.182 ↓ 6,028.0 6,028 1

Group (cost=391,317.51..391,317.52 rows=1 width=48) (actual time=834.920..840.182 rows=6,028 loops=1)

  • Group Key: events_2019_11_01_1.domain_sessionid, events_2019_11_01_1.mkt_content
4. 24.693 836.713 ↓ 14,613.0 14,613 1

Sort (cost=391,317.51..391,317.51 rows=1 width=48) (actual time=834.917..836.713 rows=14,613 loops=1)

  • Sort Key: events_2019_11_01_1.domain_sessionid, events_2019_11_01_1.mkt_content
  • Sort Method: quicksort Memory: 2270kB
5. 5.857 812.020 ↓ 14,613.0 14,613 1

Gather (cost=1,000.00..391,317.50 rows=1 width=48) (actual time=0.518..812.020 rows=14,613 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 806.163 806.163 ↓ 4,871.0 4,871 3 / 3

Parallel Seq Scan on events_2019_11_01 events_2019_11_01_1 (cost=0.00..390,317.40 rows=1 width=48) (actual time=0.157..806.163 rows=4,871 loops=3)

  • Filter: ((etl_tstamp > '2018-12-18 09:16:23.667'::timestamp without time zone) AND (mkt_source = 'Facebook_HP'::text) AND ((collector_tstamp)::date >= '2019-01-01'::date) AND ((collector_tstamp)::date <= '2019-11-18'::date) AND (mkt_content = ANY ('{23843805914300702,23843998052440702,23843925558150702,23843925472240702,23843799778680702,23843665727360702,23843665718530702,23843653265140702,23843561480940702,23843301519760702,23843446745490702,23843713458150702,23843713362830702,23843713351980702,23843713349940702,23843665727310702,23843665727350702,23843665718550702,23843665718510702,23843653265130702,23843653265090702,23843535403370702,23843535390030702,23843446654770702,23843446643050702,23843301367630702,23843301362540702,23843200210870702,23843743257690702,23843743257680702}'::text[])))
  • Rows Removed by Filter: 570341
7. 0.066 3,784.805 ↓ 80.0 80 1

Group (cost=375,148.00..375,148.03 rows=1 width=69) (actual time=3,784.715..3,784.805 rows=80 loops=1)

  • Group Key: events_2019_11_01.domain_sessionid, ((((((events_2019_11_01.contexts)::json -> 'data'::text) -> 0) -> 'data'::text) ->> 'orderId'::text))
8. 0.263 3,784.739 ↓ 85.0 85 1

Sort (cost=375,148.00..375,148.01 rows=1 width=69) (actual time=3,784.713..3,784.739 rows=85 loops=1)

  • Sort Key: events_2019_11_01.domain_sessionid, ((((((events_2019_11_01.contexts)::json -> 'data'::text) -> 0) -> 'data'::text) ->> 'orderId'::text))
  • Sort Method: quicksort Memory: 31kB
9. 7.184 3,784.476 ↓ 85.0 85 1

Hash Semi Join (cost=1,000.03..375,147.99 rows=1 width=69) (actual time=900.972..3,784.476 rows=85 loops=1)

  • Hash Cond: (events_2019_11_01.domain_sessionid = session_campaign_1.domain_sessionid)
10. 0.000 2,933.497 ↓ 33.1 1,425 1

Gather (cost=1,000.00..375,147.83 rows=43 width=428) (actual time=20.220..2,933.497 rows=1,425 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 2,933.515 2,933.515 ↓ 26.4 475 3 / 3

Parallel Seq Scan on events_2019_11_01 (cost=0.00..374,143.53 rows=18 width=428) (actual time=18.528..2,933.515 rows=475 loops=3)

  • Filter: ((etl_tstamp > '2018-12-18 09:16:23.667'::timestamp without time zone) AND ((collector_tstamp)::date >= '2019-01-01'::date) AND ((collector_tstamp)::date <= '2019-11-18'::date) AND ((((((contexts)::json -> 'data'::text) -> 0) -> 'data'::text) ->> 'eventType'::text) = 'order'::text))
  • Rows Removed by Filter: 574737
12. 1.175 843.795 ↓ 6,021.0 6,021 1

Hash (cost=0.02..0.02 rows=1 width=32) (actual time=843.795..843.795 rows=6,021 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 470kB
13. 842.620 842.620 ↓ 6,028.0 6,028 1

CTE Scan on session_campaign session_campaign_1 (cost=0.00..0.02 rows=1 width=32) (actual time=834.925..842.620 rows=6,028 loops=1)

14. 41.520 41.520 ↓ 6,028.0 6,028 80

CTE Scan on session_campaign (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.519 rows=6,028 loops=80)