explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jIEy

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 389.026 181,879.832 ↑ 14,906,879,608.6 402,463 1

Merge Right Join (cost=697,322,328.30..134,988,885,618,348.84 rows=5,999,467,487,907,304 width=244) (actual time=180,330.493..181,879.832 rows=402,463 loops=1)

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

CTE regs

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

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

CTE last_step

5. 493.685 493.685 ↓ 1.6 402,669 1

Seq Scan on users (cost=0.00..74,094.35 rows=244,940 width=64) (actual time=0.023..493.685 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,588.358 14,523.708 ↓ 1,564.3 312,868 1

HashAggregate (cost=156,692,898.65..156,693,398.65 rows=200 width=40) (actual time=13,975.480..14,523.708 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. 904.477 12,935.350 ↑ 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=9,880.782..12,935.350 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. 168.509 210.127 ↓ 1.1 402,669 1

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

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

11. 375.659 11,820.746 ↓ 1.0 3,980,882 1

Materialize (cost=1,007,841.41..1,026,851.25 rows=3,801,968 width=48) (actual time=9,704.529..11,820.746 rows=3,980,882 loops=1)

12. 6,351.672 11,445.087 ↓ 1.0 3,980,882 1

Sort (cost=1,007,841.41..1,017,346.33 rows=3,801,968 width=48) (actual time=9,704.522..11,445.087 rows=3,980,882 loops=1)

  • Sort Key: player_matched.epic_id
  • Sort Method: external merge Disk: 257,112kB
13. 5,093.415 5,093.415 ↓ 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.314..5,093.415 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.472 1,825.490 ↑ 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,716.765..1,825.490 rows=67,258 loops=1)

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

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

17. 312.721 1,725.996 ↑ 741.3 175,813 1

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

  • Sort Key: player_quit.epic_id, player_quit."timestamp" DESC
  • Sort Method: quicksort Memory: 30,868kB
18. 239.965 1,413.275 ↑ 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.316..1,413.275 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. 923.687 964.168 ↓ 1.4 305,302 1

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

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

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

21. 166.529 209.142 ↓ 1.6 570,970 1

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

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

23.          

CTE all_unioned

24. 241.966 163,636.939 ↓ 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=158,955.802..163,636.939 rows=501,764 loops=1)

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

WindowAgg (cost=484,442,728.46..484,450,339.82 rows=380,568 width=76) (actual time=158,955.800..163,394.973 rows=4,196,407 loops=1)

26. 17,621.039 160,928.582 ↓ 11.0 4,196,407 1

Sort (cost=484,442,728.46..484,443,679.88 rows=380,568 width=68) (actual time=158,955.783..160,928.582 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. 401.115 143,307.543 ↓ 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=769.766..143,307.543 rows=4,196,407 loops=1)

28. 295.189 142,906.428 ↓ 11.0 4,196,407 1

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

29. 0.232 770.068 ↓ 3.8 1,547 1

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

30. 2.598 769.836 ↓ 3.8 1,549 1

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

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

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

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

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

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

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

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

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

36. 1,083.630 3,268.951 ↓ 2,498.5 499,699 1

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

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

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

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

39. 0.009 342.828 ↓ 0.0 0 1

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

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

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

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

42. 0.009 945.981 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
43. 945.972 945.972 ↓ 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=945.972..945.972 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. 231.527 21,895.853 ↓ 1,551.3 310,253 1

Unique (cost=685,876.33..72,788,207.25 rows=200 width=68) (actual time=18,419.033..21,895.853 rows=310,253 loops=1)

45. 938.263 21,664.326 ↑ 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=18,419.032..21,664.326 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. 180.791 227.706 ↓ 1.1 402,669 1

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

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

48. 435.537 20,498.357 ↓ 2.4 4,519,828 1

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

49. 15,114.168 20,062.820 ↓ 2.4 4,519,828 1

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

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

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

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

52. 601.906 601.906 ↓ 1.6 402,669 1

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

53. 22.523 1,347.294 ↓ 401.6 80,316 1

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

54. 220.667 1,324.771 ↑ 737.7 141,777 1

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

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

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

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

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

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

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

59. 3.298 446.412 ↓ 72.0 14,409 1

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

60. 110.954 443.114 ↑ 3,352.4 21,316 1

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

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

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

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

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

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

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

65. 35.170 483.771 ↓ 12.3 374,263 1

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

66. 289.862 448.601 ↓ 12.3 374,263 1

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

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

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

68. 196.364 19,988.782 ↓ 721.9 144,370 1

