explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGvb

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 70,469.578 ↓ 200.0 200 1

Limit (cost=29,611.67..29,611.93 rows=1 width=1,258) (actual time=70,467.278..70,469.578 rows=200 loops=1)

2. 2.411 70,469.549 ↓ 200.0 200 1

GroupAggregate (cost=29,611.67..29,611.93 rows=1 width=1,258) (actual time=70,467.269..70,469.549 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. 282.120 70,467.138 ↓ 257.0 257 1

Sort (cost=29,611.67..29,611.67 rows=1 width=368) (actual time=70,467.098..70,467.138 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. 491.681 70,185.018 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,611.66 rows=1 width=368) (actual time=28.305..70,185.018 rows=42,020 loops=1)

  • Join Filter: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
5. 255.335 69,609.297 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,610.53 rows=1 width=347) (actual time=27.839..69,609.297 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
6. 1,061.335 69,269.922 ↓ 42,020.0 42,020 1

Nested Loop Left Join (cost=2.56..29,608.97 rows=1 width=335) (actual time=27.791..69,269.922 rows=42,020 loops=1)

  • Join Filter: ((organisation_ancestor.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 11261360
7. 254.127 65,477.287 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,594.58 rows=1 width=344) (actual time=27.657..65,477.287 rows=42,020 loops=1)

  • Join Filter: ((game.category_id)::text = (game_category.category_id)::text)
  • Rows Removed by Join Filter: 15753
8. 315.752 65,139.120 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,593.33 rows=1 width=334) (actual time=27.623..65,139.120 rows=42,020 loops=1)

  • Join Filter: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
  • Rows Removed by Join Filter: 383210
9. 1,192.221 64,697.308 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,591.66 rows=1 width=324) (actual time=27.588..64,697.308 rows=42,020 loops=1)

  • Join Filter: (game_profile.game_id = game.game_id)
  • Rows Removed by Join Filter: 14730773
10. 143.242 60,059.447 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,502.21 rows=1 width=290) (actual time=27.502..60,059.447 rows=42,020 loops=1)

11. 1,371.965 59,538.025 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,498.22 rows=1 width=282) (actual time=27.237..59,538.025 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.game_profile_id = game_profile.game_profile_id)
  • Rows Removed by Join Filter: 18116374
12. 69.126 54,888.500 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,436.85 rows=1 width=267) (actual time=26.966..54,888.500 rows=42,020 loops=1)

  • Join Filter: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
13. 93.877 54,819.374 ↓ 78.7 42,020 1

Nested Loop (cost=1.85..29,424.53 rows=534 width=283) (actual time=26.686..54,819.374 rows=42,020 loops=1)

14. 112.558 54,599.437 ↓ 78.7 42,020 1

Nested Loop (cost=1.57..29,268.33 rows=534 width=275) (actual time=26.625..54,599.437 rows=42,020 loops=1)

15. 145.321 53,940.619 ↓ 78.7 42,020 1

Nested Loop (cost=1.15..28,982.44 rows=534 width=259) (actual time=26.511..53,940.619 rows=42,020 loops=1)

16. 38.358 53,207.018 ↓ 78.7 42,020 1

Nested Loop (cost=0.72..28,603.53 rows=534 width=259) (actual time=26.364..53,207.018 rows=42,020 loops=1)

17. 1.142 13.922 ↓ 21.8 1,131 1

Nested Loop (cost=0.29..281.61 rows=52 width=68) (actual time=0.928..13.922 rows=1,131 loops=1)

18. 0.004 2.140 ↑ 1.0 1 1

Nested Loop (cost=0.00..164.01 rows=1 width=18) (actual time=0.769..2.140 rows=1 loops=1)

  • Join Filter: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 23
19. 0.028 2.108 ↑ 1.0 1 1

Nested Loop (cost=0.00..149.78 rows=1 width=27) (actual time=0.738..2.108 rows=1 loops=1)

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

Seq Scan on acl_allowed_organisation (cost=0.00..135.77 rows=1 width=9) (actual time=0.493..1.786 rows=1 loops=1)

  • Filter: (((permission)::text = 'READ'::text) AND (user_id = 2168379))
  • Rows Removed by Filter: 5839
21. 0.294 0.294 ↑ 1.0 267 1

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

22. 0.028 0.028 ↑ 11.5 24 1

Seq Scan on organisation (cost=0.00..10.77 rows=277 width=9) (actual time=0.023..0.028 rows=24 loops=1)

23. 10.640 10.640 ↓ 10.4 1,131 1

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

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
24. 53,154.738 53,154.738 ↑ 2.8 37 1,131

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.60 rows=105 width=199) (actual time=3.363..46.998 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: 7208
25. 588.280 588.280 ↑ 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.014..0.014 rows=1 loops=42,020)

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

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

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

Index Only Scan using pk_affiliate on affiliate (cost=0.28..0.29 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=42,020)

  • Index Cond: (affiliate_id = users.affiliate_id)
  • Heap Fetches: 0
28. 0.000 0.000 ↑ 1.0 1 42,020

Materialize (cost=0.29..4.31 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=42,020)

29. 0.259 0.259 ↑ 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.259..0.259 rows=1 loops=1)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 1
30. 3,277.560 3,277.560 ↑ 2.9 432 42,020

Seq Scan on game_profile (cost=0.00..45.61 rows=1,261 width=23) (actual time=0.003..0.078 rows=432 loops=42,020)

31. 378.180 378.180 ↑ 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.009..0.009 rows=1 loops=42,020)

  • Index Cond: ((user_id = 2168379) AND (game_id = game_profile.game_id) AND (permission = 'READ'::text))
  • Heap Fetches: 42020
32. 3,445.640 3,445.640 ↑ 3.5 352 42,020

Seq Scan on game (cost=0.00..74.20 rows=1,220 width=50) (actual time=0.003..0.082 rows=352 loops=42,020)

33. 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)

34. 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)

35. 2,731.300 2,731.300 ↑ 1.0 269 42,020

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

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
36. 84.040 84.040 ↑ 25.0 1 42,020

Seq Scan on vip_level (cost=0.00..1.25 rows=25 width=20) (actual time=0.002..0.002 rows=1 loops=42,020)

37. 84.040 84.040 ↑ 5.0 1 42,020

Seq Scan on vip_schema (cost=0.00..1.05 rows=5 width=22) (actual time=0.002..0.002 rows=1 loops=42,020)

Planning time : 37.973 ms
Execution time : 70,476.053 ms