explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6w9h : Optimization for: Optimization for: Optimization for: Optimization for: plan #jIEy; plan #ykVf; plan #V6mw; plan #ZAAy

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 400.056 237,897.069 ↑ 58,621,996,336,853.0 412,240 1

Merge Right Join (cost=1,763,196,283.37..543,744,511,528,900,416.00 rows=24,166,331,769,904,295,936 width=244) (actual time=236,239.861..237,897.069 rows=412,240 loops=1)

  • Merge Cond: (first_game_mode.epic_id = regs.epic_id)
2.          

CTE regs

3. 766.314 766.314 ↓ 1.1 402,669 1

Seq Scan on epic_auth_registrations reg (cost=0.00..37,907.58 rows=363,865 width=80) (actual time=0.034..766.314 rows=402,669 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 1,147,116
4.          

CTE last_step

5. 478.484 478.484 ↓ 1.6 402,669 1

Seq Scan on users (cost=0.00..74,094.35 rows=244,940 width=64) (actual time=0.014..478.484 rows=402,669 loops=1)

  • Filter: (registration_timestamp > (now() - '90 days'::interval))
  • Rows Removed by Filter: 1,147,116
6.          

CTE max_match_1_players

7. 291.238 1,925.929 ↑ 8.0 93,339 1

Merge Join (cost=116,880.00..161,459.59 rows=742,891 width=40) (actual time=1,583.866..1,925.929 rows=93,339 loops=1)

  • Merge Cond: (users_1.epic_id = regs_1.epic_id)
  • Join Filter: ((regs_1.reg_timestamp + '168:00:00'::interval) >= users_1.last_match_timestamp)
  • Rows Removed by Join Filter: 2,781
8. 480.978 1,259.785 ↓ 81.0 99,273 1

Sort (cost=75,994.23..75,997.30 rows=1,225 width=40) (actual time=1,248.567..1,259.785 rows=99,273 loops=1)

  • Sort Key: users_1.epic_id
  • Sort Method: quicksort Memory: 10,828kB
9. 778.807 778.807 ↓ 81.0 99,273 1

Seq Scan on users users_1 (cost=0.00..75,931.40 rows=1,225 width=40) (actual time=0.014..778.807 rows=99,273 loops=1)

  • Filter: ((matches = 1) AND (last_match_timestamp > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 1,450,512
10. 298.100 374.906 ↓ 1.1 402,669 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=335.289..374.906 rows=402,669 loops=1)

  • Sort Key: regs_1.epic_id
  • Sort Method: quicksort Memory: 43,747kB
11. 76.806 76.806 ↓ 1.1 402,669 1

CTE Scan on regs regs_1 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..76.806 rows=402,669 loops=1)

12.          

CTE last_quit

13. 12.713 2,149.135 ↑ 9.7 67,258 1

Subquery Scan on foo (cost=30,547,965.29..34,783,939.11 rows=651,688 width=128) (actual time=2,039.825..2,149.135 rows=67,258 loops=1)

  • Filter: (foo.rn = 1)
  • Rows Removed by Filter: 108,555
14. 87.255 2,136.422 ↑ 741.3 175,813 1

WindowAgg (cost=30,547,965.29..33,154,718.41 rows=130,337,656 width=144) (actual time=2,039.822..2,136.422 rows=175,813 loops=1)

15. 376.124 2,049.167 ↑ 741.3 175,813 1

Sort (cost=30,547,965.29..30,873,809.43 rows=130,337,656 width=136) (actual time=2,039.801..2,049.167 rows=175,813 loops=1)

  • Sort Key: player_quit.epic_id, player_quit."timestamp" DESC
  • Sort Method: quicksort Memory: 30,868kB
16. 322.297 1,673.043 ↑ 741.3 175,813 1

Merge Join (cost=67,312.04..7,888,646.01 rows=130,337,656 width=136) (actual time=1,251.425..1,673.043 rows=175,813 loops=1)

  • Merge Cond: (player_quit.epic_id = regs_2.epic_id)
  • Join Filter: ((regs_2.reg_timestamp + '168:00:00'::interval) >= player_quit."timestamp")
  • Rows Removed by Join Filter: 77,183
17. 970.936 1,018.010 ↓ 1.4 305,302 1

Sort (cost=26,426.27..26,963.58 rows=214,922 width=136) (actual time=957.048..1,018.010 rows=305,302 loops=1)

  • Sort Key: player_quit.epic_id
  • Sort Method: quicksort Memory: 55,222kB
18. 47.074 47.074 ↓ 1.4 305,302 1

Seq Scan on player_quit (cost=0.00..7,391.22 rows=214,922 width=136) (actual time=0.012..47.074 rows=305,302 loops=1)

19. 270.174 332.736 ↓ 1.6 570,970 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=294.364..332.736 rows=570,970 loops=1)

  • Sort Key: regs_2.epic_id
  • Sort Method: quicksort Memory: 43,747kB
20. 62.562 62.562 ↓ 1.1 402,669 1

CTE Scan on regs regs_2 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..62.562 rows=402,669 loops=1)

21.          

CTE customization

