explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ykVf : Optimization for: plan #jIEy

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 418.167 192,859.099 ↑ 4,968,874,843.3 402,463 1

Merge Right Join (cost=683,769,565.27..44,996,089,444,426.42 rows=1,999,788,276,057,596 width=244) (actual time=191,191.754..192,859.099 rows=402,463 loops=1)

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

CTE regs

3. 418.453 418.453 ↓ 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.024..418.453 rows=402,669 loops=1)

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

CTE last_step

5. 486.322 486.322 ↓ 1.6 402,669 1

Seq Scan on users (cost=0.00..74,094.35 rows=244,940 width=64) (actual time=0.022..486.322 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. 1,612.989 15,497.519 ↓ 1,564.3 312,868 1

HashAggregate (cost=156,692,898.65..156,693,398.65 rows=200 width=40) (actual time=14,943.984..15,497.519 rows=312,868 loops=1)

  • Group Key: player_matched.epic_id, player_matched.event
  • Filter: (max(player_matched.battle_matched_num) = 1)
  • Rows Removed by Filter: 2,035,690
8. 917.704 13,884.530 ↑ 981.7 2,348,558 1

Merge Join (cost=1,048,727.18..139,400,360.06 rows=2,305,671,811 width=48) (actual time=10,730.278..13,884.530 rows=2,348,558 loops=1)

  • Merge Cond: (regs_1.epic_id = player_matched.epic_id)
  • Join Filter: ((regs_1.reg_timestamp + '168:00:00'::interval) >= player_matched.event)
  • Rows Removed by Join Filter: 711,405
9. 170.849 214.719 ↓ 1.1 402,669 1

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

  • Sort Key: regs_1.epic_id
  • Sort Method: quicksort Memory: 43,747kB
10. 43.870 43.870 ↓ 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.002..43.870 rows=402,669 loops=1)

11. 377.412 12,752.107 ↓ 1.0 3,980,882 1

Materialize (cost=1,007,841.41..1,026,851.25 rows=3,801,968 width=48) (actual time=10,550.307..12,752.107 rows=3,980,882 loops=1)

12. 6,756.065 12,374.695 ↓ 1.0 3,980,882 1

Sort (cost=1,007,841.41..1,017,346.33 rows=3,801,968 width=48) (actual time=10,550.301..12,374.695 rows=3,980,882 loops=1)

  • Sort Key: player_matched.epic_id
  • Sort Method: external merge Disk: 257,112kB
13. 5,618.630 5,618.630 ↓ 1.0 3,980,882 1

Seq Scan on player_matched (cost=0.00..525,486.34 rows=3,801,968 width=48) (actual time=0.302..5,618.630 rows=3,980,882 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,783,559
14.          

CTE last_quit

15. 12.499 1,837.395 ↑ 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=1,728.422..1,837.395 rows=67,258 loops=1)

  • Filter: (foo.rn = 1)
  • Rows Removed by Filter: 108,555
16. 86.944 1,824.896 ↑ 741.3 175,813 1

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

17. 316.486 1,737.952 ↑ 741.3 175,813 1

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

  • Sort Key: player_quit.epic_id, player_quit."timestamp" DESC
  • Sort Method: quicksort Memory: 30,868kB
18. 244.028 1,421.466 ↑ 741.3 175,813 1

Merge Join (cost=67,312.04..7,888,646.01 rows=130,337,656 width=136) (actual time=1,094.585..1,421.466 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
19. 919.628 960.222 ↓ 1.4 305,302 1

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

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

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

21. 172.902 217.216 ↓ 1.6 570,970 1

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

  • Sort Key: regs_2.epic_id
  • Sort Method: quicksort Memory: 43,747kB
22. 44.314 44.314 ↓ 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..44.314 rows=402,669 loops=1)

23.          

CTE all_unioned

24. 253.963 176,147.757 ↓ 263.7 501,764 1

