explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9XYq

Settings
# exclusive inclusive rows x rows loops node
1. 258,193.114 258,193.114 ↓ 2.3 11,194,175 1

CTE Scan on main (cost=6,653,224.34..6,823,500.71 rows=4,865,039 width=339) (actual time=173,956.636..258,193.114 rows=11,194,175 loops=1)

  • Functions: 245
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 43.406 ms, Inlining 223.163 ms, Optimization 4153.216 ms, Emission 2963.022 ms, Total 7382.807 ms
2.          

CTE first_session

3. 2,507.197 58,370.969 ↓ 2.0 1,716,978 1

Unique (cost=1,539,452.02..1,602,399.09 rows=839,294 width=96) (actual time=52,725.814..58,370.969 rows=1,716,978 loops=1)

4. 51,535.649 55,863.772 ↑ 1.0 8,390,491 1

Sort (cost=1,539,452.02..1,560,434.38 rows=8,392,942 width=96) (actual time=52,725.811..55,863.772 rows=8,390,491 loops=1)

  • Sort Key: session.device_id, session.app_version, session."timestamp
  • Sort Method: external merge Disk: 908,080kB
5. 4,328.123 4,328.123 ↑ 1.0 8,390,491 1

Seq Scan on session (cost=0.00..574,232.42 rows=8,392,942 width=96) (actual time=0.011..4,328.123 rows=8,390,491 loops=1)

6.          

CTE main

7. 4,513.263 229,503.923 ↓ 2.3 11,194,175 1

Append (cost=3,397,947.08..5,050,825.25 rows=4,865,039 width=211) (actual time=173,956.620..229,503.923 rows=11,194,175 loops=1)

8. 11,557.827 195,549.977 ↓ 3.0 9,444,159 1

Merge Left Join (cost=3,397,947.08..3,485,772.19 rows=3,114,566 width=211) (actual time=173,956.619..195,549.977 rows=9,444,159 loops=1)

  • Merge Cond: ((s.app_version = first_session.first_app_version_id) AND (s.device_id = first_session.device_id))
9. 57,368.108 112,473.752 ↓ 3.0 9,444,159 1

Sort (cost=3,298,579.64..3,306,366.05 rows=3,114,566 width=162) (actual time=104,738.833..112,473.752 rows=9,444,159 loops=1)

  • Sort Key: s.app_version, s.device_id
  • Sort Method: external merge Disk: 1,603,680kB
10. 377.818 55,105.644 ↓ 3.0 9,444,159 1