22. 449.813 30,993.551 ↓ 1,551.3 310,253 1

GroupAggregate (cost=685,876.33..75,672,039.50 rows=200 width=40) (actual time=25,432.013..30,993.551 rows=310,253 loops=1)

  • Group Key: char_customization_selected.epic_id
23. 1,428.605 30,543.738 ↑ 438.3 2,631,560 1

Merge Join (cost=685,876.33..69,904,377.00 rows=1,153,532,100 width=40) (actual time=25,431.971..30,543.738 rows=2,631,560 loops=1)

  • Merge Cond: (regs_3.epic_id = char_customization_selected.epic_id)
  • Join Filter: ((regs_3.reg_timestamp + '168:00:00'::interval) >= char_customization_selected."timestamp")
  • Rows Removed by Join Filter: 872,237
24. 185.046 226.946 ↓ 1.1 402,669 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=192.487..226.946 rows=402,669 loops=1)

  • Sort Key: regs_3.epic_id
  • Sort Method: quicksort Memory: 43,747kB
25. 41.900 41.900 ↓ 1.1 402,669 1

CTE Scan on regs regs_3 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..41.900 rows=402,669 loops=1)

26. 645.711 28,888.187 ↓ 2.4 4,519,828 1

Materialize (cost=644,990.56..654,501.22 rows=1,902,132 width=40) (actual time=25,239.472..28,888.187 rows=4,519,828 loops=1)

27. 21,682.585 28,242.476 ↓ 2.4 4,519,828 1

Sort (cost=644,990.56..649,745.89 rows=1,902,132 width=40) (actual time=25,239.466..28,242.476 rows=4,519,828 loops=1)

  • Sort Key: char_customization_selected.epic_id
  • Sort Method: external merge Disk: 256,520kB
28. 6,559.891 6,559.891 ↓ 2.4 4,519,828 1

Seq Scan on char_customization_selected (cost=0.00..416,883.93 rows=1,902,132 width=40) (actual time=23.908..6,559.891 rows=4,519,828 loops=1)

  • Filter: ("timestamp" > (now() - '90 days'::interval))
  • Rows Removed by Filter: 3,251,619
29.          

CTE all_unioned

30. 448.677 210,541.086 ↑ 45.0 511,265 1