Subquery Scan on all_unioned_1 (cost=484,442,728.46..484,455,096.92 rows=1,903 width=64) (actual time=171,157.891..176,147.757 rows=501,764 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 3,694,643
25. 2,627.309 175,893.794 ↓ 11.0 4,196,407 1

WindowAgg (cost=484,442,728.46..484,450,339.82 rows=380,568 width=76) (actual time=171,157.889..175,893.794 rows=4,196,407 loops=1)

26. 18,048.952 173,266.485 ↓ 11.0 4,196,407 1

Sort (cost=484,442,728.46..484,443,679.88 rows=380,568 width=68) (actual time=171,157.870..173,266.485 rows=4,196,407 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 280,328kB
27. 419.176 155,217.533 ↓ 11.0 4,196,407 1

Subquery Scan on foo_1 (cost=96,322.20..484,407,454.00 rows=380,568 width=68) (actual time=736.781..155,217.533 rows=4,196,407 loops=1)

28. 304.442 154,798.357 ↓ 11.0 4,196,407 1

Append (cost=96,322.20..484,403,648.32 rows=380,568 width=68) (actual time=736.779..154,798.357 rows=4,196,407 loops=1)

29. 0.231 737.085 ↓ 3.8 1,547 1

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

30. 2.576 736.854 ↓ 3.8 1,549 1

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

  • Sort Key: epic_auth_registrations.epic_id
  • Sort Method: quicksort Memory: 266kB
31. 109.429 734.278 ↓ 3.8 1,549 1

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

  • Hash Cond: (client_events_pre_load_tutorial.user_id = epic_auth_registrations.user_id)
32. 0.106 0.486 ↓ 3.8 1,550 1

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

33. 0.380 0.380 ↓ 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.029..0.380 rows=1,550 loops=1)

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

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

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 85,823kB
35. 232.463 232.463 ↑ 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.008..232.463 rows=1,557,359 loops=1)

36. 1,100.254 3,257.482 ↓ 2,498.5 499,699 1

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

  • Group Key: logins_cleaned.epic_id, 'Login'::character varying, 2
37. 2,157.228 2,157.228 ↑ 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=10.048..2,157.228 rows=2,289,658 loops=1)

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

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

39. 0.009 108.500 ↓ 0.0 0 1

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

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

Seq Scan on map_loading_time_event (cost=0.00..42,865.20 rows=1,446 width=68) (actual time=108.491..108.491 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
41. 0.001 303.898 ↓ 0.0 0 1

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

42. 0.005 303.897 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
43. 303.892 303.892 ↓ 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.891..303.892 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
44. 226.488 24,038.704 ↓ 1,551.3 310,253 1

Unique (cost=685,876.33..72,788,207.25 rows=200 width=68) (actual time=20,408.755..24,038.704 rows=310,253 loops=1)

45. 962.606 23,812.216 ↑ 438.3 2,631,560 1

Merge Join (cost=685,876.33..69,904,377.00 rows=1,153,532,100 width=68) (actual time=20,408.753..23,812.216 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
46. 170.528 212.684 ↓ 1.1 402,669 1

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

  • Sort Key: regs_3.epic_id
  • Sort Method: quicksort Memory: 43,747kB
47. 42.156 42.156 ↓ 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..42.156 rows=402,669 loops=1)

48. 432.720 22,636.926 ↓ 2.4 4,519,828 1

Materialize (cost=644,990.56..654,501.22 rows=1,902,132 width=40) (actual time=20,217.579..22,636.926 rows=4,519,828 loops=1)

49. 17,035.731 22,204.206 ↓ 2.4 4,519,828 1

Sort (cost=644,990.56..649,745.89 rows=1,902,132 width=40) (actual time=20,217.574..22,204.206 rows=4,519,828 loops=1)

  • Sort Key: char_customization_selected.epic_id
  • Sort Method: external merge Disk: 256,520kB
50. 5,168.475 5,168.475 ↓ 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=36.017..5,168.475 rows=4,519,828 loops=1)

  • Filter: ("timestamp" > (now() - '90 days'::interval))
  • Rows Removed by Filter: 3,251,619
51. 38.772 633.837 ↓ 1.6 402,669 1

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

52. 595.065 595.065 ↓ 1.6 402,669 1

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

53. 20.276 1,381.043 ↓ 401.6 80,316 1

Unique (cost=84,102.11..6,621,571.72 rows=200 width=68) (actual time=1,091.525..1,381.043 rows=80,316 loops=1)

54. 208.974 1,360.767 ↑ 737.7 141,777 1

