explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o2Gv

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

Limit (cost=48,013.45..48,066.45 rows=200 width=1,258) (actual time=1,503.251..1,506.157 rows=200 loops=1)

2. 2.864 1,506.133 ↑ 35.2 200 1

GroupAggregate (cost=48,013.45..49,879.84 rows=7,043 width=1,258) (actual time=1,503.249..1,506.133 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. 112.495 1,503.269 ↑ 17.0 415 1

Sort (cost=48,013.45..48,031.06 rows=7,043 width=368) (actual time=1,503.180..1,503.269 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. 127.539 1,390.774 ↓ 4.3 30,436 1

Nested Loop (cost=338.53..47,563.33 rows=7,043 width=368) (actual time=5.456..1,390.774 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. 18.252 1,263.235 ↓ 4.3 30,436 1

Hash Join (cost=338.53..46,998.85 rows=7,043 width=347) (actual time=5.421..1,263.235 rows=30,436 loops=1)

  • Hash Cond: (user_game_activity.vip_level_id = vip_level.vip_level_id)
6. 15.065 1,244.936 ↓ 4.3 30,436 1

Hash Join (cost=336.97..46,975.66 rows=7,043 width=335) (actual time=5.361..1,244.936 rows=30,436 loops=1)

  • Hash Cond: (game.game_id = acl_allowed_game.game_id)
7. 20.895 1,229.079 ↓ 4.3 30,436 1

Hash Left Join (cost=226.27..46,846.50 rows=7,009 width=343) (actual time=4.554..1,229.079 rows=30,436 loops=1)

  • Hash Cond: ((organisation.organisation_id)::text = (organisation_ancestor.organisation_id)::text)
8. 19.341 1,207.980 ↓ 4.3 30,436 1

Hash Join (cost=211.89..46,719.99 rows=7,009 width=352) (actual time=4.340..1,207.980 rows=30,436 loops=1)

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

Hash Join (cost=210.64..46,693.26 rows=7,009 width=342) (actual time=4.309..1,188.619 rows=30,436 loops=1)

  • Hash Cond: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
10. 19.263 1,168.196 ↓ 4.3 30,436 1

Hash Join (cost=208.97..46,670.58 rows=7,009 width=332) (actual time=4.271..1,168.196 rows=30,436 loops=1)

  • Hash Cond: (game_profile.game_id = game.game_id)
11. 17.185 1,147.593 ↓ 4.3 30,436 1

Hash Join (cost=119.52..46,562.67 rows=7,009 width=282) (actual time=2.920..1,147.593 rows=30,436 loops=1)

  • Hash Cond: (user_game_activity.game_profile_id = game_profile.game_profile_id)
12. 16.307 1,129.091 ↓ 4.3 30,436 1

Hash Join (cost=58.14..46,482.83 rows=7,009 width=267) (actual time=1.588..1,129.091 rows=30,436 loops=1)

  • Hash Cond: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
13. 21.210 1,112.628 ↓ 1.8 30,436 1

Hash Join (cost=36.33..46,417.55 rows=16,459 width=283) (actual time=1.407..1,112.628 rows=30,436 loops=1)

  • Hash Cond: (users.affiliate_id = affiliate.affiliate_id)
14. 26.811 1,091.029 ↓ 1.8 30,436 1

Nested Loop (cost=1.85..46,339.60 rows=16,459 width=275) (actual time=1.007..1,091.029 rows=30,436 loops=1)

15. 42.939 972.910 ↓ 1.8 30,436 1

Nested Loop (cost=1.43..37,527.90 rows=16,459 width=259) (actual time=0.992..972.910 rows=30,436 loops=1)

16. 12.474 838.663 ↓ 1.8 30,436 1

Nested Loop (cost=1.00..30,102.27 rows=16,459 width=259) (actual time=0.972..838.663 rows=30,436 loops=1)

17. 0.255 3.952 ↓ 21.8 1,131 1

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

18. 0.004 0.175 ↑ 1.0 1 1

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

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

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

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

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

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

22. 0.029 0.029 ↑ 12.6 22 1

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

23. 3.522 3.522 ↓ 10.4 1,131 1

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

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

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.60 rows=3,240 width=199) (actual time=0.160..0.727 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. 91.308 91.308 ↑ 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.003..0.003 rows=1 loops=30,436)

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

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

  • Index Cond: (user_id = user_registration.user_id)
27. 0.125 0.389 ↑ 1.0 688 1

Hash (cost=25.88..25.88 rows=688 width=8) (actual time=0.388..0.389 rows=688 loops=1)

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

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

29. 0.046 0.156 ↓ 1.1 325 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
30. 0.110 0.110 ↓ 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.029..0.110 rows=325 loops=1)

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

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

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

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

33. 0.430 1.340 ↑ 1.0 1,220 1

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

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

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

35. 0.009 0.029 ↑ 1.0 30 1

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

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

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

37. 0.005 0.020 ↑ 1.0 11 1

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

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

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

39. 0.067 0.204 ↑ 1.0 269 1

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

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

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

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
41. 0.273 0.792 ↑ 1.0 1,220 1

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

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

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

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

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

Seq Scan on vip_level (cost=0.00..1.25 rows=25 width=20) (actual time=0.021..0.031 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.011 0.011 ↑ 1.7 3 1

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

Planning time : 27.466 ms
Execution time : 1,507.100 ms