explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 396.844 175,455.783 ↑ 58,621,996,336,853.0 412,240 1

Merge Right Join (cost=1,763,196,283.37..543,744,511,528,900,416.00 rows=24,166,331,769,904,295,936 width=244) (actual time=173,798.844..175,455.783 rows=412,240 loops=1)

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

CTE regs

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

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

CTE last_step

5. 479.802 479.802 ↓ 1.6 402,669 1

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

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

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

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

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

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

  • Sort Key: regs_1.epic_id
  • Sort Method: quicksort Memory: 43,747kB
11. 43.132 43.132 ↓ 1.1 402,669 1

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

12.          

CTE last_quit

13. 12.699 1,956.510 ↑ 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,847.745..1,956.510 rows=67,258 loops=1)

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

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

15. 308.126 1,857.150 ↑ 741.3 175,813 1

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

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

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

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

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

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

19. 185.877 229.087 ↓ 1.6 570,970 1

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

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

21.          

CTE first_game_mode

22. 267.324 8,858.735 ↑ 111.8 316,976 1

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

  • Merge Cond: (match_summary.epic_id = regs_3.epic_id)
23. 1,069.637 7,764.268 ↓ 16.7 324,618 1

Sort (cost=766,708.15..766,756.87 rows=19,487 width=64) (actual time=7,747.053..7,764.268 rows=324,618 loops=1)

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

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

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

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

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

27.          

CTE customization

28. 302.770 22,658.391 ↓ 1,551.3 310,253 1

GroupAggregate (cost=685,876.33..75,672,039.50 rows=200 width=40) (actual time=19,027.382..22,658.391 rows=310,253 loops=1)

  • Group Key: char_customization_selected.epic_id
29. 942.810 22,355.621 ↑ 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=19,027.355..22,355.621 rows=2,631,560 loops=1)

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

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

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

32. 445.247 21,198.190 ↓ 2.4 4,519,828 1

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

33. 15,285.182 20,752.943 ↓ 2.4 4,519,828 1

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

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

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

CTE all_unioned

36. 400.621 155,865.769 ↑ 45.0 511,265 1

Subquery Scan on all_unioned_1 (cost=1,491,497,413.33..1,640,975,940.19 rows=22,996,696 width=64) (actual time=148,121.570..155,865.769 rows=511,265 loops=1)

  • Filter: (all_unioned_1.last_event = 1)
  • Rows Removed by Filter: 6,510,991
37. 4,114.844 155,465.148 ↑ 655.0 7,022,256 1

WindowAgg (cost=1,491,497,413.33..1,583,484,199.09 rows=4,599,339,288 width=76) (actual time=148,121.568..155,465.148 rows=7,022,256 loops=1)

38. 18,161.595 151,350.304 ↑ 655.0 7,022,256 1

Sort (cost=1,491,497,413.33..1,502,995,761.55 rows=4,599,339,288 width=68) (actual time=148,121.551..151,350.304 rows=7,022,256 loops=1)

  • Sort Key: foo_1.epic_id, foo_1.event_rank DESC
  • Sort Method: external merge Disk: 462,064kB
39. 708.926 133,188.709 ↑ 655.0 7,022,256 1

Subquery Scan on foo_1 (cost=96,322.20..537,737,493.49 rows=4,599,339,288 width=68) (actual time=813.726..133,188.709 rows=7,022,256 loops=1)

40. 506.489 132,479.783 ↑ 655.0 7,022,256 1

Append (cost=96,322.20..491,744,100.61 rows=4,599,339,288 width=68) (actual time=813.723..132,479.783 rows=7,022,256 loops=1)

41. 0.226 814.025 ↓ 3.8 1,547 1

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

42. 2.585 813.799 ↓ 3.8 1,549 1

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

  • Sort Key: epic_auth_registrations.epic_id
  • Sort Method: quicksort Memory: 266kB
43. 120.388 811.214 ↓ 3.8 1,549 1

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

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

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

45. 0.368 0.368 ↓ 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.033..0.368 rows=1,550 loops=1)

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

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

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

48. 1,106.133 3,345.708 ↓ 2,498.5 499,699 1

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

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

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

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

51. 0.009 106.179 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event.epic_id
  • Sort Method: quicksort Memory: 25kB
52. 106.170 106.170 ↓ 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.170..106.170 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
53. 0.001 305.433 ↓ 0.0 0 1

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

54. 0.006 305.432 ↓ 0.0 0 1

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

  • Sort Key: map_loading_time_event_client.epic_id
  • Sort Method: quicksort Memory: 25kB
55. 305.426 305.426 ↓ 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=305.426..305.426 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
56. 22,736.939 22,736.939 ↓ 1,551.3 310,253 1