Merge Join (cost=84,102.11..6,360,107.42 rows=104,585,717 width=68) (actual time=1,091.524..1,360.767 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. 839.408 947.110 ↓ 1.2 215,073 1

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

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

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

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

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

  • Sort Key: regs_4.epic_id
  • Sort Method: quicksort Memory: 43,747kB
58. 45.795 45.795 ↓ 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.004..45.795 rows=402,669 loops=1)

59. 3.323 569.643 ↓ 72.0 14,409 1

Unique (cost=65,619.45..4,532,424.07 rows=200 width=68) (actual time=432.834..569.643 rows=14,409 loops=1)

60. 110.916 566.320 ↑ 3,352.4 21,316 1

Merge Join (cost=65,619.45..4,353,775.46 rows=71,459,447 width=68) (actual time=432.832..566.320 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. 115.695 184.991 ↑ 3.7 32,020 1

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

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

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

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

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

  • Sort Key: regs_5.epic_id
  • Sort Method: quicksort Memory: 43,747kB
64. 66.861 66.861 ↓ 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.003..66.861 rows=402,669 loops=1)

65. 35.020 426.348 ↓ 12.3 374,263 1

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

66. 295.819 391.328 ↓ 12.3 374,263 1

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

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

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

68. 196.336 25,210.805 ↓ 721.9 144,370 1

Unique (cost=1,018,674.21..127,997,854.35 rows=200 width=68) (actual time=22,248.871..25,210.805 rows=144,370 loops=1)

69. 796.326 25,014.469 ↑ 803.5 2,528,424 1

Merge Join (cost=1,018,674.21..122,919,094.91 rows=2,031,503,778 width=68) (actual time=22,248.868..25,014.469 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. 164.464 208.411 ↓ 1.1 402,668 1

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

  • Sort Key: regs_6.epic_id
  • Sort Method: quicksort Memory: 43,747kB
71. 43.947 43.947 ↓ 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..43.947 rows=402,669 loops=1)

72. 345.711 24,009.732 ↓ 1.1 3,714,214 1

Materialize (cost=977,788.44..994,537.82 rows=3,349,875 width=40) (actual time=22,060.763..24,009.732 rows=3,714,214 loops=1)

73. 12,471.792 23,664.021 ↓ 1.1 3,714,214 1

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

  • Sort Key: blueprint_crafted.epic_id
  • Sort Method: external merge Disk: 210,800kB
74. 11,192.229 11,192.229 ↓ 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=8,991.092..11,192.229 rows=3,714,214 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,942,116
75. 258.439 24,454.500 ↓ 1,648.1 329,626 1

Group (cost=1,346,717.23..214,726,488.58 rows=200 width=68) (actual time=20,587.344..24,454.500 rows=329,626 loops=1)

  • Group Key: matchmaking_started.epic_id
76. 1,003.261 24,196.061 ↑ 1,150.4 2,967,599 1

Merge Join (cost=1,346,717.23..206,191,933.43 rows=3,413,822,062 width=32) (actual time=20,587.341..24,196.061 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
77. 154.573 196.146 ↓ 1.1 402,669 1

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

  • Sort Key: regs_7.epic_id
  • Sort Method: quicksort Memory: 43,747kB
78. 41.573 41.573 ↓ 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.002..41.573 rows=402,669 loops=1)

79. 463.809 22,996.654 ↑ 1.1 4,968,025 1

Materialize (cost=1,305,831.46..1,333,977.80 rows=5,629,267 width=40) (actual time=20,412.352..22,996.654 rows=4,968,025 loops=1)

80. 16,547.687 22,532.845 ↑ 1.1 4,968,025 1

Sort (cost=1,305,831.46..1,319,904.63 rows=5,629,267 width=40) (actual time=20,412.347..22,532.845 rows=4,968,025 loops=1)

  • Sort Key: matchmaking_started.epic_id
  • Sort Method: external merge Disk: 281,952kB
81. 5,985.158 5,985.158 ↑ 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=4,210.726..5,985.158 rows=4,968,025 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 13,683,135
82. 73.245 5,261.793 ↓ 943.8 188,760 1

