explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZIkw

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 10,405.754 ↑ 1.0 200 1

Limit (cost=48,013.98..48,066.98 rows=200 width=1,258) (actual time=10,402.856..10,405.754 rows=200 loops=1)

2. 2.915 10,405.726 ↑ 35.2 200 1

GroupAggregate (cost=48,013.98..49,880.38 rows=7,043 width=1,258) (actual time=10,402.850..10,405.726 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. 176.657 10,402.811 ↑ 17.0 415 1

Sort (cost=48,013.98..48,031.59 rows=7,043 width=368) (actual time=10,402.719..10,402.811 rows=415 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: 16462kB
4. 220.356 10,226.154 ↓ 4.3 30,436 1

Nested Loop (cost=338.53..47,563.86 rows=7,043 width=368) (actual time=27.036..10,226.154 rows=30,436 loops=1)

  • Join Filter: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
  • Rows Removed by Join Filter: 60872
5. 33.145 10,005.798 ↓ 4.3 30,436 1

Hash Join (cost=338.53..46,999.38 rows=7,043 width=347) (actual time=26.519..10,005.798 rows=30,436 loops=1)

  • Hash Cond: (user_game_activity.vip_level_id = vip_level.vip_level_id)
6. 29.672 9,972.596 ↓ 4.3 30,436 1

Hash Join (cost=336.97..46,976.19 rows=7,043 width=335) (actual time=26.412..9,972.596 rows=30,436 loops=1)

  • Hash Cond: (game.game_id = acl_allowed_game.game_id)
7. 38.555 9,938.842 ↓ 4.3 30,436 1

Hash Left Join (cost=226.27..46,847.04 rows=7,009 width=343) (actual time=22.285..9,938.842 rows=30,436 loops=1)

  • Hash Cond: ((organisation.organisation_id)::text = (organisation_ancestor.organisation_id)::text)
8. 33.857 9,899.981 ↓ 4.3 30,436 1

Hash Join (cost=211.89..46,720.53 rows=7,009 width=352) (actual time=21.921..9,899.981 rows=30,436 loops=1)

  • Hash Cond: ((game.category_id)::text = (game_category.category_id)::text)
9. 40.273 9,866.079 ↓ 4.3 30,436 1

Hash Join (cost=210.64..46,693.79 rows=7,009 width=342) (actual time=21.826..9,866.079 rows=30,436 loops=1)

  • Hash Cond: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
10. 39.461 9,825.753 ↓ 4.3 30,436 1

Hash Join (cost=208.97..46,671.12 rows=7,009 width=332) (actual time=21.732..9,825.753 rows=30,436 loops=1)

  • Hash Cond: (game_profile.game_id = game.game_id)
11. 31.176 9,783.862 ↓ 4.3 30,436 1

Hash Join (cost=119.52..46,563.20 rows=7,009 width=282) (actual time=19.261..9,783.862 rows=30,436 loops=1)

  • Hash Cond: (user_game_activity.game_profile_id = game_profile.game_profile_id)
12. 30.516 9,751.212 ↓ 4.3 30,436 1

Hash Join (cost=58.14..46,483.36 rows=7,009 width=267) (actual time=17.745..9,751.212 rows=30,436 loops=1)

  • Hash Cond: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
13. 41.722 9,720.152 ↓ 1.8 30,436 1

Hash Join (cost=36.33..46,418.08 rows=16,459 width=283) (actual time=17.160..9,720.152 rows=30,436 loops=1)

  • Hash Cond: (users.affiliate_id = affiliate.affiliate_id)
14. 64.185 9,677.486 ↓ 1.8 30,436 1

Nested Loop (cost=1.85..46,340.13 rows=16,459 width=275) (actual time=16.172..9,677.486 rows=30,436 loops=1)

15. 99.197 9,491.557 ↓ 1.8 30,436 1

Nested Loop (cost=1.43..37,528.21 rows=16,459 width=259) (actual time=16.077..9,491.557 rows=30,436 loops=1)

16. 26.117 9,179.308 ↓ 1.8 30,436 1

Nested Loop (cost=1.00..30,102.56 rows=16,459 width=259) (actual time=15.974..9,179.308 rows=30,436 loops=1)

17. 0.452 10.187 ↓ 21.8 1,131 1

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

18. 0.005 0.476 ↑ 1.0 1 1

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

  • Join Filter: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 21
19. 0.013 0.432 ↑ 1.0 1 1

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

  • Join Filter: ((brand.organisation_id)::text = (acl_allowed_organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 266
20. 0.219 0.219 ↑ 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.201..0.219 rows=1 loops=1)

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

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

22. 0.039 0.039 ↑ 12.6 22 1

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

23. 9.259 9.259 ↓ 10.4 1,131 1

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

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
24. 9,143.004 9,143.004 ↑ 120.0 27 1,131

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.61 rows=3,240 width=199) (actual time=1.674..8.084 rows=27 loops=1,131)

  • Index Cond: (portal_id = portal.portal_id)
  • Filter: ((interval_start_date >= '2019-09-01 11:00:00+00'::timestamp with time zone) AND (interval_end_date <= '2019-12-01 12:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 337
25. 213.052 213.052 ↑ 1.0 1 30,436

Index Scan using pk_user_registration on user_registration (cost=0.42..0.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=30,436)

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
26. 121.744 121.744 ↑ 1.0 1 30,436

Index Scan using pk_users on users (cost=0.42..0.54 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=30,436)

  • Index Cond: (user_id = user_registration.user_id)
27. 0.203 0.944 ↑ 1.0 688 1

Hash (cost=25.88..25.88 rows=688 width=8) (actual time=0.943..0.944 rows=688 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
28. 0.741 0.741 ↑ 1.0 688 1

Seq Scan on affiliate (cost=0.00..25.88 rows=688 width=8) (actual time=0.043..0.741 rows=688 loops=1)

29. 0.107 0.544 ↓ 1.1 325 1

Hash (cost=18.15..18.15 rows=293 width=8) (actual time=0.544..0.544 rows=325 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
30. 0.437 0.437 ↓ 1.1 325 1

Index Only Scan using pk_acl_allowed_affiliate on acl_allowed_affiliate (cost=0.29..18.15 rows=293 width=8) (actual time=0.162..0.437 rows=325 loops=1)

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 2
31. 0.372 1.474 ↑ 1.0 1,261 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
32. 1.102 1.102 ↑ 1.0 1,261 1

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

33. 0.578 2.430 ↑ 1.0 1,220 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
34. 1.852 1.852 ↑ 1.0 1,220 1

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

35. 0.015 0.053 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=27) (actual time=0.053..0.053 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 0.038 0.038 ↑ 1.0 30 1

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

37. 0.011 0.045 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=20) (actual time=0.045..0.045 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.034 0.034 ↑ 1.0 11 1

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

39. 0.098 0.306 ↑ 1.0 269 1

Hash (cost=11.01..11.01 rows=270 width=18) (actual time=0.306..0.306 rows=269 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
40. 0.208 0.208 ↑ 1.0 269 1

Seq Scan on organisation_ancestor (cost=0.00..11.01 rows=270 width=18) (actual time=0.036..0.208 rows=269 loops=1)

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
41. 0.331 4.082 ↑ 1.0 1,220 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
42. 3.751 3.751 ↑ 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.279..3.751 rows=1,220 loops=1)

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 55
43. 0.014 0.057 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=20) (actual time=0.057..0.057 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.043 0.043 ↑ 1.0 25 1

Seq Scan on vip_level (cost=0.00..1.25 rows=25 width=20) (actual time=0.035..0.043 rows=25 loops=1)

45. 0.000 0.000 ↑ 1.7 3 30,436

Materialize (cost=0.00..1.07 rows=5 width=22) (actual time=0.000..0.000 rows=3 loops=30,436)

46. 0.031 0.031 ↑ 1.7 3 1

Seq Scan on vip_schema (cost=0.00..1.05 rows=5 width=22) (actual time=0.030..0.031 rows=3 loops=1)

Planning time : 44.479 ms
Execution time : 10,410.708 ms