Subquery Scan on all_unioned_1 (cost=1,491,497,413.33..1,640,975,940.19 rows=22,996,696 width=64) (actual time=201,810.361..210,541.086 rows=511,265 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 6,510,991
31. 4,702.472 210,092.409 ↑ 655.0 7,022,256 1

WindowAgg (cost=1,491,497,413.33..1,583,484,199.09 rows=4,599,339,288 width=76) (actual time=201,810.359..210,092.409 rows=7,022,256 loops=1)

32. 23,277.959 205,389.937 ↑ 655.0 7,022,256 1

Sort (cost=1,491,497,413.33..1,502,995,761.55 rows=4,599,339,288 width=68) (actual time=201,809.538..205,389.937 rows=7,022,256 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 462,064kB
33. 917.372 182,111.978 ↑ 655.0 7,022,256 1

Subquery Scan on foo_1 (cost=96,322.20..537,737,493.49 rows=4,599,339,288 width=68) (actual time=756.688..182,111.978 rows=7,022,256 loops=1)

34. 632.875 181,194.606 ↑ 655.0 7,022,256 1

Append (cost=96,322.20..491,744,100.61 rows=4,599,339,288 width=68) (actual time=756.686..181,194.606 rows=7,022,256 loops=1)

35. 0.224 756.981 ↓ 3.8 1,547 1

Unique (cost=96,322.20..96,324.23 rows=405 width=69) (actual time=756.683..756.981 rows=1,547 loops=1)

36. 2.581 756.757 ↓ 3.8 1,549 1

Sort (cost=96,322.20..96,323.21 rows=405 width=69) (actual time=756.682..756.757 rows=1,549 loops=1)

  • Sort Key: epic_auth_registrations.epic_id
  • Sort Method: quicksort Memory: 266kB
37. 119.768 754.176 ↓ 3.8 1,549 1

Hash Join (cost=77,602.46..96,304.66 rows=405 width=69) (actual time=638.599..754.176 rows=1,549 loops=1)

  • Hash Cond: (client_events_pre_load_tutorial.user_id = epic_auth_registrations.user_id)
38. 0.105 0.475 ↓ 3.8 1,550 1

Append (cost=0.43..411.55 rows=405 width=37) (actual time=0.037..0.475 rows=1,550 loops=1)

39. 0.370 0.370 ↓ 3.8 1,550 1

Index Scan using client_events_pre_load_tutorial_event_timestamp_idx on client_events_pre_load_tutorial (cost=0.43..409.53 rows=405 width=37) (actual time=0.036..0.370 rows=1,550 loops=1)

  • Index Cond: ((event = 'pre_load_tutorial'::text) AND ("timestamp" > (now() - '90 days'::interval)))
40. 424.629 633.933 ↑ 1.0 1,557,359 1

Hash (cost=39,830.57..39,830.57 rows=1,559,557 width=70) (actual time=633.932..633.933 rows=1,557,359 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 85,823kB
41. 209.304 209.304 ↑ 1.0 1,557,359 1

Seq Scan on epic_auth_registrations (cost=0.00..39,830.57 rows=1,559,557 width=70) (actual time=0.012..209.304 rows=1,557,359 loops=1)

42. 1,168.903 3,815.028 ↓ 2,498.5 499,699 1

HashAggregate (cost=344,631.00..344,633.00 rows=200 width=68) (actual time=3,699.846..3,815.028 rows=499,699 loops=1)

  • Group Key: logins_cleaned.epic_id, 'Login'::character varying, 2
43. 2,646.125 2,646.125 ↑ 1.3 2,289,658 1

Seq Scan on logins_cleaned (cost=0.00..322,065.88 rows=3,008,683 width=68) (actual time=0.016..2,646.125 rows=2,289,658 loops=1)

  • Filter: (event_login > (now() - '90 days'::interval))
  • Rows Removed by Filter: 5,798,175
44. 0.001 106.561 ↓ 0.0 0 1

Unique (cost=42,941.10..42,948.33 rows=200 width=68) (actual time=106.561..106.561 rows=0 loops=1)

45. 0.009 106.560 ↓ 0.0 0 1

Sort (cost=42,941.10..42,944.71 rows=1,446 width=68) (actual time=106.560..106.560 rows=0 loops=1)

  • Sort Key: map_loading_time_event.epic_id
  • Sort Method: quicksort Memory: 25kB
46. 106.551 106.551 ↓ 0.0 0 1

Seq Scan on map_loading_time_event (cost=0.00..42,865.20 rows=1,446 width=68) (actual time=106.551..106.551 rows=0 loops=1)

  • Filter: ((map_name = '/Game/Maps/Meta/Outpost/Outpost_P'::text) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 964,528
47. 0.001 303.104 ↓ 0.0 0 1

Unique (cost=134,657.40..134,684.22 rows=200 width=68) (actual time=303.104..303.104 rows=0 loops=1)

48. 0.006 303.103 ↓ 0.0 0 1

Sort (cost=134,657.40..134,670.81 rows=5,364 width=68) (actual time=303.103..303.103 rows=0 loops=1)

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
49. 303.097 303.097 ↓ 0.0 0 1

Seq Scan on map_loading_time_event_client (cost=0.00..134,325.12 rows=5,364 width=68) (actual time=303.097..303.097 rows=0 loops=1)

  • Filter: ((map_name = '/Game/Maps/Tutorial/Tut_Walkthrough_P'::text) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 3,318,640
50. 31,113.445 31,113.445 ↓ 1,551.3 310,253 1

CTE Scan on customization (cost=0.00..4.00 rows=200 width=68) (actual time=25,432.016..31,113.445 rows=310,253 loops=1)

51. 38.709 625.158 ↓ 1.6 402,669 1

Subquery Scan on *SELECT* 6 (cost=0.00..7,348.20 rows=244,940 width=68) (actual time=0.017..625.158 rows=402,669 loops=1)

52. 586.449 586.449 ↓ 1.6 402,669 1

CTE Scan on last_step (cost=0.00..4,898.80 rows=244,940 width=68) (actual time=0.016..586.449 rows=402,669 loops=1)

53. 28.610 1,333.580 ↓ 401.6 80,316 1

Group (cost=84,102.11..7,144,500.30 rows=200 width=68) (actual time=1,037.122..1,333.580 rows=80,316 loops=1)

  • Group Key: client_disconnected.epic_id, 'Connection Timeout'::character varying, 16
54. 209.731 1,304.970 ↑ 737.7 141,777 1

Merge Join (cost=84,102.11..6,360,107.42 rows=104,585,717 width=68) (actual time=1,037.119..1,304.970 rows=141,777 loops=1)

  • Merge Cond: (client_disconnected.epic_id = regs_4.epic_id)
  • Join Filter: ((regs_4.reg_timestamp + '168:00:00'::interval) >= client_disconnected."timestamp")
  • Rows Removed by Join Filter: 31,393
55. 766.989 875.059 ↓ 1.2 215,073 1

Sort (cost=43,216.34..43,647.49 rows=172,458 width=40) (actual time=843.940..875.059 rows=215,073 loops=1)

  • Sort Key: client_disconnected.epic_id
  • Sort Method: quicksort Memory: 22,947kB
56. 108.070 108.070 ↓ 1.2 215,073 1

Seq Scan on client_disconnected (cost=0.00..28,216.04 rows=172,458 width=40) (actual time=5.170..108.070 rows=215,073 loops=1)

  • Filter: ("timestamp" > (now() - '90 days'::interval))
  • Rows Removed by Filter: 17,738
57. 169.185 220.180 ↓ 1.3 489,073 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=193.147..220.180 rows=489,073 loops=1)

  • Sort Key: regs_4.epic_id
  • Sort Method: quicksort Memory: 43,747kB
58. 50.995 50.995 ↓ 1.1 402,669 1

CTE Scan on regs regs_4 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.002..50.995 rows=402,669 loops=1)

59. 4.422 452.364 ↓ 72.0 14,409 1

Group (cost=65,619.45..4,889,721.31 rows=200 width=68) (actual time=311.544..452.364 rows=14,409 loops=1)

  • Group Key: client_disconnected_client.epic_id, 'Client Disconnect'::character varying, 17
60. 113.363 447.942 ↑ 3,352.4 21,316 1

Merge Join (cost=65,619.45..4,353,775.46 rows=71,459,447 width=68) (actual time=311.542..447.942 rows=21,316 loops=1)

  • Merge Cond: (client_disconnected_client.epic_id = regs_5.epic_id)
  • Join Filter: ((regs_5.reg_timestamp + '168:00:00'::interval) >= client_disconnected_client."timestamp")
  • Rows Removed by Join Filter: 4,012
61. 66.369 113.156 ↑ 3.7 32,020 1

Sort (cost=24,733.68..25,028.26 rows=117,834 width=40) (actual time=109.251..113.156 rows=32,020 loops=1)

  • Sort Key: client_disconnected_client.epic_id
  • Sort Method: quicksort Memory: 3,270kB
62. 46.787 46.787 ↑ 3.7 32,020 1

Seq Scan on client_disconnected_client (cost=0.00..14,808.28 rows=117,834 width=40) (actual time=29.467..46.787 rows=32,020 loops=1)

  • Filter: ("timestamp" > (now() - '90 days'::interval))
  • Rows Removed by Filter: 129,426
63. 177.226 221.423 ↓ 1.1 411,611 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=202.244..221.423 rows=411,611 loops=1)

  • Sort Key: regs_5.epic_id
  • Sort Method: quicksort Memory: 43,747kB
64. 44.197 44.197 ↓ 1.1 402,669 1

CTE Scan on regs regs_5 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.002..44.197 rows=402,669 loops=1)

65. 36.239 417.680 ↓ 12.3 374,263 1

Subquery Scan on *SELECT* 9 (cost=11,071.14..12,059.82 rows=30,421 width=68) (actual time=278.121..417.680 rows=374,263 loops=1)

66. 286.598 381.441 ↓ 12.3 374,263 1

HashAggregate (cost=11,071.14..11,451.40 rows=30,421 width=68) (actual time=278.119..381.441 rows=374,263 loops=1)

  • Group Key: last_button.epic_id, concat('Button after tutorial: ', last_button.button_name), 18
67. 94.843 94.843 ↓ 1.2 374,265 1

Seq Scan on last_button (cost=0.00..8,789.59 rows=304,207 width=68) (actual time=0.024..94.843 rows=374,265 loops=1)

68. 308.874 25,547.202 ↓ 721.9 144,370 1

Group (cost=1,018,674.21..127,997,854.35 rows=200 width=68) (actual time=20,844.360..25,547.202 rows=144,370 loops=1)

  • Group Key: blueprint_crafted.epic_id
69. 1,213.607 25,238.328 ↑ 803.5 2,528,424 1

Merge Join (cost=1,018,674.21..122,919,094.91 rows=2,031,503,778 width=32) (actual time=20,844.357..25,238.328 rows=2,528,424 loops=1)

  • Merge Cond: (regs_6.epic_id = blueprint_crafted.epic_id)
  • Join Filter: ((regs_6.reg_timestamp + '168:00:00'::interval) >= blueprint_crafted.event)
  • Rows Removed by Join Filter: 486,931
70. 176.955 218.780 ↓ 1.1 402,668 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=183.369..218.780 rows=402,668 loops=1)

  • Sort Key: regs_6.epic_id
  • Sort Method: quicksort Memory: 43,747kB
