explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 436.418 357,601.204 ↑ 15,114,929,594,590.3 412,240 1

Merge Right Join (cost=436,420,192.74..140,197,545,675,895,488.00 rows=6,230,978,576,073,886,720 width=244) (actual time=355,846.778..357,601.204 rows=412,240 loops=1)

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

CTE regs

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

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

CTE last_step

5. 669.975 669.975 ↓ 1.6 402,669 1

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

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

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

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

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

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

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

12.          

CTE last_quit

13. 17.055 2,541.914 ↑ 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,396.007..2,541.914 rows=67,258 loops=1)

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

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

15. 432.916 2,407.895 ↑ 741.3 175,813 1

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

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

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

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

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

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

19. 247.252 306.767 ↓ 1.6 570,970 1

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

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

21.          

CTE first_game_mode

22. 275.929 98,454.296 ↑ 111.8 316,976 1

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

  • Merge Cond: (match_summary.epic_id = regs_3.epic_id)
23. 1,466.036 30,952.173 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=64) (actual time=30,933.758..30,952.173 rows=324,618 loops=1)

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

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

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

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

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

27.          

CTE customization

28. 225.789 7,397.225 ↓ 2.6 310,253 1

Hash Join (cost=426,401.09..434,643.31 rows=121,288 width=40) (actual time=7,112.763..7,397.225 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. 58.708 58.708 ↓ 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..58.708 rows=402,669 loops=1)

30. 97.312 7,112.728 ↓ 1,977.3 395,459 1

Hash (cost=426,398.59..426,398.59 rows=200 width=40) (actual time=7,112.727..7,112.728 rows=395,459 loops=1)

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

HashAggregate (cost=426,394.59..426,396.59 rows=200 width=40) (actual time=6,892.369..7,015.416 rows=395,459 loops=1)

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

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

CTE crafted

34. 162.772 8,864.387 ↓ 1.2 144,370 1

Hash Join (cost=579,148.30..587,390.51 rows=121,288 width=40) (actual time=8,643.125..8,864.387 rows=144,370 loops=1)

  • Hash Cond: (regs_5.epic_id = blueprint_crafted.epic_id)
  • Join Filter: ((regs_5.reg_timestamp + '168:00:00'::interval) >= (min(blueprint_crafted.event)))
  • Rows Removed by Join Filter: 4,881
35. 58.515 58.515 ↓ 1.1 402,669 1

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

36. 37.503 8,643.100 ↓ 984.9 196,976 1

Hash (cost=579,145.80..579,145.80 rows=200 width=40) (actual time=8,643.100..8,643.100 rows=196,976 loops=1)

  • Buckets: 262,144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 17,437kB
37. 1,525.557 8,605.597 ↓ 984.9 196,976 1

HashAggregate (cost=579,141.80..579,143.80 rows=200 width=40) (actual time=8,552.397..8,605.597 rows=196,976 loops=1)

  • Group Key: blueprint_crafted.epic_id
38. 7,080.040 7,080.040 ↓ 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=5,324.095..7,080.040 rows=3,714,214 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,942,116
39.          

CTE matchmaking

40. 232.488 8,492.034 ↓ 2.7 329,626 1

Hash Join (cost=614,858.37..623,100.59 rows=121,288 width=40) (actual time=8,198.674..8,492.034 rows=329,626 loops=1)

  • Hash Cond: (regs_6.epic_id = matchmaking_started.epic_id)
  • Join Filter: ((regs_6.reg_timestamp + '168:00:00'::interval) >= (min(matchmaking_started.event)))
  • Rows Removed by Join Filter: 4,638
41. 60.902 60.902 ↓ 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.001..60.902 rows=402,669 loops=1)

42. 179.521 8,198.644 ↓ 2,093.3 418,656 1

Hash (cost=614,855.87..614,855.87 rows=200 width=40) (actual time=8,198.644..8,198.644 rows=418,656 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 36,804kB
43. 1,596.110 8,019.123 ↓ 2,093.3 418,656 1

HashAggregate (cost=614,851.87..614,853.87 rows=200 width=40) (actual time=7,891.453..8,019.123 rows=418,656 loops=1)

  • Group Key: matchmaking_started.epic_id
44. 6,423.013 6,423.013 ↑ 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,608.685..6,423.013 rows=4,968,025 loops=1)

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 13,683,135
45.          

CTE all_unioned

46. 260.623 247,094.476 ↑ 11.6 511,265 1

