explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mvgCN

Settings
# exclusive inclusive rows x rows loops node
1. 261,356.050 261,356.050 ↓ 2.3 11,194,367 1

CTE Scan on main (cost=8,062,276.62..8,232,555.19 rows=4,865,102 width=339) (actual time=143,595.920..261,356.050 rows=11,194,367 loops=1)

  • Functions: 236
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 51.352 ms, Inlining 230.600 ms, Optimization 3903.540 ms, Emission 2705.327 ms, Total 6890.819 ms
2.          

CTE first_session

3. 2,709.098 53,186.142 ↓ 2.0 1,717,005 1

Unique (cost=2,043,563.23..2,106,511.58 rows=839,311 width=96) (actual time=43,795.042..53,186.142 rows=1,717,005 loops=1)

4. 45,936.952 50,477.044 ↑ 1.0 8,390,664 1

Sort (cost=2,043,563.23..2,064,546.01 rows=8,393,113 width=96) (actual time=43,795.039..50,477.044 rows=8,390,664 loops=1)

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

Seq Scan on session (cost=0.00..574,244.13 rows=8,393,113 width=96) (actual time=0.029..4,540.092 rows=8,390,664 loops=1)

6.          

CTE main

7. 4,765.315 230,716.915 ↓ 2.3 11,194,367 1

Append (cost=3,775,063.06..5,955,765.04 rows=4,865,102 width=211) (actual time=143,595.902..230,716.915 rows=11,194,367 loops=1)

8. 8,439.238 191,446.770 ↓ 3.0 9,444,326 1

Merge Join (cost=3,775,063.06..3,968,479.34 rows=3,114,629 width=211) (actual time=143,595.901..191,446.770 rows=9,444,326 loops=1)

  • Merge Cond: (s.device_id = etl_tbl_all_capital_devices.device_id)
9. 10,252.460 167,665.220 ↓ 3.0 9,444,533 1

Merge Left Join (cost=3,774,413.55..3,809,832.78 rows=3,114,629 width=164) (actual time=143,594.688..167,665.220 rows=9,444,533 loops=1)

  • Merge Cond: ((s.device_id = first_session.device_id) AND (s.app_version = first_session.first_app_version_id))
10. 58,589.424 97,428.171 ↓ 3.0 9,444,533 1

Sort (cost=3,649,839.22..3,657,625.79 rows=3,114,629 width=132) (actual time=87,519.274..97,428.171 rows=9,444,533 loops=1)

  • Sort Key: s.device_id, s.app_version
  • Sort Method: external merge Disk: 1,327,696kB
11. 2,096.716 38,838.747 ↓ 3.0 9,444,533 1

Gather (cost=603,922.81..3,064,501.39 rows=3,114,629 width=132) (actual time=34,295.465..38,838.747 rows=9,444,533 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 5,117.299 36,742.031 ↓ 2.4 3,148,178 3 / 3

Parallel Hash Join (cost=602,922.81..2,752,038.49 rows=1,297,762 width=132) (actual time=34,284.259..36,742.031 rows=3,148,178 loops=3)

  • Hash Cond: (m.session_id = s.session_id)
13. 23,760.812 23,760.812 ↑ 1.2 3,175,181 3 / 3

Parallel Seq Scan on metric_value m (cost=0.00..2,003,297.51 rows=3,893,287 width=83) (actual time=14.567..23,760.812 rows=3,175,181 loops=3)

  • Filter: (metric_name = ANY ('{launchTime,PlaceOrderPageEnterTime,restoreWebWorker,restoreWebWorkerWithReConnect,restoreWebWorkerWithReLogin}'::text[]))
  • Rows Removed by Filter: 19,935,747
14. 3,611.991 7,863.920 ↓ 2.4 2,745,507 3 / 3

Parallel Hash (cost=568,998.43..568,998.43 rows=1,165,710 width=105) (actual time=7,863.919..7,863.920 rows=2,745,507 loops=3)

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

Parallel Seq Scan on session s (cost=0.00..568,998.43 rows=1,165,710 width=105) (actual time=1,751.103..4,251.929 rows=2,745,507 loops=3)

  • Filter: (date(to_timestamp((("timestamp" / 1000))::double precision)) > '2017-10-01'::date)
  • Rows Removed by Filter: 51,381
16. 2,639.225 59,984.589 ↓ 11.3 9,515,327 1

Materialize (cost=124,574.33..128,770.88 rows=839,311 width=96) (actual time=56,075.353..59,984.589 rows=9,515,327 loops=1)

17. 2,915.707 57,345.364 ↓ 2.0 1,717,005 1

Sort (cost=124,574.33..126,672.61 rows=839,311 width=96) (actual time=56,075.348..57,345.364 rows=1,717,005 loops=1)

  • Sort Key: first_session.device_id, first_session.first_app_version_id
  • Sort Method: external merge Disk: 157,320kB
18. 54,429.657 54,429.657 ↓ 2.0 1,717,005 1

CTE Scan on first_session (cost=0.00..16,786.22 rows=839,311 width=96) (actual time=43,795.061..54,429.657 rows=1,717,005 loops=1)

19. 15,342.312 15,342.312 ↑ 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.819..15,342.312 rows=1,491,052 loops=1)