CTE Scan on customization (cost=0.00..4.00 rows=200 width=68) (actual time=19,027.385..22,736.939 rows=310,253 loops=1)

57. 37.862 625.693 ↓ 1.6 402,669 1

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

58. 587.831 587.831 ↓ 1.6 402,669 1

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

59. 27.214 1,148.618 ↓ 401.6 80,316 1

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

  • Group Key: client_disconnected.epic_id, 'Connection Timeout'::character varying, 16
60. 204.214 1,121.404 ↑ 737.7 141,777 1

Merge Join (cost=84,102.11..6,360,107.42 rows=104,585,717 width=68) (actual time=859.564..1,121.404 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
61. 602.941 709.366 ↓ 1.2 215,073 1

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

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

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

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

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

  • Sort Key: regs_5.epic_id
  • Sort Method: quicksort Memory: 43,747kB
64. 43.764 43.764 ↓ 1.1 402,669 1

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

65. 4.509 447.440 ↓ 72.0 14,409 1

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

  • Group Key: client_disconnected_client.epic_id, 'Client Disconnect'::character varying, 17
66. 112.023 442.931 ↑ 3,352.4 21,316 1

Merge Join (cost=65,619.45..4,353,775.46 rows=71,459,447 width=68) (actual time=308.583..442.931 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
67. 67.080 113.942 ↑ 3.7 32,020 1

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

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

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

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

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

71. 43.134 428.643 ↓ 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.832..428.643 rows=374,263 loops=1)

72. 291.071 385.509 ↓ 12.3 374,263 1

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

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

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

74. 204.711 20,505.602 ↓ 721.9 144,370 1

Group (cost=1,018,674.21..127,997,854.35 rows=200 width=68) (actual time=17,543.072..20,505.602 rows=144,370 loops=1)

  • Group Key: blueprint_crafted.epic_id
75. 765.794 20,300.891 ↑ 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=17,543.070..20,300.891 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
76. 166.249 209.579 ↓ 1.1 402,668 1

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

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

78. 339.023 19,325.518 ↓ 1.1 3,714,214 1

Materialize (cost=977,788.44..994,537.82 rows=3,349,875 width=40) (actual time=17,355.427..19,325.518 rows=3,714,214 loops=1)

79. 12,139.597 18,986.495 ↓ 1.1 3,714,214 1

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

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 10,942,116
81. 1,100.423 24,750.829 ↑ 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=20,932.970..24,750.829 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
82. 152.621 194.071 ↓ 1.1 402,669 1

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

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

84. 477.338 23,456.335 ↑ 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,760.233..23,456.335 rows=4,968,025 loops=1)

85. 16,990.274 22,978.997 ↑ 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,760.227..22,978.997 rows=4,968,025 loops=1)

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 13,683,135
87. 419.385 4,944.486 ↑ 1,158.2 621,876 1

Merge Join (cost=287,850.67..43,504,169.50 rows=720,241,658 width=68) (actual time=4,343.966..4,944.486 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
88. 164.582 208.574 ↓ 1.1 402,668 1

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

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

90. 3,438.622 4,316.527 ↑ 1.2 992,970 1

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

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

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

  • Filter: (event > (now() - '90 days'::interval))
  • Rows Removed by Filter: 2,906,860
92. 285.871 6,501.531 ↑ 36.8 312,868 1

Merge Join (cost=596,237.96..1,288,040.37 rows=11,528,456 width=68) (actual time=6,178.769..6,501.531 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
93. 1,054.170 5,999.094 ↓ 17.1 325,044 1

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

  • Sort Key: player_matched.epic_id
  • Sort Method: quicksort Memory: 37,683kB
94. 4,944.924 4,944.924 ↓ 17.1 325,044 1

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

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

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

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

97. 282.764 7,152.885 ↑ 37.9 311,944 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=6,834.906..7,152.885 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
98. 1,027.421 6,652.801 ↓ 16.7 324,618 1

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

  • Sort Key: match_summary_1.epic_id
  • Sort Method: quicksort Memory: 37,649kB
99. 5,625.380 5,625.380 ↓ 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.062..5,625.380 rows=324,618 loops=1)

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

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

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

102. 314.552 6,460.549 ↑ 22.1 267,940 1

Merge Join (cost=806,851.00..1,161,449.78 rows=5,909,168 width=68) (actual time=6,104.555..6,460.549 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
103. 865.130 5,926.434 ↓ 28.5 278,144 1

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

  • Sort Key: match_summary_2.epic_id
  • Sort Method: quicksort Memory: 34,019kB
104. 5,061.304 5,061.304 ↓ 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.066..5,061.304 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
105. 175.637 219.563 ↓ 1.1 404,128 1

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

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

107. 180.061 2,929.660 ↑ 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,686.733..2,929.660 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
108. 1,525.249 1,569.518 ↓ 1.2 374,265 1

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

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

110. 37.601 1,180.081 ↑ 8.0 93,340 1

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

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

112. 252.996 6,078.623 ↑ 55.5 213,078 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=5,791.049..6,078.623 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
113. 723.947 5,602.897 ↓ 11.9 232,241 1

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

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

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

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

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

117. 215.806 5,956.265 ↑ 71.7 164,729 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=5,711.128..5,956.265 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
118. 734.814 5,541.893 ↓ 9.5 185,195 1

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

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

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

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

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

122. 189.724 5,665.106 ↑ 86.7 136,273 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=5,449.159..5,665.106 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
123. 461.124 5,257.202 ↓ 8.0 156,775 1

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

  • Sort Key: match_summary_5.epic_id
  • Sort Method: quicksort Memory: 18,393kB
124. 4,796.078 4,796.078 ↓ 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.067..4,796.078 rows=156,775 loops=1)

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

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

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

127. 181.299 5,607.525 ↑ 101.3 116,710 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=5,400.902..5,607.525 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
128. 395.745 5,201.809 ↓ 7.0 137,030 1

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

  • Sort Key: match_summary_6.epic_id
  • Sort Method: quicksort Memory: 16,850kB
129. 4,806.064 4,806.064 ↓ 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.068..4,806.064 rows=137,030 loops=1)

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

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

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

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