Unique (cost=1,018,674.21..127,997,854.35 rows=200 width=68) (actual time=17,090.673..19,988.782 rows=144,370 loops=1)

69. 778.235 19,792.418 ↑ 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=17,090.671..19,792.418 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. 162.185 204.477 ↓ 1.1 402,668 1

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

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

72. 329.769 18,809.706 ↓ 1.1 3,714,214 1

Materialize (cost=977,788.44..994,537.82 rows=3,349,875 width=40) (actual time=16,906.527..18,809.706 rows=3,714,214 loops=1)

73. 11,967.719 18,479.937 ↓ 1.1 3,714,214 1

Sort (cost=977,788.44..986,163.13 rows=3,349,875 width=40) (actual time=16,906.521..18,479.937 rows=3,714,214 loops=1)

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

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

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

  • Group Key: matchmaking_started.epic_id
76. 1,002.577 24,097.748 ↑ 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,522.453..24,097.748 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. 167.330 209.608 ↓ 1.1 402,669 1

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

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

79. 465.921 22,885.563 ↑ 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,333.611..22,885.563 rows=4,968,025 loops=1)

80. 16,627.588 22,419.642 ↑ 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,333.607..22,419.642 rows=4,968,025 loops=1)

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

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

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

  • Group Key: matchmaking_cancelled.epic_id
83. 408.397 4,755.805 ↑ 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,168.280..4,755.805 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. 165.773 207.913 ↓ 1.1 402,668 1

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

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

86. 3,266.380 4,139.495 ↑ 1.2 992,970 1

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

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

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

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

Group (cost=596,237.96..1,316,861.51 rows=200 width=68) (actual time=5,965.879..6,345.973 rows=312,868 loops=1)

  • Group Key: player_matched_1.epic_id
89. 279.910 6,281.591 ↑ 36.8 312,868 1

Merge Join (cost=596,237.96..1,288,040.37 rows=11,528,456 width=32) (actual time=5,965.876..6,281.591 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,042.347 5,784.916 ↓ 17.1 325,044 1

Sort (cost=555,352.19..555,399.71 rows=19,010 width=40) (actual time=5,768.391..5,784.916 rows=325,044 loops=1)

  • Sort Key: player_matched_1.epic_id
  • Sort Method: quicksort Memory: 37,683kB
91. 4,742.569 4,742.569 ↓ 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.734..4,742.569 rows=325,044 loops=1)

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

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

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

94. 59.527 6,464.654 ↓ 1,550.7 310,143 1

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

  • Group Key: match_summary.epic_id
95. 274.482 6,405.127 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=6,095.666..6,405.127 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,020.827 5,920.687 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,904.651..5,920.687 rows=324,618 loops=1)

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

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

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

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

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

100. 51.710 6,335.166 ↓ 1,332.5 266,497 1

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

  • Group Key: match_summary_1.epic_id
101. 257.810 6,283.456 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=32) (actual time=5,991.837..6,283.456 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. 883.959 5,811.013 ↓ 28.5 278,144 1

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

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 34,019kB
103. 4,927.054 4,927.054 ↓ 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.288..4,927.054 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. 171.844 214.633 ↓ 1.1 404,128 1

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

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

106. 218.469 14,928.205 ↓ 2.6 267,387 1

Hash Join (cost=6.50..20,457.36 rows=101,402 width=68) (actual time=14,678.201..14,928.205 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.593 31.593 ↓ 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.593 rows=374,265 loops=1)

108. 45.092 14,678.143 ↓ 1,564.3 312,868 1

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

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

CTE Scan on max_match_1_players (cost=0.00..4.00 rows=200 width=40) (actual time=13,975.485..14,633.051 rows=312,868 loops=1)

110. 41.247 6,478.118 ↓ 1,059.0 211,810 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=6,179.052..6,478.118 rows=211,810 loops=1)

  • Group Key: match_summary_2.epic_id
111. 227.777 6,436.871 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=6,179.048..6,436.871 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. 719.071 5,974.929 ↓ 11.9 232,241 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,963.753..5,974.929 rows=232,241 loops=1)

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

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

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

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

116. 33.344 5,834.736 ↓ 818.8 163,763 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=5,561.005..5,834.736 rows=163,763 loops=1)

  • Group Key: match_summary_3.epic_id
117. 211.503 5,801.392 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,561.003..5,801.392 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. 544.984 5,377.708 ↓ 9.5 185,195 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=5,368.354..5,377.708 rows=185,195 loops=1)

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

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

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

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

122. 27.001 5,710.368 ↓ 677.5 135,497 1

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

  • Group Key: match_summary_4.epic_id
