explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V6mw : Optimization for: Optimization for: plan #jIEy; plan #ykVf

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 387.590 169,651.979 ↑ 4,921,021,527,158.6 402,462 1

Merge Right Join (cost=636,240,850.81..44,561,961,978,341,760.00 rows=1,980,524,165,863,285,760 width=244) (actual time=168,020.478..169,651.979 rows=402,462 loops=1)

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

CTE regs

3. 420.314 420.314 ↓ 1.1 402,669 1

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

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

CTE last_step

5. 1,015.405 1,015.405 ↓ 1.6 402,669 1

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

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

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

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

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

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

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

12.          

CTE last_quit

13. 12.528 1,886.078 ↑ 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,777.397..1,886.078 rows=67,258 loops=1)

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

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

15. 310.703 1,786.828 ↑ 741.3 175,813 1

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

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

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

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

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

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

19. 190.925 233.384 ↓ 1.6 570,970 1

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

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

21.          

CTE customization

22. 304.852 21,665.241 ↓ 1,551.3 310,253 1

GroupAggregate (cost=685,876.33..75,672,039.50 rows=200 width=40) (actual time=18,144.180..21,665.241 rows=310,253 loops=1)

  • Group Key: char_customization_selected.epic_id
23. 920.034 21,360.389 ↑ 438.3 2,631,560 1

Merge Join (cost=685,876.33..69,904,377.00 rows=1,153,532,100 width=40) (actual time=18,144.154..21,360.389 rows=2,631,560 loops=1)

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

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

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

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

26. 418.611 20,223.787 ↓ 2.4 4,519,828 1

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

27. 14,672.892 19,805.176 ↓ 2.4 4,519,828 1

Sort (cost=644,990.56..649,745.89 rows=1,902,132 width=40) (actual time=17,949.028..19,805.176 rows=4,519,828 loops=1)

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

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

CTE all_unioned

30. 221.947 152,352.498 ↑ 3.8 501,763 1

Subquery Scan on all_unioned_1 (cost=505,261,459.54..517,511,800.56 rows=1,884,668 width=64) (actual time=148,166.626..152,352.498 rows=501,763 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 3,442,119
31. 2,280.989 152,130.551 ↑ 95.6 3,943,882 1

WindowAgg (cost=505,261,459.54..512,800,130.94 rows=376,933,570 width=76) (actual time=148,166.625..152,130.551 rows=3,943,882 loops=1)

32. 16,402.706 149,849.562 ↑ 95.6 3,943,882 1

Sort (cost=505,261,459.54..506,203,793.46 rows=376,933,570 width=68) (actual time=148,166.608..149,849.562 rows=3,943,882 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 259,376kB
33. 366.408 133,446.856 ↑ 95.6 3,943,882 1

Subquery Scan on foo_1 (cost=96,322.20..442,733,389.84 rows=376,933,570 width=68) (actual time=762.883..133,446.856 rows=3,943,882 loops=1)

34. 277.627 133,080.448 ↑ 95.6 3,943,882 1

Append (cost=96,322.20..438,964,054.14 rows=376,933,570 width=68) (actual time=762.881..133,080.448 rows=3,943,882 loops=1)

35. 0.222 763.174 ↓ 3.8 1,547 1

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

36. 2.606 762.952 ↓ 3.8 1,549 1

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

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

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

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

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

39. 22.756 22.756 ↓ 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=1.614..22.756 rows=1,550 loops=1)

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

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

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

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

42. 1,077.486 3,257.019 ↓ 2,498.5 499,699 1

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

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

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

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

45. 0.011 399.291 ↓ 0.0 0 1

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

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

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

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

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

48. 0.011 1,051.659 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
49. 1,051.648 1,051.648 ↓ 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=1,051.648..1,051.648 rows=0 loops=1)

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

CTE Scan on customization (cost=0.00..4.00 rows=200 width=68) (actual time=18,144.183..21,741.864 rows=310,253 loops=1)

51. 41.909 1,173.341 ↓ 1.6 402,669 1

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

52. 1,131.432 1,131.432 ↓ 1.6 402,669 1

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

53. 27.256 1,147.624 ↓ 401.6 80,316 1

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

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

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

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

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

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

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

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

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

59. 4.776 548.621 ↓ 72.0 14,409 1

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

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

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

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

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

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

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

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

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

65. 34.231 433.736 ↓ 12.3 374,263 1

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

66. 280.155 399.505 ↓ 12.3 374,263 1

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

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

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

68. 193.635 19,904.133 ↓ 721.9 144,370 1

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

69. 768.155 19,710.498 ↑ 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,034.476..19,710.498 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. 159.958 203.647 ↓ 1.1 402,668 1

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

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

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

72. 331.571 18,738.696 ↓ 1.1 3,714,214 1

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

73. 11,708.701 18,407.125 ↓ 1.1 3,714,214 1

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

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

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

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

  • Group Key: matchmaking_started.epic_id
76. 969.384 24,125.602 ↑ 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,627.996..24,125.602 rows=2,967,599 loops=1)

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

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

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

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

79. 447.546 22,960.547 ↑ 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,452.734..22,960.547 rows=4,968,025 loops=1)

80. 16,324.289 22,513.001 ↑ 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,452.729..22,513.001 rows=4,968,025 loops=1)

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

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

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

  • Group Key: matchmaking_cancelled.epic_id
83. 404.255 5,308.623 ↑ 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,731.855..5,308.623 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. 162.183 204.145 ↓ 1.1 402,668 1

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

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

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

86. 3,272.865 4,700.223 ↑ 1.2 992,970 1

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

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

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

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

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

  • Group Key: player_matched.epic_id
