explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 400.956 231,043.855 ↑ 58,623,541,120,999.1 412,240 1

Merge Right Join (cost=1,687,994,359.45..543,758,839,998,454,080.00 rows=24,166,968,591,720,673,280 width=244) (actual time=229,381.902..231,043.855 rows=412,240 loops=1)

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

CTE regs

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

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

CTE last_step

5. 482.306 482.306 ↓ 1.6 402,669 1

Seq Scan on users (cost=0.00..74,094.35 rows=244,940 width=64) (actual time=0.021..482.306 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. 293.145 1,960.370 ↑ 8.0 93,339 1

Merge Join (cost=116,880.00..161,459.59 rows=742,891 width=40) (actual time=1,626.648..1,960.370 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. 486.990 1,286.664 ↓ 81.0 99,273 1

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

  • Sort Key: users_1.epic_id
  • Sort Method: quicksort Memory: 10,828kB
9. 799.674 799.674 ↓ 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.023..799.674 rows=99,273 loops=1)

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

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

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

12.          

CTE last_quit

13. 12.480 2,178.569 ↑ 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,069.725..2,178.569 rows=67,258 loops=1)

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

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

15. 324.909 2,079.071 ↑ 741.3 175,813 1

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

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

Merge Join (cost=67,312.04..7,888,646.01 rows=130,337,656 width=136) (actual time=1,442.354..1,754.162 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. 1,217.164 1,295.660 ↓ 1.4 305,302 1

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

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

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

19. 181.162 224.648 ↓ 1.6 570,970 1

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

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

21.          

CTE first_game_mode

22. 416.685 15,014.022 ↑ 111.8 316,976 1

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

  • Merge Cond: (match_summary.epic_id = regs_3.epic_id)
23. 1,897.314 13,234.425 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=64) (actual time=13,207.833..13,234.425 rows=324,618 loops=1)

  • Sort Key: match_summary.epic_id
  • Sort Method: quicksort Memory: 37,649kB
24. 11,337.111 11,337.111 ↓ 16.7 324,618 1

Seq Scan on match_summary (cost=0.00..765,319.68 rows=19,487 width=64) (actual time=0.425..11,337.111 rows=324,618 loops=1)

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

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

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

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

27.          

CTE customization

28. 262.875 9,643.636 ↓ 2.6 310,253 1

Hash Join (cost=426,401.09..434,643.31 rows=121,288 width=40) (actual time=9,301.982..9,643.636 rows=310,253 loops=1)

  • Hash Cond: (regs_4.epic_id = char_customization_selected.epic_id)
  • Join Filter: ((regs_4.reg_timestamp + '168:00:00'::interval) >= (min(char_customization_selected."timestamp")))
  • Rows Removed by Join Filter: 4,978
29. 78.801 78.801 ↓ 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..78.801 rows=402,669 loops=1)

30. 121.328 9,301.960 ↓ 1,977.3 395,459 1

Hash (cost=426,398.59..426,398.59 rows=200 width=40) (actual time=9,301.960..9,301.960 rows=395,459 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 34,992kB
31. 2,952.149 9,180.632 ↓ 1,977.3 395,459 1

HashAggregate (cost=426,394.59..426,396.59 rows=200 width=40) (actual time=9,038.625..9,180.632 rows=395,459 loops=1)

  • Group Key: char_customization_selected.epic_id
32. 6,228.483 6,228.483 ↓ 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=1.243..6,228.483 rows=4,519,828 loops=1)

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

CTE all_unioned

34. 504.821 202,291.699 ↑ 45.0 511,265 1