Group (cost=287,850.67..45,304,773.64 rows=200 width=68) (actual time=4,564.323..5,261.793 rows=188,760 loops=1)

  • Group Key: matchmaking_cancelled.epic_id
83. 438.072 5,188.548 ↑ 1,158.2 621,876 1

Merge Join (cost=287,850.67..43,504,169.50 rows=720,241,658 width=32) (actual time=4,564.320..5,188.548 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
84. 169.273 214.569 ↓ 1.1 402,668 1

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

  • Sort Key: regs_8.epic_id
  • Sort Method: quicksort Memory: 43,747kB
85. 45.296 45.296 ↓ 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..45.296 rows=402,669 loops=1)

86. 3,653.348 4,535.907 ↑ 1.2 992,970 1

Sort (cost=246,964.90..249,934.03 rows=1,187,652 width=40) (actual time=4,380.784..4,535.907 rows=992,970 loops=1)

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

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 2,906,860
88. 62.746 5,038.089 ↓ 1,564.3 312,868 1

Group (cost=596,237.96..1,316,861.51 rows=200 width=68) (actual time=4,648.488..5,038.089 rows=312,868 loops=1)

  • Group Key: player_matched_1.epic_id
89. 289.553 4,975.343 ↑ 36.8 312,868 1

Merge Join (cost=596,237.96..1,288,040.37 rows=11,528,456 width=32) (actual time=4,648.486..4,975.343 rows=312,868 loops=1)

  • Merge Cond: (player_matched_1.epic_id = regs_9.epic_id)
  • Join Filter: ((regs_9.reg_timestamp + '168:00:00'::interval) >= player_matched_1.event)
  • Rows Removed by Join Filter: 4,934
90. 1,125.574 4,456.820 ↓ 17.1 325,044 1

Sort (cost=555,352.19..555,399.71 rows=19,010 width=40) (actual time=4,439.417..4,456.820 rows=325,044 loops=1)

  • Sort Key: player_matched_1.epic_id
  • Sort Method: quicksort Memory: 37,683kB
91. 3,331.246 3,331.246 ↓ 17.1 325,044 1

Seq Scan on player_matched player_matched_1 (cost=0.00..554,001.10 rows=19,010 width=40) (actual time=0.656..3,331.246 rows=325,044 loops=1)

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

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

  • Sort Key: regs_9.epic_id
  • Sort Method: quicksort Memory: 43,747kB
93. 48.122 48.122 ↓ 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.002..48.122 rows=402,669 loops=1)

94. 81.263 6,743.978 ↓ 1,550.7 310,143 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=6,229.627..6,743.978 rows=310,143 loops=1)

  • Group Key: match_summary.epic_id
95. 379.322 6,662.715 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=6,229.624..6,662.715 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
96. 1,035.576 6,054.548 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,030.337..6,054.548 rows=324,618 loops=1)

  • Sort Key: match_summary.epic_id
  • Sort Method: quicksort Memory: 37,649kB
97. 5,018.972 5,018.972 ↓ 16.7 324,618 1

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

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

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

  • Sort Key: regs_10.epic_id
  • Sort Method: quicksort Memory: 43,747kB
99. 42.667 42.667 ↓ 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.002..42.667 rows=402,669 loops=1)

100. 51.650 6,423.391 ↓ 1,332.5 266,497 1

Group (cost=806,851.00..1,176,222.70 rows=200 width=68) (actual time=6,084.654..6,423.391 rows=266,497 loops=1)

  • Group Key: match_summary_1.epic_id
101. 254.589 6,371.741 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=32) (actual time=6,084.651..6,371.741 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
102. 872.697 5,906.023 ↓ 28.5 278,144 1

Sort (cost=765,965.23..765,989.59 rows=9,744 width=40) (actual time=5,892.616..5,906.023 rows=278,144 loops=1)

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 34,019kB
103. 5,033.326 5,033.326 ↓ 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.406..5,033.326 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
104. 168.158 211.129 ↓ 1.1 404,128 1

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

  • Sort Key: regs_11.epic_id
  • Sort Method: quicksort Memory: 43,747kB
105. 42.971 42.971 ↓ 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.003..42.971 rows=402,669 loops=1)

106. 215.684 15,901.344 ↓ 2.6 267,387 1