132. 153.347 5,461.554 ↑ 176.8 66,851 1

Merge Join (cost=807,593.92..1,516,755.08 rows=11,817,729 width=68) (actual time=5,285.161..5,461.554 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
133. 235.874 5,115.272 ↓ 4.4 85,379 1

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

  • Sort Key: match_summary_7.epic_id
  • Sort Method: quicksort Memory: 9,743kB
134. 4,879.398 4,879.398 ↓ 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.069..4,879.398 rows=85,379 loops=1)

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

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

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

137.          

CTE logins

138. 1,311.440 4,243.542 ↓ 2,498.5 499,699 1

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

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

  • Filter: (event_login > (now() - '90 days'::interval))
  • Rows Removed by Filter: 5,798,175
140. 164.840 9,121.266 ↑ 111.8 316,976 1

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

  • Sort Key: first_game_mode.epic_id
  • Sort Method: quicksort Memory: 37,052kB
141. 8,956.426 8,956.426 ↑ 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=8,554.286..8,956.426 rows=316,976 loops=1)

142. 81.916 165,937.673 ↑ 330,700,864.7 412,240 1

Materialize (cost=3,882,780.20..2,385,766,874,186.66 rows=136,328,124,473,238 width=208) (actual time=164,701.538..165,937.673 rows=412,240 loops=1)

143. 360.424 165,855.757 ↑ 332,393,309.8 410,141 1

Merge Left Join (cost=3,882,780.20..2,044,946,563,003.57 rows=136,328,124,473,238 width=208) (actual time=164,701.534..165,855.757 rows=410,141 loops=1)

  • Merge Cond: (regs.epic_id = all_unioned.epic_id)
144. 172.065 9,191.156 ↑ 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,498.170..9,191.156 rows=400,639 loops=1)

  • Merge Cond: (regs.epic_id = last_quit.epic_id)
145. 395.998 7,002.230 ↓ 1.1 400,639 1

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

  • Merge Cond: (logins.epic_id = regs.epic_id)
146. 1,985.226 6,351.152 ↓ 2,498.5 499,699 1

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

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

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

148. 207.611 255.080 ↓ 1.1 402,669 1

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

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

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

150. 35.592 2,016.861 ↑ 9.7 67,258 1

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

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

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

152. 62.582 156,304.177 ↑ 45.0 511,265 1

Materialize (cost=3,765,916.10..3,880,899.58 rows=22,996,696 width=64) (actual time=156,203.357..156,304.177 rows=511,265 loops=1)

153. 245.437 156,241.595 ↑ 45.0 511,265 1

Sort (cost=3,765,916.10..3,823,407.84 rows=22,996,696 width=64) (actual time=156,203.355..156,241.595 rows=511,265 loops=1)

  • Sort Key: all_unioned.epic_id
  • Sort Method: quicksort Memory: 76,818kB
154. 155,996.158 155,996.158 ↑ 45.0 511,265 1

CTE Scan on all_unioned (cost=0.00..459,933.92 rows=22,996,696 width=64) (actual time=148,121.573..155,996.158 rows=511,265 loops=1)

Planning time : 4.974 ms
Execution time : 175,571.573 ms