Subquery Scan on all_unioned_1 (cost=1,491,528,845.65..1,641,011,307.87 rows=22,997,302 width=64) (actual time=192,620.396..202,291.699 rows=511,265 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 6,510,991
35. 5,251.566 201,786.878 ↑ 655.0 7,022,256 1

WindowAgg (cost=1,491,528,845.65..1,583,518,053.17 rows=4,599,460,376 width=76) (actual time=192,620.394..201,786.878 rows=7,022,256 loops=1)

36. 26,299.332 196,535.312 ↑ 655.0 7,022,256 1

Sort (cost=1,491,528,845.65..1,503,027,496.59 rows=4,599,460,376 width=68) (actual time=192,620.375..196,535.312 rows=7,022,256 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 462,064kB
37. 817.364 170,235.980 ↑ 655.0 7,022,256 1

Subquery Scan on foo_1 (cost=96,322.20..537,742,942.45 rows=4,599,460,376 width=68) (actual time=772.350..170,235.980 rows=7,022,256 loops=1)

38. 597.955 169,418.616 ↑ 655.0 7,022,256 1

Append (cost=96,322.20..491,748,338.69 rows=4,599,460,376 width=68) (actual time=772.348..169,418.616 rows=7,022,256 loops=1)

39. 0.224 772.647 ↓ 3.8 1,547 1

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

40. 2.590 772.423 ↓ 3.8 1,549 1

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

  • Sort Key: epic_auth_registrations.epic_id
  • Sort Method: quicksort Memory: 266kB
41. 118.935 769.833 ↓ 3.8 1,549 1

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

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

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

43. 0.362 0.362 ↓ 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.362 rows=1,550 loops=1)

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

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

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

46. 1,114.075 3,394.756 ↓ 2,498.5 499,699 1

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

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

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

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

49. 0.012 106.530 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event.epic_id
  • Sort Method: quicksort Memory: 25kB
50. 106.518 106.518 ↓ 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.518..106.518 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
51. 0.000 308.281 ↓ 0.0 0 1

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

52. 0.008 308.281 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
53. 308.273 308.273 ↓ 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=308.273..308.273 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
54. 9,798.882 9,798.882 ↓ 2.6 310,253 1

CTE Scan on customization (cost=0.00..2,425.76 rows=121,288 width=68) (actual time=9,301.988..9,798.882 rows=310,253 loops=1)

55. 39.310 635.442 ↓ 1.6 402,669 1

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

56. 596.132 596.132 ↓ 1.6 402,669 1

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

57. 35.973 1,305.872 ↓ 401.6 80,316 1

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

  • Group Key: client_disconnected.epic_id, 'Connection Timeout'::character varying, 16
58. 277.084 1,269.899 ↑ 737.7 141,777 1

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

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

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

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

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

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

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

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

63. 4.402 458.125 ↓ 72.0 14,409 1

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

  • Group Key: client_disconnected_client.epic_id, 'Client Disconnect'::character varying, 17
64. 112.665 453.723 ↑ 3,352.4 21,316 1

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

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

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

  • Sort Key: client_disconnected_client.epic_id
  • Sort Method: quicksort Memory: 3,270kB
66. 46.193 46.193 ↑ 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.008..46.193 rows=32,020 loops=1)

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

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

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

69. 34.092 401.790 ↓ 12.3 374,263 1

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

70. 274.778 367.698 ↓ 12.3 374,263 1

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

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

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

72. 220.347 29,190.943 ↓ 721.9 144,370 1

Group (cost=1,018,674.21..127,997,854.35 rows=200 width=68) (actual time=25,962.520..29,190.943 rows=144,370 loops=1)

  • Group Key: blueprint_crafted.epic_id
73. 840.897 28,970.596 ↑ 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=25,962.517..28,970.596 rows=2,528,424 loops=1)

  • Merge Cond: (regs_7.epic_id = blueprint_crafted.epic_id)
  • Join Filter: ((regs_7.reg_timestamp + '168:00:00'::interval) >= blueprint_crafted.event)
  • Rows Removed by Join Filter: 486,931
74. 170.328 215.105 ↓ 1.1 402,668 1

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

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

76. 368.938 27,914.594 ↓ 1.1 3,714,214 1

Materialize (cost=977,788.44..994,537.82 rows=3,349,875 width=40) (actual time=25,769.886..27,914.594 rows=3,714,214 loops=1)

77. 17,895.839 27,545.656 ↓ 1.1 3,714,214 1

Sort (cost=977,788.44..986,163.13 rows=3,349,875 width=40) (actual time=25,769.880..27,545.656 rows=3,714,214 loops=1)

  • Sort Key: blueprint_crafted.epic_id
  • Sort Method: external merge Disk: 210,800kB
78. 9,649.817 9,649.817 ↓ 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,549.170..9,649.817 rows=3,714,214 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,942,116
79. 1,364.919 35,218.790 ↑ 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=30,438.225..35,218.790 rows=2,967,599 loops=1)

  • Merge Cond: (regs_8.epic_id = matchmaking_started.epic_id)
  • Join Filter: ((regs_8.reg_timestamp + '168:00:00'::interval) >= matchmaking_started.event)
  • Rows Removed by Join Filter: 874,267
