explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rLY9

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 61,871.739 ↓ 200.0 200 1

Limit (cost=29,611.92..29,612.19 rows=1 width=1,258) (actual time=61,869.375..61,871.739 rows=200 loops=1)

2. 2.344 61,871.716 ↓ 200.0 200 1

GroupAggregate (cost=29,611.92..29,612.19 rows=1 width=1,258) (actual time=61,869.373..61,871.716 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. 247.162 61,869.372 ↓ 257.0 257 1

Sort (cost=29,611.92..29,611.92 rows=1 width=368) (actual time=61,869.291..61,869.372 rows=257 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: 23203kB
4. 473.628 61,622.210 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,611.91 rows=1 width=368) (actual time=9.484..61,622.210 rows=42,020 loops=1)

  • Join Filter: ((vip_level.vip_schema_id)::text = (vip_schema.vip_schema_id)::text)
5. 251.203 61,064.542 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,610.78 rows=1 width=347) (actual time=9.455..61,064.542 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.vip_level_id = vip_level.vip_level_id)
6. 1,052.943 60,729.299 ↓ 42,020.0 42,020 1

Nested Loop Left Join (cost=2.56..29,609.22 rows=1 width=335) (actual time=9.441..60,729.299 rows=42,020 loops=1)

  • Join Filter: ((organisation_ancestor.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 11261360
7. 252.672 56,861.016 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,594.83 rows=1 width=344) (actual time=9.364..56,861.016 rows=42,020 loops=1)

  • Join Filter: ((game.category_id)::text = (game_category.category_id)::text)
  • Rows Removed by Join Filter: 15753
8. 312.557 56,524.304 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,593.59 rows=1 width=334) (actual time=9.350..56,524.304 rows=42,020 loops=1)

  • Join Filter: ((game.supplier_id)::text = (game_supplier.supplier_id)::text)
  • Rows Removed by Join Filter: 383210
9. 1,203.918 56,085.687 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,591.91 rows=1 width=324) (actual time=9.332..56,085.687 rows=42,020 loops=1)

  • Join Filter: (game_profile.game_id = game.game_id)
  • Rows Removed by Join Filter: 14730773
10. 137.930 51,394.109 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.56..29,502.46 rows=1 width=290) (actual time=9.299..51,394.109 rows=42,020 loops=1)

11. 1,415.412 50,877.999 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,498.47 rows=1 width=282) (actual time=9.269..50,877.999 rows=42,020 loops=1)

  • Join Filter: (user_game_activity.game_profile_id = game_profile.game_profile_id)
  • Rows Removed by Join Filter: 18116374
12. 67.723 46,143.007 ↓ 42,020.0 42,020 1

Nested Loop (cost=2.13..29,437.10 rows=1 width=267) (actual time=9.114..46,143.007 rows=42,020 loops=1)

  • Join Filter: (affiliate.affiliate_id = acl_allowed_affiliate.affiliate_id)
13. 94.294 46,075.284 ↓ 78.7 42,020 1

Nested Loop (cost=1.85..29,424.78 rows=534 width=283) (actual time=9.064..46,075.284 rows=42,020 loops=1)

14. 89.474 45,854.930 ↓ 78.7 42,020 1

Nested Loop (cost=1.57..29,268.58 rows=534 width=275) (actual time=9.025..45,854.930 rows=42,020 loops=1)

15. 146.170 45,303.236 ↓ 78.7 42,020 1

Nested Loop (cost=1.15..28,982.69 rows=534 width=259) (actual time=8.988..45,303.236 rows=42,020 loops=1)

16. 35.396 44,694.846 ↓ 78.7 42,020 1

Nested Loop (cost=0.72..28,603.76 rows=534 width=259) (actual time=8.959..44,694.846 rows=42,020 loops=1)

17. 1.150 13.225 ↓ 21.8 1,131 1

Nested Loop (cost=0.29..281.61 rows=52 width=68) (actual time=0.600..13.225 rows=1,131 loops=1)

18. 0.004 1.718 ↑ 1.0 1 1