Subquery Scan on all_unioned_1 (cost=352,538,528.57..391,079,651.00 rows=5,929,403 width=64) (actual time=240,552.326..247,094.476 rows=511,265 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 3,873,018
47. 3,209.311 246,833.853 ↑ 270.5 4,384,283 1

WindowAgg (cost=352,538,528.57..376,256,142.37 rows=1,185,880,690 width=76) (actual time=240,552.323..246,833.853 rows=4,384,283 loops=1)

48. 24,342.529 243,624.542 ↑ 270.5 4,384,283 1

Sort (cost=352,538,528.57..355,503,230.30 rows=1,185,880,690 width=68) (actual time=240,552.299..243,624.542 rows=4,384,283 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 289,104kB
49. 41,283.102 219,282.013 ↑ 270.5 4,384,283 1

Subquery Scan on foo_1 (cost=96,322.20..118,218,512.05 rows=1,185,880,690 width=68) (actual time=2,046.730..219,282.013 rows=4,384,283 loops=1)

50. 350.332 177,998.911 ↑ 270.5 4,384,283 1

Append (cost=96,322.20..106,359,705.15 rows=1,185,880,690 width=68) (actual time=2,046.727..177,998.911 rows=4,384,283 loops=1)

51. 0.325 2,047.153 ↓ 3.8 1,547 1

Unique (cost=96,322.20..96,324.23 rows=405 width=69) (actual time=2,046.716..2,047.153 rows=1,547 loops=1)

52. 25.902 2,046.828 ↓ 3.8 1,549 1

Sort (cost=96,322.20..96,323.21 rows=405 width=69) (actual time=2,046.714..2,046.828 rows=1,549 loops=1)

  • Sort Key: epic_auth_registrations.epic_id
  • Sort Method: quicksort Memory: 266kB
53. 278.551 2,020.926 ↓ 3.8 1,549 1

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

  • Hash Cond: (client_events_pre_load_tutorial.user_id = epic_auth_registrations.user_id)
54. 0.143 1.033 ↓ 3.8 1,550 1

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

55. 0.890 0.890 ↓ 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.053..0.890 rows=1,550 loops=1)

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

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

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

58. 1,424.608 15,282.891 ↓ 2,498.5 499,699 1

HashAggregate (cost=344,631.00..344,633.00 rows=200 width=68) (actual time=15,144.288..15,282.891 rows=499,699 loops=1)

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

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

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

61. 1,299.321 1,421.373 ↓ 0.0 0 1

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

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

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

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

64. 0.008 330.565 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
65. 330.557 330.557 ↓ 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=330.557..330.557 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
66. 7,520.356 7,520.356 ↓ 2.6 310,253 1

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

67. 50.951 869.748 ↓ 1.6 402,669 1

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

68. 818.797 818.797 ↓ 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..818.797 rows=402,669 loops=1)

69. 38.554 1,657.642 ↓ 401.6 80,316 1

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

  • Group Key: client_disconnected.epic_id, 'Connection Timeout'::character varying, 16
70. 292.141 1,619.088 ↑ 737.7 141,777 1

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

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

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

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

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

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

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

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

75. 5.513 509.732 ↓ 72.0 14,409 1

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

  • Group Key: client_disconnected_client.epic_id, 'Client Disconnect'::character varying, 17
76. 142.905 504.219 ↑ 3,352.4 21,316 1

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

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

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

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

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

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

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

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

81. 37.111 514.428 ↓ 12.3 374,263 1

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

82. 336.817 477.317 ↓ 12.3 374,263 1

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

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

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

84. 8,919.268 8,919.268 ↓ 1.2 144,370 1

CTE Scan on crafted (cost=0.00..2,425.76 rows=121,288 width=68) (actual time=8,643.129..8,919.268 rows=144,370 loops=1)

85. 9,293.891 9,293.891 ↓ 2.7 329,626 1

CTE Scan on matchmaking (cost=0.00..2,425.76 rows=121,288 width=68) (actual time=8,198.681..9,293.891 rows=329,626 loops=1)

86. 570.725 61,115.768 ↑ 1,158.2 621,876 1

Merge Join (cost=287,850.67..43,504,169.50 rows=720,241,658 width=68) (actual time=60,338.927..61,115.768 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
87. 55,010.413 55,063.406 ↓ 1.1 402,668 1

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

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

89. 4,364.472 5,481.637 ↑ 1.2 992,970 1

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

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

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

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

Merge Join (cost=596,237.96..1,288,040.37 rows=11,528,456 width=68) (actual time=7,076.182..7,506.288 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
92. 1,442.372 6,837.324 ↓ 17.1 325,044 1

Sort (cost=555,352.19..555,399.71 rows=19,010 width=40) (actual time=6,817.462..6,837.324 rows=325,044 loops=1)

  • Sort Key: player_matched.epic_id
  • Sort Method: quicksort Memory: 37,683kB
93. 5,394.952 5,394.952 ↓ 17.1 325,044 1

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

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

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

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

96. 289.940 11,677.130 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=11,352.332..11,677.130 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
97. 1,267.538 11,100.183 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=11,083.419..11,100.183 rows=324,618 loops=1)

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 37,649kB
98. 9,832.645 9,832.645 ↓ 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.423..9,832.645 rows=324,618 loops=1)

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

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

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

