explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5fBO

Settings
# exclusive inclusive rows x rows loops node
1. 282,966.921 282,966.921 ↓ 2.3 11,193,630 1

CTE Scan on main (cost=8,061,729.93..8,231,996.53 rows=4,864,760 width=339) (actual time=173,945.102..282,966.921 rows=11,193,630 loops=1)

  • Functions: 236
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 45.747 ms, Inlining 273.331 ms, Optimization 3833.686 ms, Emission 2686.181 ms, Total 6838.945 ms
2.          

CTE first_session

3. 2,668.956 50,852.430 ↓ 2.0 1,716,924 1

Unique (cost=2,043,416.23..2,106,360.09 rows=839,251 width=96) (actual time=41,521.495..50,852.430 rows=1,716,924 loops=1)

4. 44,037.629 48,183.474 ↑ 1.0 8,390,066 1

Sort (cost=2,043,416.23..2,064,397.52 rows=8,392,514 width=96) (actual time=41,521.492..48,183.474 rows=8,390,066 loops=1)

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

Seq Scan on session (cost=0.00..574,203.14 rows=8,392,514 width=96) (actual time=0.010..4,145.845 rows=8,390,066 loops=1)

6.          

CTE main

7. 4,506.066 254,221.944 ↓ 2.3 11,193,630 1

Append (cost=3,774,799.70..5,955,369.84 rows=4,864,760 width=211) (actual time=173,945.041..254,221.944 rows=11,193,630 loops=1)

8. 8,261.730 217,961.179 ↓ 3.0 9,443,730 1

Merge Join (cost=3,774,799.70..3,968,206.87 rows=3,114,415 width=211) (actual time=173,945.040..217,961.179 rows=9,443,730 loops=1)

  • Merge Cond: (s.device_id = etl_tbl_all_capital_devices.device_id)
9. 9,893.732 197,155.174 ↓ 3.0 9,443,854 1

Merge Left Join (cost=3,774,150.19..3,809,566.69 rows=3,114,415 width=164) (actual time=173,943.919..197,155.174 rows=9,443,854 loops=1)

  • Merge Cond: ((s.device_id = first_session.device_id) AND (s.app_version = first_session.first_app_version_id))
10. 58,802.476 129,769.879 ↓ 3.0 9,443,854 1

Sort (cost=3,649,585.45..3,657,371.48 rows=3,114,415 width=132) (actual time=120,221.796..129,769.879 rows=9,443,854 loops=1)

  • Sort Key: s.device_id, s.app_version
  • Sort Method: external merge Disk: 1,327,576kB
11. 2,100.389 70,967.403 ↓ 3.0 9,443,854 1