71. 41.825 41.825 ↓ 1.1 402,669 1

CTE Scan on regs regs_6 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..41.825 rows=402,669 loops=1)

72. 502.352 23,805.941 ↓ 1.1 3,714,214 1

Materialize (cost=977,788.44..994,537.82 rows=3,349,875 width=40) (actual time=20,660.952..23,805.941 rows=3,714,214 loops=1)

73. 14,639.596 23,303.589 ↓ 1.1 3,714,214 1

Sort (cost=977,788.44..986,163.13 rows=3,349,875 width=40) (actual time=20,660.947..23,303.589 rows=3,714,214 loops=1)

  • Sort Key: blueprint_crafted.epic_id
  • Sort Method: external merge Disk: 210,800kB
74. 8,663.993 8,663.993 ↓ 1.1 3,714,214 1

Seq Scan on blueprint_crafted (cost=0.00..562,392.42 rows=3,349,875 width=40) (actual time=6,372.069..8,663.993 rows=3,714,214 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,942,116
75. 1,723.482 34,956.229 ↑ 1,150.4 2,967,599 1

Merge Join (cost=1,346,717.23..206,191,933.43 rows=3,413,822,062 width=68) (actual time=28,942.712..34,956.229 rows=2,967,599 loops=1)

  • Merge Cond: (regs_7.epic_id = matchmaking_started.epic_id)
  • Join Filter: ((regs_7.reg_timestamp + '168:00:00'::interval) >= matchmaking_started.event)
  • Rows Removed by Join Filter: 874,267
76. 202.304 259.303 ↓ 1.1 402,669 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=225.253..259.303 rows=402,669 loops=1)

  • Sort Key: regs_7.epic_id
  • Sort Method: quicksort Memory: 43,747kB