Nested Loop (cost=0.00..164.01 rows=1 width=18) (actual time=0.511..1.718 rows=1 loops=1)

  • Join Filter: ((brand.organisation_id)::text = (organisation.organisation_id)::text)
  • Rows Removed by Join Filter: 23
19. 0.025 1.702 ↑ 1.0 1 1

Nested Loop (cost=0.00..149.78 rows=1 width=27) (actual time=0.495..1.702 rows=1 loops=1)

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

Seq Scan on acl_allowed_organisation (cost=0.00..135.77 rows=1 width=9) (actual time=0.401..1.549 rows=1 loops=1)

  • Filter: (((permission)::text = 'READ'::text) AND (user_id = 2168379))
  • Rows Removed by Filter: 5839
21. 0.128 0.128 ↑ 1.0 267 1

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

22. 0.012 0.012 ↑ 11.5 24 1

Seq Scan on organisation (cost=0.00..10.77 rows=277 width=9) (actual time=0.008..0.012 rows=24 loops=1)

23. 10.357 10.357 ↓ 10.4 1,131 1

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

  • Index Cond: ((brand_id)::text = (brand.brand_id)::text)
24. 44,646.225 44,646.225 ↑ 2.8 37 1,131

Index Scan using user_game_activity_ix02 on user_game_activity (cost=0.43..543.61 rows=105 width=199) (actual time=2.758..39.475 rows=37 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: 7215
25. 462.220 462.220 ↑ 1.0 1 42,020

Index Scan using pk_user_registration on user_registration (cost=0.42..0.71 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=42,020)

  • Index Cond: (user_registration_id = user_game_activity.user_registration_id)
26. 462.220 462.220 ↑ 1.0 1 42,020

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)
27. 126.060 126.060 ↑ 1.0 1 42,020

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=42,020)

  • Index Cond: (affiliate_id = users.affiliate_id)
  • Heap Fetches: 0
28. 0.000 0.000 ↑ 1.0 1 42,020

Materialize (cost=0.29..4.31 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=42,020)

29. 0.028 0.028 ↑ 1.0 1 1

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

  • Index Cond: ((user_id = 2168379) AND (permission = 'READ'::text))
  • Heap Fetches: 1
30. 3,319.580 3,319.580 ↑ 2.9 432 42,020

Seq Scan on game_profile (cost=0.00..45.61 rows=1,261 width=23) (actual time=0.003..0.079 rows=432 loops=42,020)

31. 378.180 378.180 ↑ 1.0 1 42,020

Index Only Scan using pk_acl_allowed_game on acl_allowed_game (cost=0.42..3.98 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=42,020)

  • Index Cond: ((user_id = 2168379) AND (game_id = game_profile.game_id) AND (permission = 'READ'::text))
  • Heap Fetches: 42020
32. 3,487.660 3,487.660 ↑ 3.5 352 42,020

Seq Scan on game (cost=0.00..74.20 rows=1,220 width=50) (actual time=0.003..0.083 rows=352 loops=42,020)

33. 126.060 126.060 ↑ 3.0 10 42,020

Seq Scan on game_supplier (cost=0.00..1.30 rows=30 width=27) (actual time=0.002..0.003 rows=10 loops=42,020)

34. 84.040 84.040 ↑ 11.0 1 42,020

Seq Scan on game_category (cost=0.00..1.11 rows=11 width=20) (actual time=0.002..0.002 rows=1 loops=42,020)

35. 2,815.340 2,815.340 ↑ 1.0 269 42,020

Seq Scan on organisation_ancestor (cost=0.00..11.01 rows=270 width=18) (actual time=0.005..0.067 rows=269 loops=42,020)

  • Filter: nearest_billing_entity
  • Rows Removed by Filter: 332
36. 84.040 84.040 ↑ 25.0 1 42,020

Seq Scan on vip_level (cost=0.00..1.25 rows=25 width=20) (actual time=0.002..0.002 rows=1 loops=42,020)

37. 84.040 84.040 ↑ 5.0 1 42,020

Seq Scan on vip_schema (cost=0.00..1.05 rows=5 width=22) (actual time=0.002..0.002 rows=1 loops=42,020)

Planning time : 22.185 ms
Execution time : 61,875.002 ms