80. 163.545 207.423 ↓ 1.1 402,669 1

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

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

82. 605.438 33,646.448 ↑ 1.1 4,968,025 1

Materialize (cost=1,305,831.46..1,333,977.80 rows=5,629,267 width=40) (actual time=30,258.936..33,646.448 rows=4,968,025 loops=1)

83. 23,146.570 33,041.010 ↑ 1.1 4,968,025 1

Sort (cost=1,305,831.46..1,319,904.63 rows=5,629,267 width=40) (actual time=30,258.931..33,041.010 rows=4,968,025 loops=1)

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 13,683,135
85. 692.661 6,533.562 ↑ 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,613.083..6,533.562 rows=621,876 loops=1)

  • Merge Cond: (regs_9.epic_id = matchmaking_cancelled.epic_id)
  • Join Filter: ((regs_9.reg_timestamp + '168:00:00'::interval) >= matchmaking_cancelled.event)
  • Rows Removed by Join Filter: 164,563
86. 176.704 218.983 ↓ 1.1 402,668 1

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

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

88. 4,744.326 5,621.918 ↑ 1.2 992,970 1

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

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

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 2,906,860
90. 281.668 7,857.807 ↑ 36.8 312,868 1

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

  • Merge Cond: (player_matched.epic_id = regs_10.epic_id)
  • Join Filter: ((regs_10.reg_timestamp + '168:00:00'::interval) >= player_matched.event)
  • Rows Removed by Join Filter: 4,934
91. 1,089.705 7,351.644 ↓ 17.1 325,044 1

Sort (cost=555,352.19..555,399.71 rows=19,010 width=40) (actual time=7,335.629..7,351.644 rows=325,044 loops=1)

  • Sort Key: player_matched.epic_id
  • Sort Method: quicksort Memory: 37,683kB
92. 6,261.939 6,261.939 ↓ 17.1 325,044 1

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

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

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

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

95. 337.151 13,890.408 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=13,512.860..13,890.408 rows=311,944 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: 5,032
96. 1,858.247 13,223.921 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=13,205.622..13,223.921 rows=324,618 loops=1)

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 37,649kB
97. 11,365.674 11,365.674 ↓ 16.7 324,618 1

Seq Scan on match_summary match_summary_1 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.427..11,365.674 rows=324,618 loops=1)

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

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

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

100. 483.960 10,816.999 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=68) (actual time=10,273.925..10,816.999 rows=267,940 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: 3,898
101. 1,531.878 9,958.615 ↓ 28.5 278,144 1

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

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 34,019kB
102. 8,426.737 8,426.737 ↓ 28.5 278,144 1

Seq Scan on match_summary match_summary_2 (cost=0.00..765,319.68 rows=9,744 width=40) (actual time=0.051..8,426.737 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
103. 293.667 374.424 ↓ 1.1 404,128 1

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

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

105. 300.466 4,908.882 ↑ 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,529.260..4,908.882 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
106. 2,469.329 2,549.298 ↓ 1.2 374,265 1

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

  • Sort Key: last_button_1.epic_id
  • Sort Method: quicksort Memory: 60,134kB
107. 79.969 79.969 ↓ 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.015..79.969 rows=374,265 loops=1)

108. 58.107 2,059.118 ↑ 8.0 93,340 1

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

  • Sort Key: max_match_1_players.epic_id
  • Sort Method: quicksort Memory: 10,365kB
109. 2,001.011 2,001.011 ↑ 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,626.651..2,001.011 rows=93,339 loops=1)

110. 385.412 8,716.257 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=8,283.754..8,716.257 rows=213,078 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,265
111. 757.360 8,091.664 ↓ 11.9 232,241 1

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

  • Sort Key: match_summary_3.epic_id
  • Sort Method: quicksort Memory: 24,288kB
112. 7,334.304 7,334.304 ↓ 11.9 232,241 1

Seq Scan on match_summary match_summary_3 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.084..7,334.304 rows=232,241 loops=1)

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

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

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

115. 209.086 6,887.896 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=6,650.397..6,887.896 rows=164,729 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,473
116. 594.240 6,472.386 ↓ 9.5 185,195 1

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

  • Sort Key: match_summary_4.epic_id
  • Sort Method: quicksort Memory: 20,613kB
