explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wHZN

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 4,151.794 ↑ 1.0 200 1

Limit (cost=29,404.66..29,457.66 rows=200 width=1,258) (actual time=4,149.207..4,151.794 rows=200 loops=1)

2. 2.583 4,151.770 ↑ 1.1 200 1

GroupAggregate (cost=29,404.66..29,465.08 rows=228 width=1,258) (actual time=4,149.205..4,151.770 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. 1.641 4,149.187 ↓ 1.6 368 1

Sort (cost=29,404.66..29,405.23 rows=228 width=368) (actual time=4,149.138..4,149.187 rows=368 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: 309kB
4. 1.314 4,147.546 ↓ 2.4 552 1

Merge Join (cost=771.70..29,395.73 rows=228 width=368) (actual time=4,141.804..4,147.546 rows=552 loops=1)

  • Merge Cond: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
5. 4.245 4,146.194 ↓ 2.4 552 1

Nested Loop (cost=114.21..30,044.71 rows=228 width=347) (actual time=4,141.739..4,146.194 rows=552 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
  • Rows Removed by Join Filter: 13248
6. 0.074 0.074 ↑ 1.0 25 1

Index Scan using vip_level_unq02 on vip_level (cost=0.14..12.51 rows=25 width=20) (actual time=0.044..0.074 rows=25 loops=1)

7. 2.205 4,141.875 ↓ 2.4 552 25

Materialize (cost=114.08..29,947.27 rows=228 width=335) (actual time=0.130..165.675 rows=552 loops=25)

8. 1.119 4,139.670 ↓ 2.4 552 1

Nested Loop Left Join (cost=114.08..29,946.13 rows=228 width=335) (actual time=3.237..4,139.670 rows=552 loops=1)

9. 1.611 4,134.687 ↓ 2.4 552 1

Nested Loop (cost=113.80..29,836.95 rows=228 width=344) (actual time=3.214..4,134.687 rows=552 loops=1)

10. 0.864 4,130.868 ↓ 2.4 552 1

Hash Join (cost=113.66..29,799.57 rows=228 width=334) (actual time=3.181..4,130.868 rows=552 loops=1)

  • Hash Cond: (game.game_id = acl_allowed_game.game_id)
11. 0.451 4,127.569 ↓ 2.4 552 1

Nested Loop (cost=2.97..29,688.28 rows=227 width=342) (actual time=0.716..4,127.569 rows=552 loops=1)

  • Join Filter: ((game.category_id)::text = (game_category.category_id)::text)
  • Rows Removed by Join Filter: 587
12. 1.165 4,126.566 ↓ 2.4 552 1

Nested Loop (cost=2.97..29,652.57 rows=227 width=332) (actual time=0.696..4,126.566 rows=552 loops=1)

13. 0.808 4,123.745 ↓ 2.4 552 1

Nested Loop (cost=2.69..29,533.04 rows=227 width=282) (actual time=0.677..4,123.745 rows=552 loops=1)

14. 0.856 4,120.729 ↓ 2.4 552 1

Nested Loop (cost=2.42..29,465.83 rows=227 width=267) (actual time=0.646..4,120.729 rows=552 loops=1)

  • Join Filter: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
15. 0.882 4,117.113 ↓ 1.0 552 1

Nested Loop (cost=2.13..29,293.28 rows=534 width=283) (actual time=0.580..4,117.113 rows=552 loops=1)

16. 1.420 4,114.575 ↓ 1.0 552 1

Nested Loop (cost=1.85..29,137.08 rows=534 width=275) (actual time=0.537..4,114.575 rows=552 loops=1)

17. 1.267 4,109.291 ↓ 1.0 552 1

Nested Loop (cost=1.43..28,851.19 rows=534 width=259) (actual time=0.395..4,109.291 rows=552 loops=1)

18. 1.562 4,103.608 ↓ 1.0 552 1

Nested Loop (cost=1.00..28,472.26 rows=534 width=259) (actual time=0.348..4,103.608 rows=552 loops=1)

19. 0.388 6.695 ↓ 21.8 1,131 1

Nested Loop (cost=0.57..150.14 rows=52 width=68) (actual time=0.159..6.695 rows=1,131 loops=1)

20. 0.006 0.182 ↑ 1.0 1 1

Nested Loop (cost=0.28..32.54 rows=1 width=18) (actual time=0.099..0.182 rows=1 loops=1)

  • Join Filter: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 21
21. 0.025 0.161 ↑ 1.0 1 1

Nested Loop (cost=0.28..18.31 rows=1 width=27) (actual time=0.078..0.161 rows=1 loops=1)

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

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

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 0
23. 0.093 0.093 ↑ 1.0 267 1

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

24. 0.015 0.015 ↑ 12.6 22 1

Seq Scan on organisation (cost=0.00..10.77 rows=277 width=9) (actual time=0.010..0.015 rows=22 loops=1)

25. 6.125 6.125 ↓ 10.4 1,131 1

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

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
26. 4,095.351 4,095.351 ↓ 0.0 0 1,131

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.61 rows=105 width=199) (actual time=1.735..3.621 rows=0 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: 364
27. 4.416 4.416 ↑ 1.0 1 552

Index Scan using pk_user_registration on user_registration (cost=0.42..0.71 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=552)

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
28. 3.864 3.864 ↑ 1.0 1 552

Index Scan using pk_users on users (cost=0.42..0.54 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=552)

  • Index Cond: (user_id = user_registration.user_id)
29. 1.656 1.656 ↑ 1.0 1 552

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=552)

  • Index Cond: (affiliate_id = users.affiliate_id)
  • Heap Fetches: 0
30. 2.760 2.760 ↑ 1.0 1 552

Index Only Scan using pk_acl_allowed_affiliate on acl_allowed_affiliate (cost=0.29..0.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=552)

  • Index Cond: ((user_id = 197663) AND (affiliate_id = users.affiliate_id) AND (permission = 'READ'::text))
  • Heap Fetches: 0
31. 2.208 2.208 ↑ 1.0 1 552

Index Scan using pk_game_profile on game_profile (cost=0.28..0.30 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=552)

  • Index Cond: (game_profile_id = user_game_activity.game_profile_id)
32. 1.656 1.656 ↑ 1.0 1 552

Index Scan using pk_game on game (cost=0.28..0.53 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=552)

  • Index Cond: (game_id = game_profile.game_id)
33. 0.535 0.552 ↑ 5.5 2 552

Materialize (cost=0.00..1.17 rows=11 width=20) (actual time=0.000..0.001 rows=2 loops=552)

34. 0.017 0.017 ↑ 1.8 6 1

Seq Scan on game_category (cost=0.00..1.11 rows=11 width=20) (actual time=0.012..0.017 rows=6 loops=1)

35. 0.515 2.435 ↑ 1.0 1,220 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
36. 1.920 1.920 ↑ 1.0 1,220 1

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.116..1.920 rows=1,220 loops=1)

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 55
37. 2.208 2.208 ↑ 1.0 1 552

Index Scan using pk_game_supplier on game_supplier (cost=0.14..0.16 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=552)

  • Index Cond: ((supplier_id)::text = (game.supplier_id)::text)
38. 3.864 3.864 ↑ 1.0 1 552

Index Scan using organisation_ancestor_unq02 on organisation_ancestor (cost=0.28..0.47 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=552)

  • Index Cond: ((organisation_id)::text = (organisation.organisation_id)::text)
  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 1
39. 0.020 0.038 ↑ 5.0 1 1

Sort (cost=1.11..1.12 rows=5 width=22) (actual time=0.038..0.038 rows=1 loops=1)

  • Sort Key: vip_schema.vip_schema_id
  • Sort Method: quicksort Memory: 25kB
40. 0.018 0.018 ↑ 1.0 5 1

Seq Scan on vip_schema (cost=0.00..1.05 rows=5 width=22) (actual time=0.016..0.018 rows=5 loops=1)

Planning time : 34.188 ms
Execution time : 4,152.887 ms