123. 190.443 5,683.367 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,465.272..5,683.367 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. 455.202 5,263.961 ↓ 8.0 156,775 1

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

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

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

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

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

128. 23.192 5,569.077 ↓ 580.1 116,025 1

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

  • Group Key: match_summary_5.epic_id
129. 176.807 5,545.885 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,343.565..5,545.885 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. 395.056 5,138.806 ↓ 7.0 137,030 1

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

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

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

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

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

134. 12.926 5,639.191 ↓ 332.5 66,505 1

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

  • Group Key: match_summary_6.epic_id
135. 149.277 5,626.265 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,454.228..5,626.265 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. 235.989 5,256.339 ↓ 4.4 85,379 1

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

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

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

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

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

140.          

CTE logins

141. 1,240.712 3,951.152 ↓ 2,498.5 499,699 1

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

  • Group Key: logins_cleaned_1.epic_id
142. 2,710.440 2,710.440 ↑ 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=19.554..2,710.440 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. 530.471 8,104.153 ↑ 335.6 316,976 1

Merge Join (cost=620,371.46..2,216,084.44 rows=106,361,378 width=64) (actual time=7,376.808..8,104.153 rows=316,976 loops=1)

  • Merge Cond: (match_summary_7.epic_id = regs_17.epic_id)
145. 4,596.645 6,831.386 ↓ 20.8 1,218,234 1

Sort (cost=579,485.69..579,631.85 rows=58,462 width=64) (actual time=6,653.910..6,831.386 rows=1,218,234 loops=1)

  • Sort Key: match_summary_7.epic_id
  • Sort Method: external merge Disk: 58,120kB
146. 0.000 2,234.741 ↓ 20.8 1,218,235 1

Gather (cost=1,000.00..574,856.90 rows=58,462 width=64) (actual time=1.155..2,234.741 rows=1,218,235 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
147. 2,261.772 2,261.772 ↓ 16.7 243,647 5 / 5

Parallel Seq Scan on match_summary match_summary_7 (cost=0.00..568,010.70 rows=14,616 width=64) (actual time=0.031..2,261.772 rows=243,647 loops=5)

  • Filter: (battle_num = 1)
  • Rows Removed by Filter: 2,599,761
148. 172.075 742.296 ↓ 1.1 404,492 1

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

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

150. 150.536 8,342.845 ↑ 335.6 316,976 1

Sort (cost=18,592,646.74..18,858,550.18 rows=106,361,378 width=64) (actual time=8,318.571..8,342.845 rows=316,976 loops=1)

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
151. 8,192.309 8,192.309 ↑ 335.6 316,976 1

CTE Scan on first_game_mode (cost=0.00..2,127,227.56 rows=106,361,378 width=64) (actual time=7,376.812..8,192.309 rows=316,976 loops=1)

152. 77.350 173,147.961 ↑ 28,030.6 402,463 1

Materialize (cost=117,005.82..197,606,528.89 rows=11,281,289,507 width=208) (actual time=172,011.908..173,147.961 rows=402,463 loops=1)

153. 166.719 173,070.611 ↑ 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=172,011.903..173,070.611 rows=400,640 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
154. 342.646 171,018.264 ↑ 8.6 400,640 1

Merge Left Join (cost=41,039.13..99,340.39 rows=3,462,175 width=112) (actual time=170,132.700..171,018.264 rows=400,640 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
155. 387.367 6,673.411 ↓ 1.1 400,639 1

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

  • Merge Cond: (logins.epic_id = regs.epic_id)
156. 1,970.500 6,038.179 ↓ 2,498.5 499,699 1

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

  • Sort Key: logins.epic_id
  • Sort Method: quicksort Memory: 51,327kB
157. 4,067.679 4,067.679 ↓ 2,498.5 499,699 1

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

158. 202.450 247.865 ↓ 1.1 402,669 1

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

  • Sort Key: regs.epic_id
  • Sort Method: quicksort Memory: 68,914kB
159. 45.415 45.415 ↓ 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.415 rows=402,669 loops=1)

160. 233.700 164,002.207 ↓ 263.7 501,764 1

Sort (cost=141.72..146.47 rows=1,903 width=64) (actual time=163,966.603..164,002.207 rows=501,764 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 75,483kB
161. 163,768.507 163,768.507 ↓ 263.7 501,764 1

CTE Scan on all_unioned (cost=0.00..38.06 rows=1,903 width=64) (actual time=158,955.805..163,768.507 rows=501,764 loops=1)

162. 35.486 1,885.628 ↑ 9.7 67,258 1

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

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

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

Planning time : 8.627 ms
Execution time : 182,015.349 ms