explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w5mW

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 78,496.775 ↓ 200.0 200 1

Limit (cost=29,036.00..29,036.27 rows=1 width=1,258) (actual time=78,494.185..78,496.775 rows=200 loops=1)

2. 2.565 78,496.751 ↓ 200.0 200 1

GroupAggregate (cost=29,036.00..29,036.27 rows=1 width=1,258) (actual time=78,494.183..78,496.751 rows=200 loops=1)

  • Group Key: (timezone('Europe/London'::text, date_trunc('day'::text, timezone('Europe/London'::text, user_game_activity.interval_start_date)))), (timezone('Europe/London'::text, date_trunc('month'::text, timezone('Europe/London'::text, user_game_activity.interval_start_date)))), users.user_id, game_supplier.supplier_id, game_category.category_id, game.game_id, game_profile.game_profile_id, portal.portal_id, vip_level.vip_level_id, user_game_activity.client_mode, user_game_activity.play_mode, vip_schema.description
3. 231.181 78,494.186 ↓ 257.0 257 1

Sort (cost=29,036.00..29,036.00 rows=1 width=368) (actual time=78,494.079..78,494.186 rows=257 loops=1)

  • Sort Key: (timezone('Europe/London'::text, date_trunc('day'::text, timezone('Europe/London'::text, user_game_activity.interval_start_date)))), (timezone('Europe/London'::text, date_trunc('month'::text, timezone('Europe/London'::text, user_game_activity.interval_start_date)))), users.user_id, game_supplier.supplier_id, game_category.category_id, game.game_id, game_profile.game_profile_id, portal.portal_id, vip_level.vip_level_id, user_game_activity.client_mode, user_game_activity.play_mode, vip_schema.description
  • Sort Method: quicksort Memory: 23203kB
4. 1,138.515 78,263.005 ↓ 42,020.0 42,020 1