Gather (cost=603,880.16..3,064,288.64 rows=3,114,415 width=132) (actual time=66,267.434..70,967.403 rows=9,443,854 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 5,451.284 68,867.014 ↓ 2.4 3,147,951 3 / 3

Parallel Hash Join (cost=602,880.16..2,751,847.14 rows=1,297,673 width=132) (actual time=66,240.999..68,867.014 rows=3,147,951 loops=3)

  • Hash Cond: (m.session_id = s.session_id)
13. 55,799.156 55,799.156 ↑ 1.2 3,174,955 3 / 3

Parallel Seq Scan on metric_value m (cost=0.00..2,003,159.20 rows=3,893,018 width=83) (actual time=17.315..55,799.156 rows=3,174,955 loops=3)

  • Filter: (metric_name = ANY ('{launchTime,PlaceOrderPageEnterTime,restoreWebWorker,restoreWebWorkerWithReConnect,restoreWebWorkerWithReLogin}'::text[]))
  • Rows Removed by Filter: 19,934,441
14. 3,469.123 7,616.574 ↓ 2.4 2,745,308 3 / 3

Parallel Hash (cost=568,957.82..568,957.82 rows=1,165,627 width=105) (actual time=7,616.573..7,616.574 rows=2,745,308 loops=3)

  • Buckets: 65,536 (originally 65536) Batches: 256 (originally 64) Memory Usage: 5,056kB
15. 4,147.451 4,147.451 ↓ 2.4 2,745,308 3 / 3

Parallel Seq Scan on session s (cost=0.00..568,957.82 rows=1,165,627 width=105) (actual time=1,708.266..4,147.451 rows=2,745,308 loops=3)

  • Filter: (date(to_timestamp((("timestamp" / 1000))::double precision)) > '2017-10-01'::date)
  • Rows Removed by Filter: 51,381
16. 2,578.154 57,491.563 ↓ 11.3 9,514,648 1

Materialize (cost=124,564.74..128,761.00 rows=839,251 width=96) (actual time=53,722.068..57,491.563 rows=9,514,648 loops=1)

17. 2,830.566 54,913.409 ↓ 2.0 1,716,924 1

Sort (cost=124,564.74..126,662.87 rows=839,251 width=96) (actual time=53,722.064..54,913.409 rows=1,716,924 loops=1)

  • Sort Key: first_session.device_id, first_session.first_app_version_id
  • Sort Method: external merge Disk: 157,312kB
18. 52,082.843 52,082.843 ↓ 2.0 1,716,924 1

CTE Scan on first_session (cost=0.00..16,785.02 rows=839,251 width=96) (actual time=41,521.518..52,082.843 rows=1,716,924 loops=1)

19. 12,544.275 12,544.275 ↑ 1.0 1,491,052 1

Index Scan using ix_etl_tbl_all_capital_devices_2 on etl_tbl_all_capital_devices (cost=0.43..61,871.25 rows=1,491,103 width=52) (actual time=0.775..12,544.275 rows=1,491,052 loops=1)

20. 2,215.262 31,754.699 ↑ 1.0 1,749,900 1

Hash Left Join (cost=1,519,791.08..1,914,191.58 rows=1,750,345 width=211) (actual time=16,425.865..31,754.699 rows=1,749,900 loops=1)

  • Hash Cond: (l.user_id = d.user_id)
21. 3,779.685 28,145.037 ↑ 1.0 1,749,900 1

Merge Left Join (cost=1,407,805.23..1,659,653.06 rows=1,750,345 width=171) (actual time=15,030.469..28,145.037 rows=1,749,900 loops=1)

  • Merge Cond: (l.device_id = c.device_id)
22. 2,407.431 21,225.426 ↑ 1.0 1,749,900 1

Merge Left Join (cost=1,407,804.67..1,434,299.96 rows=1,750,345 width=195) (actual time=15,030.417..21,225.426 rows=1,749,900 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. 7,282.530 14,386.605 ↑ 1.0 1,749,900 1

Sort (cost=1,283,239.92..1,287,615.79 rows=1,750,345 width=163) (actual time=12,708.899..14,386.605 rows=1,749,900 loops=1)

  • Sort Key: l.device_id, s_1.app_version
  • Sort Method: external merge Disk: 309,096kB
24. 565.291 7,104.075 ↑ 1.0 1,749,900 1

Gather (cost=621,181.82..933,537.68 rows=1,750,345 width=163) (actual time=5,812.821..7,104.075 rows=1,749,900 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 1,214.909 6,538.784 ↑ 1.3 583,300 3 / 3

Parallel Hash Left Join (cost=620,181.82..757,503.18 rows=729,310 width=163) (actual time=5,793.653..6,538.784 rows=583,300 loops=3)

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

Parallel Seq Scan on command_latency l (cost=0.00..50,010.38 rows=729,310 width=133) (actual time=0.043..248.694 rows=583,295 loops=3)

  • Filter: (command = 'PLACE_MARKET_ORDER'::text)
  • Rows Removed by Filter: 0
27. 2,611.887 5,075.181 ↑ 1.3 2,796,689 3 / 3

Parallel Hash (cost=525,246.81..525,246.81 rows=3,496,881 width=93) (actual time=5,075.181..5,075.181 rows=2,796,689 loops=3)

  • Buckets: 65,536 Batches: 256 Memory Usage: 4,800kB
28. 2,463.294 2,463.294 ↑ 1.3 2,796,689 3 / 3

Parallel Seq Scan on session s_1 (cost=0.00..525,246.81 rows=3,496,881 width=93) (actual time=558.840..2,463.294 rows=2,796,689 loops=3)

29. 1,108.659 4,431.390 ↓ 3.9 3,266,360 1

Materialize (cost=124,564.74..128,761.00 rows=839,251 width=96) (actual time=2,321.435..4,431.390 rows=3,266,360 loops=1)

30. 2,689.750 3,322.731 ↓ 2.0 1,716,863 1

Sort (cost=124,564.74..126,662.87 rows=839,251 width=96) (actual time=2,321.428..3,322.731 rows=1,716,863 loops=1)

  • Sort Key: first_session_1.device_id, first_session_1.first_app_version_id
  • Sort Method: external merge Disk: 157,312kB
31. 632.981 632.981 ↓ 2.0 1,716,924 1

CTE Scan on first_session first_session_1 (cost=0.00..16,785.02 rows=839,251 width=96) (actual time=0.067..632.981 rows=1,716,924 loops=1)

32. 3,139.926 3,139.926 ↑ 1.5 7,216,807 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=0.017..3,139.926 rows=7,216,807 loops=1)

33. 180.841 1,394.400 ↑ 1.0 524,021 1

Hash (cost=102,865.44..102,865.44 rows=524,673 width=11) (actual time=1,394.400..1,394.400 rows=524,021 loops=1)

  • Buckets: 262,144 Batches: 8 Memory Usage: 4,913kB
34. 214.490 1,213.559 ↑ 1.0 524,021 1

Subquery Scan on d (cost=94,925.22..102,865.44 rows=524,673 width=11) (actual time=712.660..1,213.559 rows=524,021 loops=1)

35. 285.118 999.069 ↑ 1.0 524,021 1

Hash Right Join (cost=94,925.22..97,618.71 rows=524,673 width=3,512) (actual time=712.656..999.069 rows=524,021 loops=1)

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

CTE aff_goals

37. 3.129 11.108 ↓ 1.1 4,556 1

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

  • Sort Key: g.user_id
  • Sort Method: quicksort Memory: 415kB
38. 5.025 7.979 ↓ 1.1 4,556 1

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

  • Group Key: g.user_id
39. 2.954 2.954 ↑ 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..2.954 rows=7,157 loops=1)

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

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

41. 185.534 700.354 ↑ 1.0 524,021 1

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

  • Buckets: 262,144 Batches: 8 Memory Usage: 4,710kB
42. 514.820 514.820 ↑ 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.019..514.820 rows=524,021 loops=1)

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