explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1WjP

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 1,441.498 ↓ 200.0 200 1

Limit (cost=312,489.62..312,490.58 rows=1 width=1,258) (actual time=1,436.060..1,441.498 rows=200 loops=1)

2. 0.000 1,441.463 ↓ 200.0 200 1

Finalize GroupAggregate (cost=312,489.62..312,490.58 rows=1 width=1,258) (actual time=1,436.054..1,441.463 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. 69.661 1,471.317 ↓ 118.5 237 1

Gather Merge (cost=312,489.62..312,490.10 rows=2 width=1,098) (actual time=1,435.890..1,471.317 rows=237 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1.984 1,401.656 ↓ 114.0 114 3 / 3

Partial GroupAggregate (cost=311,489.60..311,489.85 rows=1 width=1,098) (actual time=1,399.775..1,401.656 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. 55.074 1,399.672 ↓ 125.0 125 3 / 3

Sort (cost=311,489.60..311,489.61 rows=1 width=368) (actual time=1,399.631..1,399.672 rows=125 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: 7275kB
  • Worker 0: Sort Method: quicksort Memory: 7821kB
  • Worker 1: Sort Method: quicksort Memory: 7724kB
6. 55.634 1,344.598 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=311,483.42..311,489.59 rows=1 width=368) (actual time=994.745..1,344.598 rows=14,007 loops=3)

7. 8.664 1,260.951 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=311,483.28..311,488.78 rows=1 width=347) (actual time=994.212..1,260.951 rows=14,007 loops=3)

8. 10.044 1,224.274 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=311,483.15..311,488.62 rows=1 width=335) (actual time=994.165..1,224.274 rows=14,007 loops=3)

9. 12.006 1,186.217 ↓ 14,007.0 14,007 3 / 3

Nested Loop Left Join (cost=311,482.87..311,488.33 rows=1 width=351) (actual time=994.091..1,186.217 rows=14,007 loops=3)

10. 17.439 1,090.171 ↓ 14,007.0 14,007 3 / 3

Nested Loop (cost=311,482.60..311,487.85 rows=1 width=360) (actual time=994.028..1,090.171 rows=14,007 loops=3)

11. 18.855 1,016.705 ↓ 14,007.0 14,007 3 / 3

Merge Join (cost=311,482.32..311,487.49 rows=1 width=369) (actual time=993.940..1,016.705 rows=14,007 loops=3)

  • Merge Cond: (users.affiliate_id = acl_allowed_affiliate.affiliate_id)
12. 54.146 997.742 ↓ 62.0 14,007 3 / 3

Sort (cost=311,482.04..311,482.60 rows=226 width=361) (actual time=993.810..997.742 rows=14,007 loops=3)

  • Sort Key: users.affiliate_id
  • Sort Method: quicksort Memory: 7275kB
  • Worker 0: Sort Method: quicksort Memory: 7821kB
  • Worker 1: Sort Method: quicksort Memory: 7724kB
13. 24.223 943.596 ↓ 62.0 14,007 3 / 3

Nested Loop (cost=991.87..311,473.20 rows=226 width=361) (actual time=35.171..943.596 rows=14,007 loops=3)

14. 27.675 765.300 ↓ 62.0 14,007 3 / 3

Nested Loop (cost=991.45..311,352.20 rows=226 width=345) (actual time=35.036..765.300 rows=14,007 loops=3)

15. 13.335 569.545 ↓ 62.0 14,007 3 / 3

Hash Join (cost=991.02..311,191.83 rows=226 width=345) (actual time=34.885..569.545 rows=14,007 loops=3)

  • Hash Cond: ((brand.organisation_id)::text = (acl_allowed_organisation.organisation_id)::text)
16. 16.551 556.011 ↑ 3.8 16,242 3 / 3

Hash Join (cost=986.71..311,022.54 rows=61,891 width=336) (actual time=33.429..556.011 rows=16,242 loops=3)

  • Hash Cond: (user_game_activity.game_profile_id = game_profile.game_profile_id)
17. 17.162 529.858 ↑ 3.8 16,242 3 / 3

Hash Join (cost=707.18..309,893.25 rows=61,588 width=259) (actual time=23.736..529.858 rows=16,242 loops=3)

  • Hash Cond: ((portal.brand_id)::text = (brand.brand_id)::text)
18. 20.075 512.251 ↑ 3.8 16,242 3 / 3

Hash Join (cost=693.17..309,715.01 rows=61,588 width=259) (actual time=23.240..512.251 rows=16,242 loops=3)

  • Hash Cond: (user_game_activity.portal_id = portal.portal_id)
19. 469.273 469.273 ↑ 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.101..469.273 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: 31910
20. 7.614 22.903 ↓ 1.0 14,411 3 / 3

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

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

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

22. 0.116 0.445 ↑ 1.0 267 3 / 3

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

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

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

24. 1.054 9.602 ↑ 1.0 1,261 3 / 3

Hash (cost=263.69..263.69 rows=1,267 width=85) (actual time=9.602..9.602 rows=1,261 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 167kB
25. 0.615 8.548 ↑ 1.0 1,261 3 / 3

Hash Join (cost=203.07..263.69 rows=1,267 width=85) (actual time=4.763..8.548 rows=1,261 loops=3)

  • Hash Cond: (game.game_id = acl_allowed_game.game_id)
26. 0.577 5.856 ↑ 1.0 1,261 3 / 3

Hash Join (cost=92.37..149.67 rows=1,261 width=93) (actual time=2.639..5.856 rows=1,261 loops=3)

  • Hash Cond: ((game.category_id)::text = (game_category.category_id)::text)
27. 0.685 5.220 ↑ 1.0 1,261 3 / 3

Hash Join (cost=91.12..143.83 rows=1,261 width=83) (actual time=2.536..5.220 rows=1,261 loops=3)

  • Hash Cond: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
28. 0.656 4.462 ↑ 1.0 1,261 3 / 3

Hash Join (cost=89.45..138.38 rows=1,261 width=73) (actual time=2.417..4.462 rows=1,261 loops=3)

  • Hash Cond: (game_profile.game_id = game.game_id)
29. 1.463 1.463 ↑ 1.0 1,261 3 / 3

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

30. 0.573 2.343 ↑ 1.0 1,220 3 / 3

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

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

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

32. 0.026 0.073 ↑ 1.0 30 3 / 3

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

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

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

34. 0.013 0.059 ↑ 1.0 11 3 / 3

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

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

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

36. 0.490 2.077 ↑ 1.0 1,220 3 / 3

Hash (cost=95.37..95.37 rows=1,226 width=8) (actual time=2.077..2.077 rows=1,220 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
37. 1.587 1.587 ↑ 1.0 1,220 3 / 3

Index Only Scan using pk_acl_allowed_game on acl_allowed_game (cost=0.42..95.37 rows=1,226 width=8) (actual time=0.174..1.587 rows=1,220 loops=3)

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 3660
38. 0.017 0.199 ↑ 1.0 1 3 / 3

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

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

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

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
41. 154.073 154.073 ↑ 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.011..0.011 rows=1 loops=42,020)

  • Index Cond: (user_id = user_registration.user_id)
42. 0.108 0.108 ↑ 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.108..0.108 rows=1 loops=3)

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

Index Only Scan using pk_organisation on organisation (cost=0.27..0.36 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=42,020)

  • Index Cond: (organisation_id = (brand.organisation_id)::text)
  • Heap Fetches: 0
44. 84.040 84.040 ↑ 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.004..0.006 rows=1 loops=42,020)

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

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

  • Index Cond: (affiliate_id = users.affiliate_id)
  • Heap Fetches: 0
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 : 69.139 ms
Execution time : 1,480.763 ms