explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sa4L

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

Limit (cost=312,414.05..312,415.01 rows=1 width=1,258) (actual time=1,921.997..1,927.270 rows=200 loops=1)

2. 0.000 1,927.240 ↓ 200.0 200 1

Finalize GroupAggregate (cost=312,414.05..312,415.01 rows=1 width=1,258) (actual time=1,921.995..1,927.240 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. 58.524 1,960.264 ↓ 118.0 236 1

Gather Merge (cost=312,414.05..312,414.53 rows=2 width=1,098) (actual time=1,921.920..1,960.264 rows=236 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 2.062 1,901.740 ↓ 114.0 114 3 / 3

Partial GroupAggregate (cost=311,414.03..311,414.28 rows=1 width=1,098) (actual time=1,899.778..1,901.740 rows=114 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. 74.112 1,899.678 ↓ 126.0 126 3 / 3

Sort (cost=311,414.03..311,414.03 rows=1 width=368) (actual time=1,899.639..1,899.678 rows=126 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: 9380kB
  • Worker 0: Sort Method: quicksort Memory: 7110kB
  • Worker 1: Sort Method: quicksort Memory: 6714kB
6. 97.511 1,825.566 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.92..311,414.02 rows=1 width=368) (actual time=27.343..1,825.566 rows=14,007 loops=3)

7. 26.440 1,700.042 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.79..311,413.21 rows=1 width=347) (actual time=26.973..1,700.042 rows=14,007 loops=3)

8. 25.539 1,645.589 ↓ 14,007.0 14,007 3 / 3

Nested Loop Left Join (cost=889.65..311,413.05 rows=1 width=335) (actual time=26.909..1,645.589 rows=14,007 loops=3)

9. 34.692 1,507.997 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=889.37..311,412.57 rows=1 width=344) (actual time=26.802..1,507.997 rows=14,007 loops=3)

  • Join Filter: (game.game_id = acl_allowed_game.game_id)
10. 22.009 1,375.258 ↓ 14,007.0 14,007 3 / 3

Hash Join (cost=888.95..311,408.58 rows=1 width=352) (actual time=26.664..1,375.258 rows=14,007 loops=3)

  • Hash Cond: (users.affiliate_id = affiliate.affiliate_id)
11. 37.781 1,353.021 ↓ 63.1 14,007 3 / 3

Nested Loop (cost=880.32..311,399.11 rows=222 width=360) (actual time=26.338..1,353.021 rows=14,007 loops=3)

12. 34.809 1,147.160 ↓ 63.1 14,007 3 / 3

Nested Loop (cost=879.90..311,280.26 rows=222 width=344) (actual time=26.245..1,147.160 rows=14,007 loops=3)

13. 16.769 916.258 ↓ 63.1 14,007 3 / 3

Hash Join (cost=879.48..311,122.73 rows=222 width=344) (actual time=26.112..916.258 rows=14,007 loops=3)

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

Hash Join (cost=875.16..310,954.27 rows=61,588 width=353) (actual time=24.217..899.360 rows=16,242 loops=3)

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

Hash Join (cost=860.93..310,775.90 rows=61,588 width=344) (actual time=23.864..881.994 rows=16,242 loops=3)

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

Hash Join (cost=846.92..310,597.66 rows=61,588 width=344) (actual time=23.525..864.227 rows=16,242 loops=3)

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

Hash Join (cost=154.31..309,743.33 rows=61,588 width=284) (actual time=3.833..823.993 rows=16,242 loops=3)

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

Hash Join (cost=153.06..309,518.12 rows=61,588 width=274) (actual time=3.738..807.451 rows=16,242 loops=3)

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

Hash Join (cost=151.38..309,331.94 rows=61,588 width=264) (actual time=3.631..786.084 rows=16,242 loops=3)

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

Hash Join (cost=61.93..309,080.27 rows=61,588 width=214) (actual time=1.605..765.194 rows=16,242 loops=3)

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

Parallel Index Scan using user_game_activity_pkey on user_game_activity (cost=0.56..308,856.68 rows=61,588 width=199) (actual time=0.224..739.969 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: 29109
22. 0.395 1.335 ↑ 1.0 1,261 3 / 3

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

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

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

24. 0.569 1.974 ↑ 1.0 1,220 3 / 3

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

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

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

26. 0.018 0.059 ↑ 1.0 30 3 / 3

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

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

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

28. 0.011 0.049 ↑ 1.0 11 3 / 3

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

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

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

30. 6.868 19.506 ↓ 1.0 14,411 3 / 3

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

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

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

32. 0.096 0.299 ↑ 1.0 267 3 / 3

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

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

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

34. 0.093 0.305 ↑ 1.0 277 3 / 3

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

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

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

36. 0.013 0.129 ↑ 1.0 1 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.116 0.116 ↑ 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.114..0.116 rows=1 loops=3)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 3
38. 196.093 196.093 ↑ 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.014..0.014 rows=1 loops=42,020)

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
39. 168.080 168.080 ↑ 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.012..0.012 rows=1 loops=42,020)

  • Index Cond: (user_id = user_registration.user_id)
40. 0.013 0.228 ↑ 1.0 1 3 / 3

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

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

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

42. 0.144 0.144 ↑ 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.142..0.144 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.064..0.065 rows=1 loops=3)

  • Index Cond: (affiliate_id = acl_allowed_affiliate.affiliate_id)
  • Heap Fetches: 0
44. 98.047 98.047 ↑ 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.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
45. 112.053 112.053 ↑ 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.006..0.008 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 : 53.271 ms
Execution time : 1,965.652 ms