explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h7K4

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 1,285.577 ↓ 200.0 200 1

Limit (cost=312,418.06..312,419.02 rows=1 width=1,258) (actual time=1,279.829..1,285.577 rows=200 loops=1)

2. 0.000 1,285.547 ↓ 200.0 200 1

Finalize GroupAggregate (cost=312,418.06..312,419.02 rows=1 width=1,258) (actual time=1,279.827..1,285.547 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. 54.384 1,309.886 ↓ 116.5 233 1

Gather Merge (cost=312,418.06..312,418.53 rows=2 width=1,098) (actual time=1,279.755..1,309.886 rows=233 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1.844 1,255.502 ↓ 111.0 111 3 / 3

Partial GroupAggregate (cost=311,418.03..311,418.28 rows=1 width=1,098) (actual time=1,253.760..1,255.502 rows=111 loops=3)

  • 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
5. 69.861 1,253.658 ↓ 122.0 122 3 / 3

Sort (cost=311,418.03..311,418.04 rows=1 width=368) (actual time=1,253.628..1,253.658 rows=122 loops=3)

  • 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: 11129kB
  • Worker 0: Sort Method: quicksort Memory: 5932kB
  • Worker 1: Sort Method: quicksort Memory: 6144kB
6. 85.160 1,183.797 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.92..311,418.02 rows=1 width=368) (actual time=25.622..1,183.797 rows=14,007 loops=3)

7. 20.886 1,070.624 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.79..311,417.21 rows=1 width=347) (actual time=25.237..1,070.624 rows=14,007 loops=3)

8. 26.296 1,021.725 ↓ 14,007.0 14,007 3 / 3

Nested Loop Left Join (cost=889.65..311,417.05 rows=1 width=335) (actual time=25.161..1,021.725 rows=14,007 loops=3)

9. 29.155 897.382 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.37..311,416.58 rows=1 width=344) (actual time=25.067..897.382 rows=14,007 loops=3)

  • Join Filter: (game.game_id = acl_allowed_game.game_id)
10. 18.727 784.187 ↓ 14,007.0 14,007 3 / 3

Hash Join (cost=888.95..311,412.58 rows=1 width=352) (actual time=24.943..784.187 rows=14,007 loops=3)

  • Hash Cond: (users.affiliate_id = affiliate.affiliate_id)
11. 23.565 765.243 ↓ 63.1 14,007 3 / 3

Nested Loop (cost=880.32..311,403.11 rows=222 width=360) (actual time=24.627..765.243 rows=14,007 loops=3)

12. 30.991 601.611 ↓ 63.1 14,007 3 / 3

Nested Loop (cost=879.90..311,284.26 rows=222 width=344) (actual time=24.540..601.611 rows=14,007 loops=3)

13. 15.123 416.547 ↓ 63.1 14,007 3 / 3

Hash Join (cost=879.48..311,126.73 rows=222 width=344) (actual time=24.433..416.547 rows=14,007 loops=3)

  • Hash Cond: ((organisation.organisation_id)::text = (acl_allowed_organisation.organisation_id)::text)
14. 15.489 401.305 ↑ 3.8 16,242 3 / 3

Hash Join (cost=875.16..310,958.27 rows=61,588 width=353) (actual time=23.922..401.305 rows=16,242 loops=3)

  • Hash Cond: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
15. 16.534 385.521 ↑ 3.8 16,242 3 / 3

Hash Join (cost=860.93..310,779.90 rows=61,588 width=344) (actual time=23.590..385.521 rows=16,242 loops=3)

  • Hash Cond: ((portal.brand_id)::text = (brand.brand_id)::text)
16. 18.158 368.656 ↑ 3.8 16,242 3 / 3

Hash Join (cost=846.92..310,601.66 rows=61,588 width=344) (actual time=23.223..368.656 rows=16,242 loops=3)

  • Hash Cond: (user_game_activity.portal_id = portal.portal_id)
17. 15.017 331.205 ↑ 3.8 16,242 3 / 3

Hash Join (cost=154.31..309,747.33 rows=61,588 width=284) (actual time=3.795..331.205 rows=16,242 loops=3)

  • Hash Cond: ((game.category_id)::text = (game_category.category_id)::text)
18. 18.148 316.140 ↑ 3.8 16,242 3 / 3

Hash Join (cost=153.06..309,522.12 rows=61,588 width=274) (actual time=3.713..316.140 rows=16,242 loops=3)

  • Hash Cond: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
19. 16.280 297.935 ↑ 3.8 16,242 3 / 3

Hash Join (cost=151.38..309,335.94 rows=61,588 width=264) (actual time=3.625..297.935 rows=16,242 loops=3)

  • Hash Cond: (game_profile.game_id = game.game_id)
20. 19.907 279.556 ↑ 3.8 16,242 3 / 3

Hash Join (cost=61.93..309,084.27 rows=61,588 width=214) (actual time=1.477..279.556 rows=16,242 loops=3)

  • Hash Cond: (user_game_activity.game_profile_id = game_profile.game_profile_id)
21. 258.280 258.280 ↑ 3.8 16,242 3 / 3

Parallel Index Scan using user_game_activity_pkey on user_game_activity (cost=0.56..308,860.68 rows=61,588 width=199) (actual time=0.077..258.280 rows=16,242 loops=3)

  • Index Cond: (interval_start_date >= '2020-05-18 16:00:00+00'::timestamp with time zone)
  • Filter: (interval_end_date <= '2020-05-19 16:00:00+00'::timestamp with time zone)
  • Rows Removed by Filter: 30682
