explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DnF4

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7,769.307 ↑ 1.0 1 1

Aggregate (cost=791,410.43..791,410.44 rows=1 width=8) (actual time=7,769.307..7,769.307 rows=1 loops=1)

2. 419.958 7,769.304 ↓ 0.0 0 1

Merge Left Join (cost=701,164.06..790,561.72 rows=339,484 width=0) (actual time=7,769.304..7,769.304 rows=0 loops=1)

  • Merge Cond: ((user_game_activity.interval_start_date = game_activity.interval_start_date) AND (user_game_activity.interval_end_date = game_activity.interval_end_date) AND ((user_game_activity.currency)::text = (game_activity.currency)::text) AND (user_game_activity.portal_id = game_activity.portal_id) AND ((user_game_activity.client_mode)::text = (game_activity.client_mode)::text) AND (user_game_activity.game_profile_id = game_activity.game_profile_id) AND (users.affiliate_id = game_activity.affiliate_id) AND (user_game_activity.vip_level_id = game_activity.vip_level_id) AND ((user_game_activity.play_mode)::text = (game_activity.play_mode)::text))
  • Filter: ((COALESCE((sum(user_game_activity.bet_count)), '0'::numeric) <> COALESCE((sum(game_activity.bet_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.stake_transaction_count)), '0'::numeric) <> COALESCE((sum(game_activity.stake_transaction_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.return_transaction_count)), '0'::numeric) <> COALESCE((sum(game_activity.return_transaction_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.stake_refund_transaction_count)), '0'::numeric) <> COALESCE((sum(game_activity.stake_refund_transaction_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.total_staked_amount)), '0'::numeric) <> COALESCE((sum(game_activity.total_staked_amount)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.total_returned_amount)), '0'::numeric) <> COALESCE((sum(game_activity.total_returned_amount)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.total_stake_refunded_amount)), '0'::numeric) <> COALESCE((sum(game_activity.total_stake_refunded_amount)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.total_effective_stake_amount)), '0'::numeric) <> COALESCE((sum(game_activity.total_effective_stake_amount)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.game_play_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.game_play_player_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.cancelled_game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.cancelled_game_play_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.cancelled_game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.cancelled_game_play_player_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.forced_complete_game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.forced_complete_game_play_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.forced_complete_game_play_count)), '0'::numeric) <> COALESCE((sum(game_activity.forced_complete_game_play_player_count)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_fixed_win)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_fixed_win)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_fixed_win_paid)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_fixed_win_paid)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_network_progressive_contribution)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_network_progressive_contribution)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_private_progressive_contribution)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_private_progressive_contribution)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_network_progressive_win)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_network_progressive_win)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_private_progressive_win)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_private_progressive_win)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_network_progressive_win_paid)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_network_progressive_win_paid)), '0'::numeric)) OR (COALESCE((sum(user_game_activity.community_prize_total_private_progressive_win_paid)), '0'::numeric) <> COALESCE((sum(game_activity.community_prize_total_private_progressive_win_paid)), '0'::numeric)))
  • Rows Removed by Filter: 197454
3. 953.189 5,409.123 ↑ 1.7 197,454 1

GroupAggregate (cost=429,187.39..478,412.57 rows=339,484 width=773) (actual time=4,334.287..5,409.123 rows=197,454 loops=1)

  • Group Key: user_game_activity.interval_start_date, user_game_activity.interval_end_date, user_game_activity.currency, user_game_activity.portal_id, user_game_activity.client_mode, user_game_activity.game_profile_id, users.affiliate_id, user_game_activity.vip_level_id, user_game_activity.play_mode
4. 1,305.318 4,455.934 ↑ 1.0 332,920 1

Sort (cost=429,187.39..430,036.10 rows=339,484 width=168) (actual time=4,334.210..4,455.934 rows=332,920 loops=1)

  • Sort Key: user_game_activity.interval_start_date, user_game_activity.interval_end_date, user_game_activity.currency, user_game_activity.portal_id, user_game_activity.client_mode, user_game_activity.game_profile_id, users.affiliate_id, user_game_activity.vip_level_id, user_game_activity.play_mode
  • Sort Method: external merge Disk: 63176kB
5. 188.363 3,150.616 ↑ 1.0 332,920 1

Hash Join (cost=56,056.86..398,000.72 rows=339,484 width=168) (actual time=1,125.040..3,150.616 rows=332,920 loops=1)

  • Hash Cond: (user_registration.user_id = users.user_id)