Hash Join (cost=6.50..20,457.36 rows=101,402 width=68) (actual time=15,654.328..15,901.344 rows=267,387 loops=1)

  • Hash 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: 45,271
107. 31.387 31.387 ↓ 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..31.387 rows=374,265 loops=1)

108. 46.587 15,654.273 ↓ 1,564.3 312,868 1

Hash (cost=4.00..4.00 rows=200 width=40) (actual time=15,654.273..15,654.273 rows=312,868 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 28,539kB
109. 15,607.686 15,607.686 ↓ 1,564.3 312,868 1

CTE Scan on max_match_1_players (cost=0.00..4.00 rows=200 width=40) (actual time=14,943.988..15,607.686 rows=312,868 loops=1)

110. 44.622 9,280.902 ↓ 1,059.0 211,810 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=8,963.094..9,280.902 rows=211,810 loops=1)

  • Group Key: match_summary_2.epic_id
111. 241.425 9,236.280 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=8,963.091..9,236.280 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
112. 745.527 8,750.390 ↓ 11.9 232,241 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=8,738.584..8,750.390 rows=232,241 loops=1)

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 24,288kB
113. 8,004.863 8,004.863 ↓ 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=1.657..8,004.863 rows=232,241 loops=1)

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

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

  • Sort Key: regs_12.epic_id
  • Sort Method: quicksort Memory: 43,747kB
115. 45.248 45.248 ↓ 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..45.248 rows=402,669 loops=1)

116. 32.518 7,172.741 ↓ 818.8 163,763 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=6,907.848..7,172.741 rows=163,763 loops=1)

  • Group Key: match_summary_3.epic_id
117. 204.148 7,140.223 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=6,907.845..7,140.223 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
118. 592.761 6,730.216 ↓ 9.5 185,195 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,720.948..6,730.216 rows=185,195 loops=1)

  • Sort Key: match_summary_3.epic_id
  • Sort Method: quicksort Memory: 20,613kB
119. 6,137.455 6,137.455 ↓ 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.048..6,137.455 rows=185,195 loops=1)

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

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

  • Sort Key: regs_13.epic_id
  • Sort Method: quicksort Memory: 43,747kB
121. 45.577 45.577 ↓ 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..45.577 rows=402,669 loops=1)

122. 27.621 5,767.893 ↓ 677.5 135,497 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=5,521.543..5,767.893 rows=135,497 loops=1)

  • Group Key: match_summary_4.epic_id
123. 191.589 5,740.272 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,521.541..5,740.272 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
124. 462.601 5,328.700 ↓ 8.0 156,775 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,321.049..5,328.700 rows=156,775 loops=1)

  • Sort Key: match_summary_4.epic_id
  • Sort Method: quicksort Memory: 18,393kB
125. 4,866.099 4,866.099 ↓ 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.028..4,866.099 rows=156,775 loops=1)

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

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

  • Sort Key: regs_14.epic_id
  • Sort Method: quicksort Memory: 43,747kB
127. 43.199 43.199 ↓ 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..43.199 rows=402,669 loops=1)

128. 23.444 5,776.346 ↓ 580.1 116,025 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=5,545.778..5,776.346 rows=116,025 loops=1)

  • Group Key: match_summary_5.epic_id
129. 180.087 5,752.902 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,545.774..5,752.902 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
130. 403.688 5,355.361 ↓ 7.0 137,030 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,348.166..5,355.361 rows=137,030 loops=1)

  • Sort Key: match_summary_5.epic_id
  • Sort Method: quicksort Memory: 16,850kB
131. 4,951.673 4,951.673 ↓ 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.055..4,951.673 rows=137,030 loops=1)

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

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

  • Sort Key: regs_15.epic_id
  • Sort Method: quicksort Memory: 43,747kB
133. 43.744 43.744 ↓ 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..43.744 rows=402,669 loops=1)

134. 14.097 6,005.592 ↓ 332.5 66,505 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=5,805.293..6,005.592 rows=66,505 loops=1)

  • Group Key: match_summary_6.epic_id