22. 0.340 1.369 ↑ 1.0 1,261 3 / 3

Hash (cost=45.61..45.61 rows=1,261 width=23) (actual time=1.368..1.369 rows=1,261 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
23. 1.029 1.029 ↑ 1.0 1,261 3 / 3

Seq Scan on game_profile (cost=0.00..45.61 rows=1,261 width=23) (actual time=0.032..1.029 rows=1,261 loops=3)

24. 0.503 2.099 ↑ 1.0 1,220 3 / 3

Hash (cost=74.20..74.20 rows=1,220 width=50) (actual time=2.099..2.099 rows=1,220 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
25. 1.596 1.596 ↑ 1.0 1,220 3 / 3

Seq Scan on game (cost=0.00..74.20 rows=1,220 width=50) (actual time=0.028..1.596 rows=1,220 loops=3)

26. 0.018 0.057 ↑ 1.0 30 3 / 3

Hash (cost=1.30..1.30 rows=30 width=27) (actual time=0.057..0.057 rows=30 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.039 0.039 ↑ 1.0 30 3 / 3

Seq Scan on game_supplier (cost=0.00..1.30 rows=30 width=27) (actual time=0.029..0.039 rows=30 loops=3)

28. 0.013 0.048 ↑ 1.0 11 3 / 3

Hash (cost=1.11..1.11 rows=11 width=20) (actual time=0.048..0.048 rows=11 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.035 0.035 ↑ 1.0 11 3 / 3

Seq Scan on game_category (cost=0.00..1.11 rows=11 width=20) (actual time=0.032..0.035 rows=11 loops=3)

30. 5.803 19.293 ↓ 1.0 14,411 3 / 3

Hash (cost=513.94..513.94 rows=14,294 width=68) (actual time=19.293..19.293 rows=14,411 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 1545kB
31. 13.490 13.490 ↓ 1.0 14,411 3 / 3

Seq Scan on portal (cost=0.00..513.94 rows=14,294 width=68) (actual time=0.037..13.490 rows=14,411 loops=3)

32. 0.107 0.331 ↑ 1.0 267 3 / 3

Hash (cost=10.67..10.67 rows=267 width=18) (actual time=0.331..0.331 rows=267 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
33. 0.224 0.224 ↑ 1.0 267 3 / 3

Seq Scan on brand (cost=0.00..10.67 rows=267 width=18) (actual time=0.040..0.224 rows=267 loops=3)

34. 0.084 0.295 ↑ 1.0 277 3 / 3

Hash (cost=10.77..10.77 rows=277 width=9) (actual time=0.295..0.295 rows=277 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
35. 0.211 0.211 ↑ 1.0 277 3 / 3

Seq Scan on organisation (cost=0.00..10.77 rows=277 width=9) (actual time=0.037..0.211 rows=277 loops=3)

36. 0.007 0.119 ↑ 1.0 1 3 / 3

Hash (cost=4.30..4.30 rows=1 width=9) (actual time=0.119..0.119 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.112 0.112 ↑ 1.0 1 3 / 3

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

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 3
38. 154.073 154.073 ↑ 1.0 1 42,020 / 3

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

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
39. 140.067 140.067 ↑ 1.0 1 42,020 / 3

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)
40. 0.014 0.217 ↑ 1.0 1 3 / 3

Hash (cost=8.61..8.61 rows=1 width=16) (actual time=0.216..0.217 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.007 0.203 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.56..8.61 rows=1 width=16) (actual time=0.202..0.203 rows=1 loops=3)

42. 0.131 0.131 ↑ 1.0 1 3 / 3

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

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 3
43. 0.065 0.065 ↑ 1.0 1 3 / 3

Index Only Scan using pk_affiliate on affiliate (cost=0.28..4.29 rows=1 width=8) (actual time=0.065..0.065 rows=1 loops=3)

  • Index Cond: (affiliate_id = acl_allowed_affiliate.affiliate_id)
  • Heap Fetches: 0
44. 84.040 84.040 ↑ 1.0 1 42,020 / 3

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

  • Index Cond: ((user_id = 2168379) AND (game_id = game_profile.game_id) AND (permission = 'READ'::text))
  • Heap Fetches: 42020
45. 98.047 98.047 ↑ 1.0 1 42,020 / 3

Index Scan using organisation_ancestor_unq02 on organisation_ancestor (cost=0.28..0.47 rows=1 width=18) (actual time=0.005..0.007 rows=1 loops=42,020)

  • Index Cond: ((organisation_id)::text = (organisation.organisation_id)::text)
  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 1
46. 28.013 28.013 ↑ 1.0 1 42,020 / 3

Index Scan using pk_vip_level on vip_level (cost=0.14..0.16 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=42,020)

  • Index Cond: (vip_level_id = user_game_activity.vip_level_id)
47. 28.013 28.013 ↑ 1.0 1 42,020 / 3

Index Scan using pk_vip_schema on vip_schema (cost=0.13..0.63 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=42,020)

  • Index Cond: ((vip_schema_id)::text = (vip_level.vip_schema_id)::text)
Planning time : 49.770 ms
Execution time : 1,316.476 ms