6. 218.329 2,392.405 ↑ 1.0 332,920 1

Hash Join (cost=35,544.42..376,597.12 rows=339,484 width=168) (actual time=543.813..2,392.405 rows=332,920 loops=1)

  • Hash Cond: (user_game_activity.user_registration_id = user_registration.user_registration_id)
7. 1,684.615 1,889.453 ↑ 1.0 332,920 1

Bitmap Heap Scan on user_game_activity (cost=20,635.81..360,797.36 rows=339,484 width=168) (actual time=249.389..1,889.453 rows=332,920 loops=1)

  • Recheck Cond: (interval_start_date >= '2019-05-11 12:45:35.893+00'::timestamp with time zone)
  • Filter: (interval_end_date <= '2019-06-10 12:30:35.892+00'::timestamp with time zone)
  • Rows Removed by Filter: 893
  • Heap Blocks: exact=127712
8. 204.838 204.838 ↑ 1.0 333,947 1

Bitmap Index Scan on user_game_activity_pkey (cost=0.00..20,550.93 rows=339,517 width=0) (actual time=204.838..204.838 rows=333,947 loops=1)

  • Index Cond: (interval_start_date >= '2019-05-11 12:45:35.893+00'::timestamp with time zone)
9. 169.404 284.623 ↑ 1.0 495,813 1

Hash (cost=8,697.16..8,697.16 rows=496,916 width=16) (actual time=284.623..284.623 rows=495,813 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 27338kB
10. 115.219 115.219 ↑ 1.0 495,813 1

Seq Scan on user_registration (cost=0.00..8,697.16 rows=496,916 width=16) (actual time=0.026..115.219 rows=495,813 loops=1)

11. 196.895 569.848 ↑ 1.0 499,205 1

Hash (cost=14,264.42..14,264.42 rows=499,842 width=16) (actual time=569.848..569.848 rows=499,205 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 27497kB
12. 372.953 372.953 ↑ 1.0 499,205 1

Seq Scan on users (cost=0.00..14,264.42 rows=499,842 width=16) (actual time=0.018..372.953 rows=499,205 loops=1)

13. 119.513 1,940.223 ↓ 1.4 197,454 1

Materialize (cost=271,976.67..297,917.36 rows=142,155 width=773) (actual time=856.258..1,940.223 rows=197,454 loops=1)

14. 910.660 1,820.710 ↓ 1.4 197,454 1

GroupAggregate (cost=271,976.67..296,140.42 rows=142,155 width=773) (actual time=856.250..1,820.710 rows=197,454 loops=1)

  • Group Key: game_activity.interval_start_date, game_activity.interval_end_date, game_activity.currency, game_activity.portal_id, game_activity.client_mode, game_activity.game_profile_id, game_activity.affiliate_id, game_activity.vip_level_id, game_activity.play_mode
15. 513.447 910.050 ↓ 1.1 197,454 1

Sort (cost=271,976.67..272,443.03 rows=186,546 width=192) (actual time=856.203..910.050 rows=197,454 loops=1)

  • Sort Key: game_activity.interval_start_date, game_activity.interval_end_date, game_activity.currency, game_activity.portal_id, game_activity.client_mode, game_activity.game_profile_id, game_activity.affiliate_id, game_activity.vip_level_id, game_activity.play_mode
  • Sort Method: quicksort Memory: 58593kB
16. 340.982 396.603 ↓ 1.1 197,454 1

Bitmap Heap Scan on game_activity (cost=12,066.42..255,645.34 rows=186,546 width=192) (actual time=58.173..396.603 rows=197,454 loops=1)

  • Recheck Cond: (interval_start_date >= '2019-05-11 12:45:35.893+00'::timestamp with time zone)
  • Filter: (interval_end_date <= '2019-06-10 12:30:35.892+00'::timestamp with time zone)
  • Rows Removed by Filter: 484
  • Heap Blocks: exact=19325
17. 55.621 55.621 ↓ 1.1 197,938 1

Bitmap Index Scan on game_activity_pkey (cost=0.00..12,019.78 rows=186,564 width=0) (actual time=55.621..55.621 rows=197,938 loops=1)

  • Index Cond: (interval_start_date >= '2019-05-11 12:45:35.893+00'::timestamp with time zone)
Planning time : 12.700 ms
Execution time : 7,802.660 ms