77. 56.999 56.999 ↓ 1.1 402,669 1

CTE Scan on regs regs_7 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.004..56.999 rows=402,669 loops=1)

78. 714.763 32,973.444 ↑ 1.1 4,968,025 1

Materialize (cost=1,305,831.46..1,333,977.80 rows=5,629,267 width=40) (actual time=28,717.447..32,973.444 rows=4,968,025 loops=1)

79. 23,930.814 32,258.681 ↑ 1.1 4,968,025 1

Sort (cost=1,305,831.46..1,319,904.63 rows=5,629,267 width=40) (actual time=28,717.442..32,258.681 rows=4,968,025 loops=1)

  • Sort Key: matchmaking_started.epic_id
  • Sort Method: external merge Disk: 281,952kB
80. 8,327.867 8,327.867 ↑ 1.1 4,968,025 1

Seq Scan on matchmaking_started (cost=0.00..586,705.54 rows=5,629,267 width=40) (actual time=5,779.087..8,327.867 rows=4,968,025 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 13,683,135
81. 619.063 6,335.115 ↑ 1,158.2 621,876 1

Merge Join (cost=287,850.67..43,504,169.50 rows=720,241,658 width=68) (actual time=5,494.679..6,335.115 rows=621,876 loops=1)

  • Merge Cond: (regs_8.epic_id = matchmaking_cancelled.epic_id)
  • Join Filter: ((regs_8.reg_timestamp + '168:00:00'::interval) >= matchmaking_cancelled.event)
  • Rows Removed by Join Filter: 164,563
82. 171.884 213.867 ↓ 1.1 402,668 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=172.675..213.867 rows=402,668 loops=1)

  • Sort Key: regs_8.epic_id
  • Sort Method: quicksort Memory: 43,747kB
83. 41.983 41.983 ↓ 1.1 402,669 1

CTE Scan on regs regs_8 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..41.983 rows=402,669 loops=1)

84. 4,604.326 5,502.185 ↑ 1.2 992,970 1

Sort (cost=246,964.90..249,934.03 rows=1,187,652 width=40) (actual time=5,321.982..5,502.185 rows=992,970 loops=1)

  • Sort Key: matchmaking_cancelled.epic_id
  • Sort Method: quicksort Memory: 101,774kB
85. 897.859 897.859 ↑ 1.2 992,970 1

Seq Scan on matchmaking_cancelled (cost=0.00..127,132.71 rows=1,187,652 width=40) (actual time=604.209..897.859 rows=992,970 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 2,906,860
86. 496.668 9,563.873 ↑ 36.8 312,868 1

Merge Join (cost=596,237.96..1,288,040.37 rows=11,528,456 width=68) (actual time=9,008.093..9,563.873 rows=312,868 loops=1)

  • Merge Cond: (player_matched.epic_id = regs_9.epic_id)
  • Join Filter: ((regs_9.reg_timestamp + '168:00:00'::interval) >= player_matched.event)
  • Rows Removed by Join Filter: 4,934
87. 1,567.264 8,722.473 ↓ 17.1 325,044 1

Sort (cost=555,352.19..555,399.71 rows=19,010 width=40) (actual time=8,695.869..8,722.473 rows=325,044 loops=1)

  • Sort Key: player_matched.epic_id
  • Sort Method: quicksort Memory: 37,683kB
88. 7,155.209 7,155.209 ↓ 17.1 325,044 1

Seq Scan on player_matched (cost=0.00..554,001.10 rows=19,010 width=40) (actual time=0.636..7,155.209 rows=325,044 loops=1)

  • Filter: ((battle_matched_num = 1) AND (event > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 14,439,397
89. 270.780 344.732 ↓ 1.1 402,669 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=312.210..344.732 rows=402,669 loops=1)

  • Sort Key: regs_9.epic_id
  • Sort Method: quicksort Memory: 43,747kB
90. 73.952 73.952 ↓ 1.1 402,669 1

CTE Scan on regs regs_9 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..73.952 rows=402,669 loops=1)

91. 336.775 10,353.239 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=9,975.575..10,353.239 rows=311,944 loops=1)

  • Merge Cond: (match_summary.epic_id = regs_10.epic_id)
  • Join Filter: ((regs_10.reg_timestamp + '168:00:00'::interval) >= match_summary."timestamp")
  • Rows Removed by Join Filter: 5,032
92. 1,681.537 9,656.817 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=9,638.213..9,656.817 rows=324,618 loops=1)

  • Sort Key: match_summary.epic_id
  • Sort Method: quicksort Memory: 37,649kB
93. 7,975.280 7,975.280 ↓ 16.7 324,618 1