135. 160.899 5,991.495 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,805.291..5,991.495 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
136. 255.768 5,591.630 ↓ 4.4 85,379 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,586.658..5,591.630 rows=85,379 loops=1)

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 9,743kB
137. 5,335.862 5,335.862 ↓ 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.035..5,335.862 rows=85,379 loops=1)

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

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

  • Sort Key: regs_16.epic_id
  • Sort Method: quicksort Memory: 43,747kB
139. 43.313 43.313 ↓ 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..43.313 rows=402,669 loops=1)

140.          

CTE logins

141. 1,163.778 3,519.823 ↓ 2,498.5 499,699 1

HashAggregate (cost=352,152.71..352,154.71 rows=200 width=40) (actual time=3,383.849..3,519.823 rows=499,699 loops=1)

  • Group Key: logins_cleaned_1.epic_id
142. 2,356.045 2,356.045 ↑ 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=21.160..2,356.045 rows=2,289,658 loops=1)

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

CTE first_game_mode

144. 263.037 6,901.771 ↑ 111.8 316,976 1

Merge Join (cost=807,593.92..1,339,489.15 rows=35,453,186 width=64) (actual time=6,603.593..6,901.771 rows=316,976 loops=1)

  • Merge Cond: (match_summary_7.epic_id = regs_17.epic_id)
145. 1,057.500 5,914.860 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=64) (actual time=5,898.718..5,914.860 rows=324,618 loops=1)

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 37,649kB
146. 4,857.360 4,857.360 ↓ 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.122..4,857.360 rows=324,618 loops=1)

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

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

  • Sort Key: regs_17.epic_id
  • Sort Method: quicksort Memory: 43,747kB
148. 553.411 553.411 ↓ 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.028..553.411 rows=402,669 loops=1)

149. 157.781 7,147.204 ↑ 111.8 316,976 1

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

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
150. 6,989.423 6,989.423 ↑ 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=6,603.597..6,989.423 rows=316,976 loops=1)

151. 84.110 185,293.728 ↑ 28,030.6 402,463 1

Materialize (cost=117,005.82..197,606,528.89 rows=11,281,289,507 width=208) (actual time=184,070.967..185,293.728 rows=402,463 loops=1)

152. 180.608 185,209.618 ↑ 28,158.2 400,640 1

Merge Left Join (cost=117,005.82..169,403,305.13 rows=11,281,289,507 width=208) (actual time=184,070.961..185,209.618 rows=400,640 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
153. 374.089 183,124.131 ↑ 8.6 400,640 1

Merge Left Join (cost=41,039.13..99,340.39 rows=3,462,175 width=112) (actual time=182,172.541..183,124.131 rows=400,640 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
154. 413.271 6,220.321 ↓ 1.1 400,639 1

Merge Join (cost=40,897.41..46,356.39 rows=363,865 width=80) (actual time=5,680.971..6,220.321 rows=400,639 loops=1)

  • Merge Cond: (logins.epic_id = regs.epic_id)
155. 1,924.907 5,561.422 ↓ 2,498.5 499,699 1

Sort (cost=11.64..12.14 rows=200 width=40) (actual time=5,472.427..5,561.422 rows=499,699 loops=1)

  • Sort Key: logins.epic_id
  • Sort Method: quicksort Memory: 51,327kB
156. 3,636.515 3,636.515 ↓ 2,498.5 499,699 1

CTE Scan on logins (cost=0.00..4.00 rows=200 width=40) (actual time=3,383.852..3,636.515 rows=499,699 loops=1)

157. 200.331 245.628 ↓ 1.1 402,669 1

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

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

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

159. 243.492 176,529.721 ↓ 263.7 501,764 1

Sort (cost=141.72..146.47 rows=1,903 width=64) (actual time=176,491.561..176,529.721 rows=501,764 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 75,483kB
160. 176,286.229 176,286.229 ↓ 263.7 501,764 1

CTE Scan on all_unioned (cost=0.00..38.06 rows=1,903 width=64) (actual time=171,157.894..176,286.229 rows=501,764 loops=1)

161. 42.713 1,904.879 ↑ 9.7 67,258 1

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

  • Sort Key: last_quit.epic_id
  • Sort Method: quicksort Memory: 12,531kB
162. 1,862.166 1,862.166 ↑ 9.7 67,258 1

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

Planning time : 2.416 ms
Execution time : 192,981.229 ms