20. 2,480.247 34,504.830 ↑ 1.0 1,750,041 1

Hash Left Join (cost=1,519,894.81..1,914,309.17 rows=1,750,473 width=211) (actual time=17,349.311..34,504.830 rows=1,750,041 loops=1)

  • Hash Cond: (l.user_id = d.user_id)
21. 3,940.492 30,175.328 ↑ 1.0 1,750,041 1

Merge Left Join (cost=1,407,908.95..1,659,760.67 rows=1,750,473 width=171) (actual time=15,498.041..30,175.328 rows=1,750,041 loops=1)

  • Merge Cond: (l.device_id = c.device_id)
22. 2,566.435 22,088.762 ↑ 1.0 1,750,041 1

Merge Left Join (cost=1,407,908.39..1,434,405.97 rows=1,750,473 width=195) (actual time=15,497.958..22,088.762 rows=1,750,041 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,941.797 15,232.444 ↑ 1.0 1,750,041 1

Sort (cost=1,283,334.06..1,287,710.24 rows=1,750,473 width=163) (actual time=13,447.298..15,232.444 rows=1,750,041 loops=1)

  • Sort Key: l.device_id, s_1.app_version
  • Sort Method: external merge Disk: 309,144kB
24. 564.051 7,290.647 ↑ 1.0 1,750,041 1

Gather (cost=621,226.43..933,605.32 rows=1,750,473 width=163) (actual time=5,927.844..7,290.647 rows=1,750,041 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 1,433.340 6,726.596 ↑ 1.3 583,347 3 / 3

Parallel Hash Left Join (cost=620,226.43..757,558.02 rows=729,364 width=163) (actual time=5,918.881..6,726.596 rows=583,347 loops=3)

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

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

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

Parallel Hash (cost=525,284.30..525,284.30 rows=3,497,130 width=93) (actual time=4,954.672..4,954.673 rows=2,796,888 loops=3)

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

Parallel Seq Scan on session s_1 (cost=0.00..525,284.30 rows=3,497,130 width=93) (actual time=531.373..2,635.001 rows=2,796,888 loops=3)

29. 1,172.627 4,289.883 ↓ 3.9 3,266,579 1

Materialize (cost=124,574.33..128,770.88 rows=839,311 width=96) (actual time=2,050.583..4,289.883 rows=3,266,579 loops=1)

30. 2,567.835 3,117.256 ↓ 2.0 1,716,944 1

Sort (cost=124,574.33..126,672.61 rows=839,311 width=96) (actual time=2,050.580..3,117.256 rows=1,716,944 loops=1)

  • Sort Key: first_session_1.device_id, first_session_1.first_app_version_id
  • Sort Method: external merge Disk: 157,320kB
31. 549.421 549.421 ↓ 2.0 1,717,005 1

CTE Scan on first_session first_session_1 (cost=0.00..16,786.22 rows=839,311 width=96) (actual time=0.038..549.421 rows=1,717,005 loops=1)

32. 4,146.074 4,146.074 ↑ 1.5 7,216,930 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.038..4,146.074 rows=7,216,930 loops=1)

33. 207.438 1,849.255 ↑ 1.0 524,021 1

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

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

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

35. 327.099 1,402.468 ↑ 1.0 524,021 1

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

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

CTE aff_goals

37. 5.772 20.554 ↓ 1.1 4,556 1

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

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

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

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

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

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

41. 293.305 1,050.235 ↑ 1.0 524,021 1

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

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

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