Seq Scan on match_summary (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.962..7,975.280 rows=324,618 loops=1)

  • Filter: ((battle_num = 1) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 13,892,421
94. 280.378 359.647 ↓ 1.1 404,492 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=337.350..359.647 rows=404,492 loops=1)

  • Sort Key: regs_10.epic_id
  • Sort Method: quicksort Memory: 43,747kB
95. 79.269 79.269 ↓ 1.1 402,669 1

CTE Scan on regs regs_10 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..79.269 rows=402,669 loops=1)

96. 481.675 12,569.667 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=68) (actual time=12,013.411..12,569.667 rows=267,940 loops=1)

  • Merge Cond: (match_summary_1.epic_id = regs_11.epic_id)
  • Join Filter: ((regs_11.reg_timestamp + '168:00:00'::interval) >= match_summary_1."timestamp")
  • Rows Removed by Join Filter: 3,898
97. 1,520.181 11,715.402 ↓ 28.5 278,144 1

Sort (cost=765,965.23..765,989.59 rows=9,744 width=40) (actual time=11,684.563..11,715.402 rows=278,144 loops=1)

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 34,019kB
98. 10,195.221 10,195.221 ↓ 28.5 278,144 1

Seq Scan on match_summary match_summary_1 (cost=0.00..765,319.68 rows=9,744 width=40) (actual time=0.064..10,195.221 rows=278,144 loops=1)

  • Filter: ((NOT match_left) AND (battle_num = 1) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 13,938,895
99. 296.743 372.590 ↓ 1.1 404,128 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=328.830..372.590 rows=404,128 loops=1)

  • Sort Key: regs_11.epic_id
  • Sort Method: quicksort Memory: 43,747kB
100. 75.847 75.847 ↓ 1.1 402,669 1

CTE Scan on regs regs_11 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.004..75.847 rows=402,669 loops=1)

101. 272.437 4,353.965 ↑ 25,343.5 14,862 1

Merge Join (cost=123,034.32..20,840,547.57 rows=376,654,404 width=68) (actual time=4,003.383..4,353.965 rows=14,862 loops=1)

  • Merge Cond: (last_button_1.epic_id = max_match_1_players.epic_id)
  • Join Filter: (last_button_1."timestamp" > max_match_1_players.match_start_time)
  • Rows Removed by Join Filter: 78,381
102. 1,976.802 2,053.556 ↓ 1.2 374,265 1

Sort (cost=35,734.26..36,494.77 rows=304,207 width=72) (actual time=1,984.456..2,053.556 rows=374,265 loops=1)

  • Sort Key: last_button_1.epic_id
  • Sort Method: quicksort Memory: 60,134kB
103. 76.754 76.754 ↓ 1.2 374,265 1

Seq Scan on last_button last_button_1 (cost=0.00..8,029.07 rows=304,207 width=72) (actual time=0.014..76.754 rows=374,265 loops=1)

104. 62.530 2,027.972 ↑ 8.0 93,340 1

Sort (cost=87,300.06..89,157.29 rows=742,891 width=40) (actual time=2,018.883..2,027.972 rows=93,340 loops=1)

  • Sort Key: max_match_1_players.epic_id
  • Sort Method: quicksort Memory: 10,365kB
105. 1,965.442 1,965.442 ↑ 8.0 93,339 1

CTE Scan on max_match_1_players (cost=0.00..14,857.82 rows=742,891 width=40) (actual time=1,583.869..1,965.442 rows=93,339 loops=1)

106. 268.585 7,458.174 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,155.300..7,458.174 rows=213,078 loops=1)

  • Merge Cond: (match_summary_2.epic_id = regs_12.epic_id)
  • Join Filter: ((regs_12.reg_timestamp + '168:00:00'::interval) >= match_summary_2."timestamp")
  • Rows Removed by Join Filter: 7,265
107. 1,056.925 6,823.502 ↓ 11.9 232,241 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,810.720..6,823.502 rows=232,241 loops=1)

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 24,288kB
108. 5,766.577 5,766.577 ↓ 11.9 232,241 1

Seq Scan on match_summary match_summary_2 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.056..5,766.577 rows=232,241 loops=1)

  • Filter: ((battle_num = 2) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 13,984,798
109. 284.840 366.087 ↓ 1.1 403,979 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=344.567..366.087 rows=403,979 loops=1)

  • Sort Key: regs_12.epic_id
  • Sort Method: quicksort Memory: 43,747kB
110. 81.247 81.247 ↓ 1.1 402,669 1

CTE Scan on regs regs_12 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..81.247 rows=402,669 loops=1)

111. 212.399 8,306.802 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=8,065.470..8,306.802 rows=164,729 loops=1)

  • Merge Cond: (match_summary_3.epic_id = regs_13.epic_id)
  • Join Filter: ((regs_13.reg_timestamp + '168:00:00'::interval) >= match_summary_3."timestamp")
  • Rows Removed by Join Filter: 7,473
112. 575.235 7,894.799 ↓ 9.5 185,195 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=7,885.394..7,894.799 rows=185,195 loops=1)

  • Sort Key: match_summary_3.epic_id
  • Sort Method: quicksort Memory: 20,613kB
113. 7,319.564 7,319.564 ↓ 9.5 185,195 1