117. 5,878.146 5,878.146 ↓ 9.5 185,195 1

Seq Scan on match_summary match_summary_4 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.109..5,878.146 rows=185,195 loops=1)

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

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

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

120. 351.950 8,702.810 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=8,305.379..8,702.810 rows=136,273 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,395
121. 842.288 7,970.866 ↓ 8.0 156,775 1

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

  • Sort Key: match_summary_5.epic_id
  • Sort Method: quicksort Memory: 18,393kB
122. 7,128.578 7,128.578 ↓ 8.0 156,775 1

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

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

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

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

125. 263.953 9,314.666 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=9,014.330..9,314.666 rows=116,710 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: 7,339
126. 664.358 8,677.580 ↓ 7.0 137,030 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=8,668.101..8,677.580 rows=137,030 loops=1)

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 16,850kB
127. 8,013.222 8,013.222 ↓ 7.0 137,030 1

Seq Scan on match_summary match_summary_6 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.083..8,013.222 rows=137,030 loops=1)

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

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

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

130. 275.606 9,599.315 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=9,284.531..9,599.315 rows=66,851 loops=1)

  • Merge Cond: (match_summary_7.epic_id = regs_17.epic_id)
  • Join Filter: ((regs_17.reg_timestamp + '168:00:00'::interval) >= match_summary_7."timestamp")
  • Rows Removed by Join Filter: 6,858
131. 418.016 8,977.068 ↓ 4.4 85,379 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=8,969.993..8,977.068 rows=85,379 loops=1)

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 9,743kB
132. 8,559.052 8,559.052 ↓ 4.4 85,379 1

Seq Scan on match_summary match_summary_7 (cost=0.00..765,319.68 rows=19,487 width=40) (actual time=0.091..8,559.052 rows=85,379 loops=1)

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

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

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

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

135.          

CTE logins

136. 1,755.592 6,077.930 ↓ 2,498.5 499,699 1

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

  • Group Key: logins_cleaned_1.epic_id
137. 4,322.338 4,322.338 ↑ 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=2.584..4,322.338 rows=2,289,658 loops=1)

  • Filter: (event_login > (now() - '90 days'::interval))
  • Rows Removed by Filter: 5,798,175
138. 180.105 15,330.300 ↑ 111.8 316,976 1

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

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
139. 15,150.195 15,150.195 ↑ 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=14,539.922..15,150.195 rows=316,976 loops=1)

140. 81.869 215,312.599 ↑ 330,709,579.2 412,240 1

Materialize (cost=3,882,884.79..2,385,829,742,444.34 rows=136,331,716,939,181 width=208) (actual time=214,076.138..215,312.599 rows=412,240 loops=1)

141. 364.544 215,230.730 ↑ 332,402,068.9 410,141 1

Merge Left Join (cost=3,882,884.79..2,045,000,450,096.39 rows=136,331,716,939,181 width=208) (actual time=214,076.133..215,230.730 rows=410,141 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
142. 170.877 12,082.780 ↑ 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=11,393.110..12,082.780 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
143. 396.078 9,670.471 ↓ 1.1 400,639 1

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

  • Merge Cond: (logins.epic_id = regs.epic_id)
144. 2,538.937 8,825.617 ↓ 2,498.5 499,699 1

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

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

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

146. 361.869 448.776 ↓ 1.1 402,669 1

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

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

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

148. 38.023 2,241.432 ↑ 9.7 67,258 1

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

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

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

150. 62.939 202,783.406 ↑ 45.0 511,265 1

Materialize (cost=3,766,020.69..3,881,007.20 rows=22,997,302 width=64) (actual time=202,683.016..202,783.406 rows=511,265 loops=1)

151. 261.788 202,720.467 ↑ 45.0 511,265 1

Sort (cost=3,766,020.69..3,823,513.94 rows=22,997,302 width=64) (actual time=202,683.013..202,720.467 rows=511,265 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 76,818kB
152. 202,458.679 202,458.679 ↑ 45.0 511,265 1

CTE Scan on all_unioned (cost=0.00..459,946.04 rows=22,997,302 width=64) (actual time=192,620.397..202,458.679 rows=511,265 loops=1)

Planning time : 3.734 ms
Execution time : 231,150.097 ms