explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BNJ0

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

Limit (cost=1,218,241.80..1,218,294.80 rows=200 width=1,258) (actual time=8,988.412..8,991.244 rows=200 loops=1)

2. 2.838 8,991.220 ↑ 35.2 200 1

GroupAggregate (cost=1,218,241.80..1,220,108.46 rows=7,044 width=1,258) (actual time=8,988.410..8,991.220 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. 113.839 8,988.382 ↑ 17.0 415 1

Sort (cost=1,218,241.80..1,218,259.41 rows=7,044 width=368) (actual time=8,988.334..8,988.382 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. 76.566 8,874.543 ↓ 4.3 30,436 1

Merge Join (cost=26,903.66..1,217,791.61 rows=7,044 width=368) (actual time=8,441.135..8,874.543 rows=30,436 loops=1)

  • Merge Cond: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
5. 280.683 8,797.934 ↓ 4.3 30,436 1

Nested Loop (cost=2,074.04..1,242,425.28 rows=7,044 width=347) (actual time=8,441.022..8,797.934 rows=30,436 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
  • Rows Removed by Join Filter: 730464
6. 0.201 0.201 ↑ 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.074..0.201 rows=25 loops=1)

7. 141.041 8,517.050 ↓ 4.3 30,436 25

Materialize (cost=2,073.90..1,239,788.88 rows=7,044 width=335) (actual time=3.046..340.682 rows=30,436 loops=25)

8. 33.367 8,376.009 ↓ 4.3 30,436 1

Hash Join (cost=2,073.90..1,239,753.66 rows=7,044 width=335) (actual time=76.140..8,376.009 rows=30,436 loops=1)

  • Hash Cond: (users.affiliate_id = affiliate.affiliate_id)
9. 0.000 8,341.590 ↓ 1.8 30,436 1

Gather (cost=2,020.73..1,239,568.02 rows=16,540 width=343) (actual time=75.055..8,341.590 rows=30,436 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 21.727 8,358.589 ↓ 1.5 10,145 3 / 3

Nested Loop (cost=1,020.73..1,236,914.02 rows=6,892 width=343) (actual time=62.790..8,358.589 rows=10,145 loops=3)

11. 32.046 8,286.135 ↓ 1.5 10,145 3 / 3

Nested Loop (cost=1,020.31..1,233,224.17 rows=6,892 width=327) (actual time=62.648..8,286.135 rows=10,145 loops=3)

12. 13.268 8,172.926 ↓ 1.5 10,145 3 / 3

Hash Join (cost=1,019.88..1,230,114.78 rows=6,892 width=327) (actual time=62.499..8,172.926 rows=10,145 loops=3)

  • Hash Cond: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
13. 112.921 8,158.921 ↓ 1.5 10,145 3 / 3

Hash Join (cost=990.46..1,229,989.55 rows=6,968 width=345) (actual time=61.472..8,158.921 rows=10,145 loops=3)

  • Hash Cond: ((brand.organisation_id)::text = (acl_allowed_organisation.organisation_id)::text)
14. 458.499 8,045.873 ↑ 2.7 705,517 3 / 3

Hash Join (cost=986.15..1,224,896.50 rows=1,909,038 width=336) (actual time=34.471..8,045.873 rows=705,517 loops=3)

  • Hash Cond: (user_game_activity.game_profile_id = game_profile.game_profile_id)
15. 374.540 7,580.119 ↑ 2.7 705,517 3 / 3

Hash Join (cost=706.62..1,198,405.78 rows=1,899,695 width=259) (actual time=27.105..7,580.119 rows=705,517 loops=3)

  • Hash Cond: ((portal.brand_id)::text = (brand.brand_id)::text)
16. 476.222 7,205.276 ↑ 2.7 705,517 3 / 3

Hash Join (cost=692.62..1,193,325.92 rows=1,899,695 width=259) (actual time=26.766..7,205.276 rows=705,517 loops=3)

  • Hash Cond: (user_game_activity.portal_id = portal.portal_id)
17. 6,709.623 6,709.623 ↑ 2.7 705,517 3 / 3

Parallel Seq Scan on user_game_activity (cost=0.00..1,187,645.12 rows=1,899,695 width=199) (actual time=7.165..6,709.623 rows=705,517 loops=3)

  • 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: 3847786
18. 7.338 19.431 ↓ 1.0 14,411 3 / 3

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

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

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

20. 0.089 0.303 ↑ 1.0 267 3 / 3

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

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

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

22. 0.669 7.255 ↑ 1.0 1,261 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 167kB
23. 0.498 6.586 ↑ 1.0 1,261 3 / 3

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

  • Hash Cond: (game.game_id = acl_allowed_game.game_id)
24. 0.425 4.599 ↑ 1.0 1,261 3 / 3

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

  • Hash Cond: ((game.category_id)::text = (game_category.category_id)::text)
25. 0.497 4.128 ↑ 1.0 1,261 3 / 3

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

  • Hash Cond: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
26. 0.465 3.568 ↑ 1.0 1,261 3 / 3

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

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

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

28. 0.567 2.148 ↑ 1.0 1,220 3 / 3

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

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

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

30. 0.025 0.063 ↑ 1.0 30 3 / 3

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

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

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

32. 0.011 0.046 ↑ 1.0 11 3 / 3

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

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

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

34. 0.260 1.489 ↑ 1.0 1,220 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
35. 1.229 1.229 ↑ 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.111..1.229 rows=1,220 loops=3)

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 165
36. 0.007 0.127 ↑ 1.0 1 3 / 3

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

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

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 0
38. 0.074 0.737 ↑ 1.0 277 3 / 3

Hash (cost=25.96..25.96 rows=277 width=9) (actual time=0.737..0.737 rows=277 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
39. 0.120 0.663 ↑ 1.0 277 3 / 3

Hash Right Join (cost=14.23..25.96 rows=277 width=9) (actual time=0.429..0.663 rows=277 loops=3)

  • Hash Cond: ((organisation_ancestor.organisation_id)::text = (organisation.organisation_id)::text)
40. 0.201 0.201 ↑ 1.0 269 3 / 3

Seq Scan on organisation_ancestor (cost=0.00..11.01 rows=270 width=18) (actual time=0.051..0.201 rows=269 loops=3)

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
41. 0.087 0.342 ↑ 1.0 277 3 / 3

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

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

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

43. 81.163 81.163 ↑ 1.0 1 30,436 / 3

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

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
44. 50.727 50.727 ↑ 1.0 1 30,436 / 3

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

  • Index Cond: (user_id = user_registration.user_id)
45. 0.077 1.052 ↓ 1.1 325 1

Hash (cost=49.51..49.51 rows=293 width=16) (actual time=1.052..1.052 rows=325 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
46. 0.229 0.975 ↓ 1.1 325 1

Hash Join (cost=21.81..49.51 rows=293 width=16) (actual time=0.354..0.975 rows=325 loops=1)

  • Hash Cond: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
47. 0.458 0.458 ↑ 1.0 688 1

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

48. 0.058 0.288 ↓ 1.1 325 1

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

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

  • Index Cond: ((user_id = 197663) AND (permission = 'READ'::text))
  • Heap Fetches: 2
50. 0.025 0.043 ↑ 5.0 1 1

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

  • Sort Key: vip_schema.vip_schema_id
  • Sort Method: quicksort Memory: 25kB
51. 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 : 52.793 ms
Execution time : 8,997.321 ms