Nested Loop Left Join (cost=589.83..29,035.99 rows=1 width=368) (actual time=68,163.756..78,263.005 rows=42,020 loops=1)

  • Join Filter: ((organisation_ancestor.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 11261360
5. 213.689 74,561.270 ↓ 42,020.0 42,020 1

Nested Loop (cost=589.83..29,021.59 rows=1 width=361) (actual time=68,163.540..74,561.270 rows=42,020 loops=1)

  • Join Filter: ((game.category_id)::text = (game_category.category_id)::text)
  • Rows Removed by Join Filter: 15753
6. 88.808 74,263.541 ↓ 42,020.0 42,020 1

Nested Loop (cost=589.83..29,020.34 rows=1 width=351) (actual time=68,163.492..74,263.541 rows=42,020 loops=1)

  • Join Filter: (game.game_id = acl_allowed_game.game_id)
7. 257.584 73,880.593 ↓ 42,020.0 42,020 1

Nested Loop (cost=589.40..29,016.34 rows=1 width=359) (actual time=68,163.194..73,880.593 rows=42,020 loops=1)

  • Join Filter: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
  • Rows Removed by Join Filter: 383210
8. 5,126.217 73,496.949 ↓ 42,020.0 42,020 1

Nested Loop (cost=589.40..29,014.67 rows=1 width=349) (actual time=68,163.089..73,496.949 rows=42,020 loops=1)

  • Join Filter: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 11597520
9. 2.146 2.146 ↑ 1.0 277 1

Index Only Scan using pk_organisation on organisation (cost=0.27..24.43 rows=277 width=9) (actual time=0.050..2.146 rows=277 loops=1)

  • Heap Fetches: 57
10. 1,359.542 68,368.586 ↓ 42,020.0 42,020 277

Materialize (cost=589.13..28,986.09 rows=1 width=358) (actual time=221.426..246.818 rows=42,020 loops=277)

11. 14,006.773 67,009.044 ↓ 42,020.0 42,020 1

Nested Loop (cost=589.13..28,986.08 rows=1 width=358) (actual time=61,334.742..67,009.044 rows=42,020 loops=1)

  • Join Filter: (users.affiliate_id = affiliate.affiliate_id)
  • Rows Removed by Join Filter: 28867740
12. 2.879 2.879 ↑ 1.0 688 1

Seq Scan on affiliate (cost=0.00..25.88 rows=688 width=8) (actual time=0.015..2.879 rows=688 loops=1)

13. 3,315.169 52,999.392 ↓ 21,010.0 42,020 688

Materialize (cost=589.13..28,939.57 rows=2 width=374) (actual time=70.119..77.034 rows=42,020 loops=688)

14. 30.002 49,684.223 ↓ 21,010.0 42,020 1

Nested Loop (cost=589.13..28,939.56 rows=2 width=374) (actual time=48,241.156..49,684.223 rows=42,020 loops=1)

15. 19.121 49,570.181 ↓ 21,010.0 42,020 1

Nested Loop (cost=588.85..28,938.50 rows=2 width=324) (actual time=48,241.133..49,570.181 rows=42,020 loops=1)

  • Join Filter: (users.affiliate_id = acl_allowed_affiliate.affiliate_id)
16. 0.122 0.122 ↑ 1.0 1 1

Index Only Scan using pk_acl_allowed_affiliate on acl_allowed_affiliate (cost=0.29..4.31 rows=1 width=8) (actual time=0.116..0.122 rows=1 loops=1)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 1
17. 44.877 49,550.938 ↓ 78.0 42,020 1

Nested Loop (cost=588.57..28,927.46 rows=539 width=316) (actual time=48,241.008..49,550.938 rows=42,020 loops=1)

18. 44.344 49,422.021 ↓ 78.0 42,020 1

Nested Loop (cost=588.29..28,767.86 rows=539 width=301) (actual time=48,240.969..49,422.021 rows=42,020 loops=1)

19. 61.024 48,957.477 ↓ 78.0 42,020 1

Nested Loop (cost=587.86..28,479.28 rows=539 width=285) (actual time=48,240.893..48,957.477 rows=42,020 loops=1)

20. 27.352 48,518.273 ↓ 78.0 42,020 1

Merge Join (cost=587.44..28,096.80 rows=539 width=285) (actual time=48,240.778..48,518.273 rows=42,020 loops=1)

  • Merge Cond: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
21. 449.936 48,490.737 ↓ 78.0 42,020 1

Nested Loop (cost=1.14..28,674.12 rows=539 width=280) (actual time=48,240.582..48,490.737 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
  • Rows Removed by Join Filter: 1008480
22. 0.251 0.251 ↑ 1.0 25 1

Index Scan using vip_level_unq02 on vip_level (cost=0.14..12.51 rows=25 width=20) (actual time=0.049..0.251 rows=25 loops=1)

23. 210.358 48,040.550 ↓ 78.0 42,020 25

Materialize (cost=1.00..28,460.83 rows=539 width=268) (actual time=0.285..1,921.622 rows=42,020 loops=25)

24. 17.467 47,830.192 ↓ 78.0 42,020 1

Nested Loop (cost=1.00..28,458.13 rows=539 width=268) (actual time=7.100..47,830.192 rows=42,020 loops=1)

25. 0.985 11.010 ↓ 21.8 1,131 1

Nested Loop (cost=0.57..135.91 rows=52 width=77) (actual time=0.394..11.010 rows=1,131 loops=1)

26. 0.027 0.367 ↑ 1.0 1 1

Nested Loop (cost=0.28..18.31 rows=1 width=27) (actual time=0.280..0.367 rows=1 loops=1)

  • Join Filter: ((brand.organisation_id)::text = (acl_allowed_organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 266
27. 0.150 0.150 ↑ 1.0 1 1

Index Only Scan using pk_acl_allowed_organisation on acl_allowed_organisation (cost=0.28..4.30 rows=1 width=9) (actual time=0.146..0.150 rows=1 loops=1)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 1
28. 0.190 0.190 ↑ 1.0 267 1

Seq Scan on brand (cost=0.00..10.67 rows=267 width=18) (actual time=0.023..0.190 rows=267 loops=1)

29. 9.658 9.658 ↓ 10.4 1,131 1

Index Scan using portal_ix01 on portal (cost=0.29..116.51 rows=109 width=68) (actual time=0.105..9.658 rows=1,131 loops=1)

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
30. 47,801.715 47,801.715 ↑ 2.8 37 1,131

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.61 rows=105 width=199) (actual time=2.736..42.265 rows=37 loops=1,131)

  • Index Cond: (portal_id = portal.portal_id)
  • Filter: ((interval_start_date >= '2020-05-18 16:00:00+00'::timestamp with time zone) AND (interval_end_date <= '2020-05-19 16:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 7217
31. 0.055 0.184 ↑ 1.7 3 1

Sort (cost=1.11..1.12 rows=5 width=22) (actual time=0.183..0.184 rows=3 loops=1)

  • Sort Key: vip_schema.vip_schema_id
  • Sort Method: quicksort Memory: 25kB
32. 0.129 0.129 ↑ 1.0 5 1

Seq Scan on vip_schema (cost=0.00..1.05 rows=5 width=22) (actual time=0.127..0.129 rows=5 loops=1)

33. 378.180 378.180 ↑ 1.0 1 42,020

Index Scan using pk_user_registration on user_registration (cost=0.42..0.71 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=42,020)

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
34. 420.200 420.200 ↑ 1.0 1 42,020

Index Scan using pk_users on users (cost=0.42..0.54 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=42,020)

  • Index Cond: (user_id = user_registration.user_id)
35. 84.040 84.040 ↑ 1.0 1 42,020

Index Scan using pk_game_profile on game_profile (cost=0.28..0.30 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=42,020)

  • Index Cond: (game_profile_id = user_game_activity.game_profile_id)
36. 84.040 84.040 ↑ 1.0 1 42,020

Index Scan using pk_game on game (cost=0.28..0.53 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=42,020)

  • Index Cond: (game_id = game_profile.game_id)
37. 126.060 126.060 ↑ 3.0 10 42,020

Seq Scan on game_supplier (cost=0.00..1.30 rows=30 width=27) (actual time=0.002..0.003 rows=10 loops=42,020)

38. 294.140 294.140 ↑ 1.0 1 42,020

Index Only Scan using pk_acl_allowed_game on acl_allowed_game (cost=0.42..3.98 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=42,020)

  • Index Cond: ((user_id = 2168379) AND (game_id = game_profile.game_id) AND (permission = 'READ'::text))
  • Heap Fetches: 42020
39. 84.040 84.040 ↑ 11.0 1 42,020

Seq Scan on game_category (cost=0.00..1.11 rows=11 width=20) (actual time=0.002..0.002 rows=1 loops=42,020)

40. 2,563.220 2,563.220 ↑ 1.0 269 42,020

Seq Scan on organisation_ancestor (cost=0.00..11.01 rows=270 width=18) (actual time=0.004..0.061 rows=269 loops=42,020)

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
Planning time : 9,714.049 ms
Execution time : 78,513.001 ms