Gather (cost=627,943.93..2,962,664.36 rows=3,114,566 width=162) (actual time=10,849.699..55,105.644 rows=9,444,159 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 4,052.310 54,727.826 ↓ 2.4 3,148,053 3 / 3

Parallel Hash Join (cost=626,943.93..2,650,207.76 rows=1,297,736 width=162) (actual time=10,840.962..54,727.826 rows=3,148,053 loops=3)

  • Hash Cond: (m.session_id = s.session_id)
12. 39,855.100 39,855.100 ↑ 1.2 3,175,117 3 / 3

Parallel Seq Scan on metric_value m (cost=0.00..2,003,257.06 rows=3,893,208 width=83) (actual time=13.794..39,855.100 rows=3,175,117 loops=3)

  • Filter: (metric_name = ANY ('{launchTime,PlaceOrderPageEnterTime,restoreWebWorker,restoreWebWorkerWithReConnect,restoreWebWorkerWithReLogin}'::text[]))
  • Rows Removed by Filter: 19,935,357
13. 2,572.027 10,820.416 ↓ 2.4 2,745,392 3 / 3

Parallel Hash (cost=612,372.86..612,372.86 rows=1,165,686 width=135) (actual time=10,820.415..10,820.416 rows=2,745,392 loops=3)

  • Buckets: 8,388,608 (originally 4194304) Batches: 1 (originally 1) Memory Usage: 1,490,720kB
14. 2,354.923 8,248.389 ↓ 2.4 2,745,392 3 / 3

Parallel Hash Join (cost=40,326.09..612,372.86 rows=1,165,686 width=135) (actual time=3,495.077..8,248.389 rows=2,745,392 loops=3)

  • Hash Cond: (s.device_id = etl_tbl_all_capital_devices.device_id)
15. 2,403.724 2,403.724 ↓ 2.4 2,745,450 3 / 3

Parallel Seq Scan on session s (cost=0.00..568,986.83 rows=1,165,686 width=105) (actual time=2.222..2,403.724 rows=2,745,450 loops=3)

  • Filter: (date(to_timestamp((("timestamp" / 1000))::double precision)) > '2017-10-01'::date)
  • Rows Removed by Filter: 51,381
16. 1,264.369 3,489.742 ↑ 1.3 497,018 3 / 3

Parallel Hash (cost=32,559.93..32,559.93 rows=621,293 width=52) (actual time=3,489.742..3,489.742 rows=497,018 loops=3)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 146,304kB
17. 2,225.373 2,225.373 ↑ 1.3 497,018 3 / 3

Parallel Seq Scan on etl_tbl_all_capital_devices (cost=0.00..32,559.93 rows=621,293 width=52) (actual time=1,896.539..2,225.373 rows=497,018 loops=3)

18. 12,157.324 71,518.398 ↓ 11.3 9,515,101 1

Sort (cost=99,367.44..101,465.68 rows=839,294 width=96) (actual time=69,212.492..71,518.398 rows=9,515,101 loops=1)

  • Sort Key: first_session.first_app_version_id, first_session.device_id
  • Sort Method: quicksort Memory: 340,303kB
19. 59,361.074 59,361.074 ↓ 2.0 1,716,978 1

CTE Scan on first_session (cost=0.00..16,785.88 rows=839,294 width=96) (actual time=52,725.834..59,361.074 rows=1,716,978 loops=1)

20. 1,294.446 29,440.683 ↑ 1.0 1,750,016 1

Hash Left Join (cost=1,187,797.62..1,492,077.48 rows=1,750,473 width=211) (actual time=14,630.213..29,440.683 rows=1,750,016 loops=1)

  • Hash Cond: (l.user_id = d.user_id)
21. 3,667.807 26,806.132 ↑ 1.0 1,750,016 1

Merge Left Join (cost=1,083,531.77..1,333,284.98 rows=1,750,473 width=171) (actual time=13,285.442..26,806.132 rows=1,750,016 loops=1)

  • Merge Cond: (l.device_id = c.device_id)
22. 2,554.307 17,164.654 ↑ 1.0 1,750,016 1

Merge Left Join (cost=1,083,531.21..1,107,930.28 rows=1,750,473 width=195) (actual time=13,283.639..17,164.654 rows=1,750,016 loops=1)

  • Merge Cond: ((l.device_id = first_session_1.device_id) AND (s_1.app_version = first_session_1.first_app_version_id))
23. 6,413.745 12,252.808 ↑ 1.0 1,750,016 1

Sort (cost=984,163.76..988,539.94 rows=1,750,473 width=163) (actual time=11,648.051..12,252.808 rows=1,750,016 loops=1)

  • Sort Key: l.device_id, s_1.app_version
  • Sort Method: quicksort Memory: 500,217kB
24. 326.944 5,839.063 ↑ 1.0 1,750,016 1

Gather (cost=569,986.83..802,645.72 rows=1,750,473 width=163) (actual time=4,571.144..5,839.063 rows=1,750,016 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 625.620 5,512.119 ↑ 1.3 583,339 3 / 3

Parallel Hash Left Join (cost=568,986.83..626,598.42 rows=729,364 width=163) (actual time=4,554.962..5,512.119 rows=583,339 loops=3)

  • Hash Cond: (l.session_id = s_1.session_id)
26. 356.606 356.606 ↑ 1.3 583,334 3 / 3

Parallel Seq Scan on command_latency l (cost=0.00..50,014.05 rows=729,364 width=133) (actual time=0.469..356.606 rows=583,334 loops=3)

  • Filter: (command = 'PLACE_MARKET_ORDER'::text)
  • Rows Removed by Filter: 0
27. 2,233.092 4,529.893 ↑ 1.3 2,796,830 3 / 3

Parallel Hash (cost=525,273.59..525,273.59 rows=3,497,059 width=93) (actual time=4,529.892..4,529.893 rows=2,796,830 loops=3)

  • Buckets: 16,777,216 Batches: 1 Memory Usage: 1,206,560kB
28. 2,296.801 2,296.801 ↑ 1.3 2,796,830 3 / 3

Parallel Seq Scan on session s_1 (cost=0.00..525,273.59 rows=3,497,059 width=93) (actual time=552.284..2,296.801 rows=2,796,830 loops=3)

29. 1,871.174 2,357.539 ↓ 3.9 3,266,527 1

Sort (cost=99,367.44..101,465.68 rows=839,294 width=96) (actual time=1,635.529..2,357.539 rows=3,266,527 loops=1)

  • Sort Key: first_session_1.device_id, first_session_1.first_app_version_id
  • Sort Method: quicksort Memory: 340,303kB
30. 486.365 486.365 ↓ 2.0 1,716,978 1

CTE Scan on first_session first_session_1 (cost=0.00..16,785.88 rows=839,294 width=96) (actual time=0.004..486.365 rows=1,716,978 loops=1)

31. 5,973.671 5,973.671 ↑ 1.5 7,216,907 1

Index Scan using ix_ip_country_session_1 on etl_tbl_first_ip_country_session c (cost=0.56..368,778.31 rows=10,738,292 width=24) (actual time=1.759..5,973.671 rows=7,216,907 loops=1)

32. 164.808 1,340.105 ↑ 1.0 524,021 1

Hash (cost=97,707.44..97,707.44 rows=524,673 width=11) (actual time=1,340.105..1,340.105 rows=524,021 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 31,147kB
33. 213.557 1,175.297 ↑ 1.0 524,021 1

Subquery Scan on d (cost=92,363.22..97,707.44 rows=524,673 width=11) (actual time=712.178..1,175.297 rows=524,021 loops=1)

34. 250.817 961.740 ↑ 1.0 524,021 1

Hash Right Join (cost=92,363.22..92,460.71 rows=524,673 width=3,512) (actual time=712.176..961.740 rows=524,021 loops=1)

  • Hash Cond: (aff_goals.user_id = u.user_id)
35.          

CTE aff_goals

36. 3.211 11.426 ↓ 1.1 4,556 1

Sort (cost=1,004.01..1,014.78 rows=4,309 width=28) (actual time=10.383..11.426 rows=4,556 loops=1)

  • Sort Key: g.user_id
  • Sort Method: quicksort Memory: 415kB
37. 5.159 8.215 ↓ 1.1 4,556 1

HashAggregate (cost=700.81..743.90 rows=4,309 width=28) (actual time=6.649..8.215 rows=4,556 loops=1)

  • Group Key: g.user_id
38. 3.056 3.056 ↑ 1.0 7,157 1

Seq Scan on tbl_affiliate_goals_for_export g (cost=0.00..503.99 rows=7,157 width=28) (actual time=0.028..3.056 rows=7,157 loops=1)

  • Filter: (goal_name <> 'INSTALL_EVENT'::text)
  • Rows Removed by Filter: 2,362
39. 13.793 13.793 ↓ 1.1 4,556 1

CTE Scan on aff_goals (cost=0.00..86.18 rows=4,309 width=8) (actual time=10.385..13.793 rows=4,556 loops=1)

40. 163.494 697.130 ↑ 1.0 524,021 1

Hash (cost=84,790.02..84,790.02 rows=524,673 width=11) (actual time=697.130..697.130 rows=524,021 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 29,594kB
41. 533.636 533.636 ↑ 1.0 524,021 1

Seq Scan on etl_dim_ums_users u (cost=0.00..84,790.02 rows=524,673 width=11) (actual time=0.020..533.636 rows=524,021 loops=1)

  • Filter: (user_role = ANY ('{USER,PROFESSIONAL}'::text[]))
  • Rows Removed by Filter: 303,993
Execution time : 261,743.316 ms