101. 255.959 8,521.957 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=68) (actual time=8,234.005..8,521.957 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
102. 1,014.371 8,048.368 ↓ 28.5 278,144 1

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

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 34,019kB
103. 7,033.997 7,033.997 ↓ 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=79.828..7,033.997 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. 174.094 217.630 ↓ 1.1 404,128 1

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

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

106. 183.408 3,521.085 ↑ 25,343.5 14,862 1

Merge Join (cost=123,034.32..20,840,547.57 rows=376,654,404 width=68) (actual time=3,271.686..3,521.085 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
107. 1,456.660 1,501.854 ↓ 1.2 374,265 1

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

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

109. 231.003 1,835.823 ↑ 8.0 93,340 1

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

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

111. 270.902 7,610.470 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,308.474..7,610.470 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
112. 741.746 7,113.155 ↓ 11.9 232,241 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=7,101.407..7,113.155 rows=232,241 loops=1)

  • Sort Key: match_summary_3.epic_id
  • Sort Method: quicksort Memory: 24,288kB
113. 6,371.409 6,371.409 ↓ 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=1.725..6,371.409 rows=232,241 loops=1)

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

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

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

116. 215.573 7,595.106 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,351.006..7,595.106 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
117. 566.144 7,154.945 ↓ 9.5 185,195 1

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

  • Sort Key: match_summary_4.epic_id
  • Sort Method: quicksort Memory: 20,613kB
118. 6,588.801 6,588.801 ↓ 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=15.898..6,588.801 rows=185,195 loops=1)

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

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

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

121. 193.822 7,286.645 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,065.828..7,286.645 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
122. 480.318 6,866.702 ↓ 8.0 156,775 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=6,858.730..6,866.702 rows=156,775 loops=1)

  • Sort Key: match_summary_5.epic_id
  • Sort Method: quicksort Memory: 18,393kB
123. 6,386.384 6,386.384 ↓ 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.550..6,386.384 rows=156,775 loops=1)

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

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

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

126. 180.764 7,220.646 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,014.821..7,220.646 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
127. 400.496 6,844.012 ↓ 7.0 137,030 1

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

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 16,850kB
128. 6,443.516 6,443.516 ↓ 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.577..6,443.516 rows=137,030 loops=1)

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

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

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

131. 148.246 7,226.436 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=7,055.511..7,226.436 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
132. 237.372 6,853.410 ↓ 4.4 85,379 1

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

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 9,743kB
133. 6,616.038 6,616.038 ↓ 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.676..6,616.038 rows=85,379 loops=1)

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

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

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

136.          

CTE logins

137. 1,281.612 3,908.978 ↓ 2,498.5 499,699 1

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

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

  • Filter: (event_login > (now() - '90 days'::interval))
  • Rows Removed by Filter: 5,798,175
139. 158.860 98,705.786 ↑ 111.8 316,976 1

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

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
140. 98,546.926 98,546.926 ↑ 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=98,142.348..98,546.926 rows=316,976 loops=1)

141. 87.320 258,459.000 ↑ 85,266,974.8 412,240 1

Materialize (cost=1,029,883.86..615,154,809,760.67 rows=35,150,457,711,044 width=208) (actual time=257,151.272..258,459.000 rows=412,240 loops=1)

142. 384.580 258,371.680 ↑ 85,703,350.1 410,141 1

Merge Left Join (cost=1,029,883.86..527,278,665,483.06 rows=35,150,457,711,044 width=208) (actual time=257,151.267..258,371.680 rows=410,141 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
143. 183.245 9,997.062 ↑ 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=9,265.583..9,997.062 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
144. 418.985 7,192.665 ↓ 1.1 400,639 1

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

  • Merge Cond: (logins.epic_id = regs.epic_id)
145. 2,403.531 6,447.821 ↓ 2,498.5 499,699 1

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

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

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

147. 262.156 325.859 ↓ 1.1 402,669 1

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

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

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

149. 46.122 2,621.152 ↑ 9.7 67,258 1

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

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

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

151. 66.221 247,990.038 ↑ 11.6 511,265 1

Materialize (cost=913,019.75..942,666.77 rows=5,929,403 width=64) (actual time=247,885.282..247,990.038 rows=511,265 loops=1)

152. 657.965 247,923.817 ↑ 11.6 511,265 1

Sort (cost=913,019.75..927,843.26 rows=5,929,403 width=64) (actual time=247,884.900..247,923.817 rows=511,265 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 76,818kB
153. 247,265.852 247,265.852 ↑ 11.6 511,265 1

CTE Scan on all_unioned (cost=0.00..118,588.06 rows=5,929,403 width=64) (actual time=240,552.330..247,265.852 rows=511,265 loops=1)

Planning time : 1,553.205 ms
Execution time : 357,873.550 ms