Seq Scan on match_summary match_summary_3 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.060..7,319.564 rows=185,195 loops=1)

  • Filter: ((battle_num = 3) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 14,031,844
114. 157.302 199.604 ↓ 1.1 403,680 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=180.063..199.604 rows=403,680 loops=1)

  • Sort Key: regs_13.epic_id
  • Sort Method: quicksort Memory: 43,747kB
115. 42.302 42.302 ↓ 1.1 402,669 1

CTE Scan on regs regs_13 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..42.302 rows=402,669 loops=1)

116. 191.064 8,034.947 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,816.160..8,034.947 rows=136,273 loops=1)

  • Merge Cond: (match_summary_4.epic_id = regs_14.epic_id)
  • Join Filter: ((regs_14.reg_timestamp + '168:00:00'::interval) >= match_summary_4."timestamp")
  • Rows Removed by Join Filter: 7,395
117. 506.784 7,612.046 ↓ 8.0 156,775 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=7,604.140..7,612.046 rows=156,775 loops=1)

  • Sort Key: match_summary_4.epic_id
  • Sort Method: quicksort Memory: 18,393kB
118. 7,105.262 7,105.262 ↓ 8.0 156,775 1

Seq Scan on match_summary match_summary_4 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.067..7,105.262 rows=156,775 loops=1)

  • Filter: ((battle_num = 4) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 14,060,264
119. 186.577 231.837 ↓ 1.1 403,488 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=212.006..231.837 rows=403,488 loops=1)

  • Sort Key: regs_14.epic_id
  • Sort Method: quicksort Memory: 43,747kB
120. 45.260 45.260 ↓ 1.1 402,669 1

CTE Scan on regs regs_14 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..45.260 rows=402,669 loops=1)

121. 331.867 7,127.525 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=6,740.944..7,127.525 rows=116,710 loops=1)

  • Merge Cond: (match_summary_5.epic_id = regs_15.epic_id)
  • Join Filter: ((regs_15.reg_timestamp + '168:00:00'::interval) >= match_summary_5."timestamp")
  • Rows Removed by Join Filter: 7,339
122. 699.469 6,419.491 ↓ 7.0 137,030 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,405.304..6,419.491 rows=137,030 loops=1)

  • Sort Key: match_summary_5.epic_id
  • Sort Method: quicksort Memory: 16,850kB
123. 5,720.022 5,720.022 ↓ 7.0 137,030 1

Seq Scan on match_summary match_summary_5 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.060..5,720.022 rows=137,030 loops=1)

  • Filter: ((battle_num = 5) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 14,080,009
124. 297.604 376.167 ↓ 1.1 403,403 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=335.626..376.167 rows=403,403 loops=1)

  • Sort Key: regs_15.epic_id
  • Sort Method: quicksort Memory: 43,747kB
125. 78.563 78.563 ↓ 1.1 402,669 1

CTE Scan on regs regs_15 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..78.563 rows=402,669 loops=1)

126. 151.664 7,031.092 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=6,856.507..7,031.092 rows=66,851 loops=1)

  • Merge Cond: (match_summary_6.epic_id = regs_16.epic_id)
  • Join Filter: ((regs_16.reg_timestamp + '168:00:00'::interval) >= match_summary_6."timestamp")
  • Rows Removed by Join Filter: 6,858
127. 240.517 6,671.920 ↓ 4.4 85,379 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,667.669..6,671.920 rows=85,379 loops=1)

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 9,743kB
128. 6,431.403 6,431.403 ↓ 4.4 85,379 1

Seq Scan on match_summary match_summary_6 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.058..6,431.403 rows=85,379 loops=1)

  • Filter: ((battle_num = 10) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 14,131,660
129. 165.688 207.508 ↓ 1.1 403,056 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=40) (actual time=188.823..207.508 rows=403,056 loops=1)

  • Sort Key: regs_16.epic_id
  • Sort Method: quicksort Memory: 43,747kB
130. 41.820 41.820 ↓ 1.1 402,669 1

CTE Scan on regs regs_16 (cost=0.00..7,277.30 rows=363,865 width=40) (actual time=0.003..41.820 rows=402,669 loops=1)

131.          

CTE logins

132. 1,752.212 5,942.638 ↓ 2,498.5 499,699 1

HashAggregate (cost=352,152.71..352,154.71 rows=200 width=40) (actual time=5,751.308..5,942.638 rows=499,699 loops=1)

  • Group Key: logins_cleaned_1.epic_id
133. 4,190.426 4,190.426 ↑ 1.3 2,289,658 1

Seq Scan on logins_cleaned logins_cleaned_1 (cost=0.00..322,065.88 rows=3,008,683 width=48) (actual time=0.013..4,190.426 rows=2,289,658 loops=1)

  • Filter: (event_login > (now() - '90 days'::interval))
  • Rows Removed by Filter: 5,798,175
134.          

CTE first_game_mode

135. 463.829 14,108.914 ↑ 111.8 316,976 1

Merge Join (cost=807,593.92..1,339,489.15 rows=35,453,186 width=64) (actual time=13,571.897..14,108.914 rows=316,976 loops=1)

  • Merge Cond: (match_summary_7.epic_id = regs_17.epic_id)