89. 280.569 6,656.013 ↑ 36.8 312,868 1

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

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

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

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

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

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

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

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

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

94. 60.045 7,776.232 ↓ 1,550.7 310,143 1

Group (cost=807,593.92..1,546,299.40 rows=200 width=68) (actual time=7,405.921..7,776.232 rows=310,143 loops=1)

  • Group Key: match_summary.epic_id
95. 275.505 7,716.187 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=7,405.917..7,716.187 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,019.861 7,223.169 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=7,207.450..7,223.169 rows=324,618 loops=1)

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

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

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

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

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

100. 50.890 6,879.011 ↓ 1,332.5 266,497 1

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

  • Group Key: match_summary_1.epic_id
101. 251.922 6,828.121 ↑ 22.1 267,940 1

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

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

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 34,019kB
103. 5,486.094 5,486.094 ↓ 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.063..5,486.094 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.904 215.568 ↓ 1.1 404,128 1

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

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

106. 177.714 2,834.620 ↑ 25,343.5 14,862 1

Merge Join (cost=123,034.32..20,840,547.57 rows=376,654,404 width=68) (actual time=2,592.955..2,834.620 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,444.907 1,488.598 ↓ 1.2 374,265 1

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

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

109. 37.761 1,168.308 ↑ 8.0 93,340 1

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

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

111. 41.735 6,067.563 ↓ 1,059.0 211,810 1

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

  • Group Key: match_summary_2.epic_id
112. 229.130 6,025.828 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,766.710..6,025.828 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
113. 712.439 5,564.514 ↓ 11.9 232,241 1

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

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 24,288kB
114. 4,852.075 4,852.075 ↓ 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.052..4,852.075 rows=232,241 loops=1)

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

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

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

117. 32.406 5,806.361 ↓ 818.8 163,763 1

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

  • Group Key: match_summary_3.epic_id
118. 203.962 5,773.955 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,542.031..5,773.955 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
119. 548.791 5,346.552 ↓ 9.5 185,195 1

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

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

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

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

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

123. 26.788 5,627.417 ↓ 677.5 135,497 1

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

  • Group Key: match_summary_4.epic_id
124. 187.915 5,600.629 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,386.049..5,600.629 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
125. 442.756 5,217.487 ↓ 8.0 156,775 1

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

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

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

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

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

129. 23.232 5,589.518 ↓ 580.1 116,025 1

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

  • Group Key: match_summary_5.epic_id
130. 178.807 5,566.286 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,362.184..5,566.286 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
131. 404.270 5,160.366 ↓ 7.0 137,030 1

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

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

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

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

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

135. 13.113 5,319.825 ↓ 332.5 66,505 1

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

  • Group Key: match_summary_6.epic_id
136. 147.859 5,306.712 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=32) (actual time=5,135.766..5,306.712 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
137. 234.154 4,924.612 ↓ 4.4 85,379 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=40) (actual time=4,920.360..4,924.612 rows=85,379 loops=1)

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 9,743kB
138. 4,690.458 4,690.458 ↓ 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.067..4,690.458 rows=85,379 loops=1)

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

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

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

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

141.          

CTE logins

142. 1,261.198 4,058.259 ↓ 2,498.5 499,699 1

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

  • Group Key: logins_cleaned_1.epic_id
143. 2,797.061 2,797.061 ↑ 1.3 2,289,658 1

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

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

CTE first_game_mode

145. 268.234 6,926.285 ↑ 111.8 316,976 1

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

  • Merge Cond: (match_summary_7.epic_id = regs_17.epic_id)
146. 1,032.065 5,928.485 ↓ 16.7 324,618 1

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

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 37,649kB
147. 4,896.420 4,896.420 ↓ 16.7 324,618 1

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

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

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

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

150. 154.958 7,171.212 ↑ 111.8 316,976 1

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

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
151. 7,016.254 7,016.254 ↑ 111.8 316,976 1

CTE Scan on first_game_mode (cost=0.00..709,063.72 rows=35,453,186 width=64) (actual time=6,621.281..7,016.254 rows=316,976 loops=1)

152. 77.846 162,093.177 ↑ 27,760,673.1 402,462 1

Materialize (cost=391,487.27..195,541,931,202.67 rows=11,172,616,000,510 width=208) (actual time=160,873.226..162,093.177 rows=402,462 loops=1)

153. 354.756 162,015.331 ↑ 27,886,990.5 400,639 1

Merge Left Join (cost=391,487.27..167,610,391,201.39 rows=11,172,616,000,510 width=208) (actual time=160,873.220..162,015.331 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
154. 170.413 8,880.193 ↑ 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=8,190.047..8,880.193 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
155. 393.956 6,763.585 ↓ 1.1 400,639 1

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

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

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

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

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

158. 209.262 260.956 ↓ 1.1 402,669 1

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

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

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

160. 35.177 1,946.195 ↑ 9.7 67,258 1

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

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

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

162. 60.195 152,780.382 ↑ 3.8 501,763 1

Materialize (cost=274,623.17..284,046.51 rows=1,884,668 width=64) (actual time=152,683.166..152,780.382 rows=501,763 loops=1)

163. 243.384 152,720.187 ↑ 3.8 501,763 1

Sort (cost=274,623.17..279,334.84 rows=1,884,668 width=64) (actual time=152,683.163..152,720.187 rows=501,763 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 75,482kB
164. 152,476.803 152,476.803 ↑ 3.8 501,763 1

CTE Scan on all_unioned (cost=0.00..37,693.36 rows=1,884,668 width=64) (actual time=148,166.629..152,476.803 rows=501,763 loops=1)

Planning time : 4.546 ms
Execution time : 169,764.336 ms