explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tAf7

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 47,250.334 ↓ 200.0 200 1

Limit (cost=29,611.89..29,612.16 rows=1 width=1,258) (actual time=47,248.004..47,250.334 rows=200 loops=1)

2. 2.332 47,250.311 ↓ 200.0 200 1

GroupAggregate (cost=29,611.89..29,612.16 rows=1 width=1,258) (actual time=47,248.002..47,250.311 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. 250.608 47,247.979 ↓ 257.0 257 1

Sort (cost=29,611.89..29,611.89 rows=1 width=368) (actual time=47,247.930..47,247.979 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. 496.170 46,997.371 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,611.88 rows=1 width=368) (actual time=7.551..46,997.371 rows=42,020 loops=1)

  • Join Filter: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
5. 264.742 46,417.161 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,610.75 rows=1 width=347) (actual time=7.521..46,417.161 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
6. 1,079.331 46,068.379 ↓ 42,020.0 42,020 1

Nested Loop Left Join (cost=2.56..29,609.19 rows=1 width=335) (actual time=7.502..46,068.379 rows=42,020 loops=1)

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

Nested Loop (cost=2.56..29,594.80 rows=1 width=344) (actual time=7.373..42,047.648 rows=42,020 loops=1)

  • Join Filter: ((game.category_id)::text = (game_category.category_id)::text)
  • Rows Removed by Join Filter: 15753
8. 322.539 41,707.992 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,593.56 rows=1 width=334) (actual time=7.358..41,707.992 rows=42,020 loops=1)

  • Join Filter: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
  • Rows Removed by Join Filter: 383210
9. 1,290.232 41,259.393 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,591.88 rows=1 width=324) (actual time=7.339..41,259.393 rows=42,020 loops=1)

  • Join Filter: (game_profile.game_id = game.game_id)
  • Rows Removed by Join Filter: 14730773
10. 148.101 36,355.441 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,502.43 rows=1 width=290) (actual time=7.310..36,355.441 rows=42,020 loops=1)

11. 1,410.622 35,829.160 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,498.44 rows=1 width=282) (actual time=7.221..35,829.160 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. 72.367 31,014.918 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,437.07 rows=1 width=267) (actual time=7.068..31,014.918 rows=42,020 loops=1)

  • Join Filter: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
13. 91.379 30,942.551 ↓ 78.7 42,020 1

Nested Loop (cost=1.85..29,424.75 rows=534 width=283) (actual time=7.040..30,942.551 rows=42,020 loops=1)

14. 100.425 30,725.112 ↓ 78.7 42,020 1

Nested Loop (cost=1.57..29,268.55 rows=534 width=275) (actual time=7.024..30,725.112 rows=42,020 loops=1)

15. 134.996 30,120.447 ↓ 78.7 42,020 1

Nested Loop (cost=1.15..28,982.66 rows=534 width=259) (actual time=7.005..30,120.447 rows=42,020 loops=1)

16. 36.540 29,481.211 ↓ 78.7 42,020 1

Nested Loop (cost=0.72..28,603.73 rows=534 width=259) (actual time=6.983..29,481.211 rows=42,020 loops=1)

17. 1.133 11.527 ↓ 21.8 1,131 1

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

18. 0.024 1.789 ↑ 1.0 1 1

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

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

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

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

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

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

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

22. 0.014 0.014 ↑ 11.5 24 1

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

23. 8.605 8.605 ↓ 10.4 1,131 1

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

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
24. 29,433.144 29,433.144 ↑ 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.504..26.024 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: 7213
25. 504.240 504.240 ↑ 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.012..0.012 rows=1 loops=42,020)

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

Index Scan using pk_users on users (cost=0.42..0.54 rows=1 width=24) (actual time=0.012..0.012 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.019 0.019 ↑ 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.019..0.019 rows=1 loops=1)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 1
30. 3,403.620 3,403.620 ↑ 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.081 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,613.720 3,613.720 ↑ 3.5 352 42,020

Seq Scan on game (cost=0.00..74.20 rows=1,220 width=50) (actual time=0.003..0.086 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,941.400 2,941.400 ↑ 1.0 269 42,020

Seq Scan on organisation_ancestor (cost=0.00..11.01 rows=270 width=18) (actual time=0.005..0.070 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 : 22.320 ms
Execution time : 47,253.551 ms