136. 1,853.005 12,369.942 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=64) (actual time=12,336.896..12,369.942 rows=324,618 loops=1)

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 37,649kB
137. 10,516.937 10,516.937 ↓ 16.7 324,618 1

Seq Scan on match_summary match_summary_7 (cost=0.00..765,319.68 rows=19,487 width=64) (actual time=0.571..10,516.937 rows=324,618 loops=1)

  • Filter: ((battle_num = 1) AND ("timestamp" > (now() - '90 days'::interval)))
  • Rows Removed by Filter: 13,892,421
138. 280.417 1,275.143 ↓ 1.1 404,492 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=32) (actual time=1,234.988..1,275.143 rows=404,492 loops=1)

  • Sort Key: regs_17.epic_id
  • Sort Method: quicksort Memory: 43,747kB
139. 994.726 994.726 ↓ 1.1 402,669 1

CTE Scan on regs regs_17 (cost=0.00..7,277.30 rows=363,865 width=32) (actual time=0.040..994.726 rows=402,669 loops=1)

140. 236.071 14,503.376 ↑ 111.8 316,976 1

Sort (cost=5,916,479.00..6,005,111.97 rows=35,453,186 width=64) (actual time=14,480.311..14,503.376 rows=316,976 loops=1)

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
141. 14,267.305 14,267.305 ↑ 111.8 316,976 1

CTE Scan on first_game_mode (cost=0.00..709,063.72 rows=35,453,186 width=64) (actual time=13,571.902..14,267.305 rows=316,976 loops=1)

142. 80.828 222,993.637 ↑ 330,700,864.7 412,240 1

Materialize (cost=3,882,780.20..2,385,766,874,186.66 rows=136,328,124,473,238 width=208) (actual time=221,759.540..222,993.637 rows=412,240 loops=1)

143. 362.697 222,912.809 ↑ 332,393,309.8 410,141 1

Merge Left Join (cost=3,882,780.20..2,044,946,563,003.57 rows=136,328,124,473,238 width=208) (actual time=221,759.535..222,912.809 rows=410,141 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
144. 171.831 11,551.651 ↑ 2,959.4 400,639 1

Merge Left Join (cost=116,864.10..17,907,716.81 rows=1,185,632,271 width=176) (actual time=10,859.070..11,551.651 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
145. 394.712 9,169.052 ↓ 1.1 400,639 1

Merge Join (cost=40,897.41..46,356.39 rows=363,865 width=80) (actual time=8,654.882..9,169.052 rows=400,639 loops=1)

  • Merge Cond: (logins.epic_id = regs.epic_id)
146. 2,342.155 8,480.053 ↓ 2,498.5 499,699 1

Sort (cost=11.64..12.14 rows=200 width=40) (actual time=8,394.026..8,480.053 rows=499,699 loops=1)

  • Sort Key: logins.epic_id
  • Sort Method: quicksort Memory: 51,327kB
147. 6,137.898 6,137.898 ↓ 2,498.5 499,699 1

CTE Scan on logins (cost=0.00..4.00 rows=200 width=40) (actual time=5,751.311..6,137.898 rows=499,699 loops=1)

148. 248.180 294.287 ↓ 1.1 402,669 1

Sort (cost=40,885.77..41,795.43 rows=363,865 width=72) (actual time=260.847..294.287 rows=402,669 loops=1)

  • Sort Key: regs.epic_id
  • Sort Method: quicksort Memory: 68,914kB
149. 46.107 46.107 ↓ 1.1 402,669 1

CTE Scan on regs (cost=0.00..7,277.30 rows=363,865 width=72) (actual time=0.002..46.107 rows=402,669 loops=1)

150. 36.609 2,210.768 ↑ 9.7 67,258 1

Sort (cost=75,966.69..77,595.91 rows=651,688 width=128) (actual time=2,204.181..2,210.768 rows=67,258 loops=1)

  • Sort Key: last_quit.epic_id
  • Sort Method: quicksort Memory: 12,531kB
151. 2,174.159 2,174.159 ↑ 9.7 67,258 1

CTE Scan on last_quit (cost=0.00..13,033.76 rows=651,688 width=128) (actual time=2,039.829..2,174.159 rows=67,258 loops=1)

152. 61.415 210,998.461 ↑ 45.0 511,265 1

Materialize (cost=3,765,916.10..3,880,899.58 rows=22,996,696 width=64) (actual time=210,900.458..210,998.461 rows=511,265 loops=1)

153. 248.150 210,937.046 ↑ 45.0 511,265 1

Sort (cost=3,765,916.10..3,823,407.84 rows=22,996,696 width=64) (actual time=210,900.455..210,937.046 rows=511,265 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 76,818kB
154. 210,688.896 210,688.896 ↑ 45.0 511,265 1

CTE Scan on all_unioned (cost=0.00..459,933.92 rows=22,996,696 width=64) (actual time=201,810.363..210,688.896 rows=511,265 loops=1)

Planning time : 3.593 ms
Execution time : 238,005.073 ms