explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 471T : AABC

Settings
# exclusive inclusive rows x rows loops node
1. 7,849.747 25,056.078 ↓ 597.0 597 1

Nested Loop (cost=99.52..148.86 rows=1 width=173) (actual time=299.391..25,056.078 rows=597 loops=1)

  • Join Filter: ((temp_process_game_player_id.id = process_game_player_rating.player_id) AND ((t2.param_value)::text = (process_game_player_rating.param_value)::text) AND (t2.max_start_time = process_game_player_rating.match_start_timestamp) AND (t2.max_num_in_sequence = process_game_player_rating.game_num))
  • Rows Removed by Join Filter: 46255560
2. 83.030 83.030 ↓ 77,481.0 77,481 1

Index Scan using ix_process_game_player_rating_process_id on process_game_player_rating (cost=0.43..8.37 rows=1 width=173) (actual time=0.074..83.030 rows=77,481 loops=1)

  • Index Cond: (process_id = 52)
  • Filter: (param_type = 'map'::paramtype)
3. 15,507.711 17,123.301 ↓ 54.3 597 77,481

Hash Join (cost=99.08..140.27 rows=11 width=23) (actual time=0.010..0.221 rows=597 loops=77,481)

  • Hash Cond: (temp_process_game_player_id.id = t2.player_id)
4. 1,317.177 1,317.177 ↑ 22.6 100 77,481

Seq Scan on temp_process_game_player_id (cost=0.00..32.60 rows=2,260 width=4) (actual time=0.005..0.017 rows=100 loops=77,481)

5. 0.099 298.413 ↓ 597.0 597 1

Hash (cost=99.07..99.07 rows=1 width=19) (actual time=298.413..298.413 rows=597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
6. 0.054 298.314 ↓ 597.0 597 1

Subquery Scan on t2 (cost=99.01..99.07 rows=1 width=19) (actual time=291.713..298.314 rows=597 loops=1)

7. 0.196 298.260 ↓ 597.0 597 1

GroupAggregate (cost=99.01..99.06 rows=1 width=19) (actual time=291.712..298.260 rows=597 loops=1)

  • Group Key: process_game_player_rating_1.player_id, process_game_player_rating_1.param_value, t1.max_start_time
8. 1.396 298.064 ↓ 597.0 597 1

Merge Join (cost=99.01..99.04 rows=1 width=19) (actual time=291.703..298.064 rows=597 loops=1)

  • Merge Cond: ((temp_process_game_player_id_1.id = t1.player_id) AND ((process_game_player_rating_1.param_value)::text = (t1.param_value)::text) AND (process_game_player_rating_1.match_start_timestamp = t1.max_start_time))
9. 24.620 166.133 ↓ 8,787.0 8,787 1

Sort (cost=49.48..49.49 rows=1 width=23) (actual time=161.198..166.133 rows=8,787 loops=1)

  • Sort Key: process_game_player_rating_1.player_id, process_game_player_rating_1.param_value, process_game_player_rating_1.match_start_timestamp
  • Sort Method: quicksort Memory: 1071kB
10. 19.300 141.513 ↓ 8,787.0 8,787 1

Hash Join (cost=8.39..49.47 rows=1 width=23) (actual time=122.273..141.513 rows=8,787 loops=1)

  • Hash Cond: (temp_process_game_player_id_1.id = process_game_player_rating_1.player_id)
11. 0.031 0.031 ↑ 22.6 100 1

Seq Scan on temp_process_game_player_id temp_process_game_player_id_1 (cost=0.00..32.60 rows=2,260 width=4) (actual time=0.015..0.031 rows=100 loops=1)

12. 45.563 122.182 ↓ 77,481.0 77,481 1

Hash (cost=8.37..8.37 rows=1 width=19) (actual time=122.182..122.182 rows=77,481 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
13. 76.619 76.619 ↓ 77,481.0 77,481 1

Index Scan using ix_process_game_player_rating_process_id on process_game_player_rating process_game_player_rating_1 (cost=0.43..8.37 rows=1 width=19) (actual time=0.015..76.619 rows=77,481 loops=1)

  • Index Cond: (process_id = 52)
  • Filter: (param_type = 'map'::paramtype)
14. 0.219 130.535 ↓ 597.0 597 1

Sort (cost=49.52..49.53 rows=1 width=15) (actual time=130.487..130.535 rows=597 loops=1)

  • Sort Key: t1.player_id, t1.param_value, t1.max_start_time
  • Sort Method: quicksort Memory: 71kB
15. 0.080 130.316 ↓ 597.0 597 1

Subquery Scan on t1 (cost=49.48..49.51 rows=1 width=15) (actual time=124.210..130.316 rows=597 loops=1)

16. 1.469 130.236 ↓ 597.0 597 1

GroupAggregate (cost=49.48..49.50 rows=1 width=15) (actual time=124.209..130.236 rows=597 loops=1)

  • Group Key: process_game_player_rating_2.player_id, process_game_player_rating_2.param_value
17. 12.869 128.767 ↓ 8,787.0 8,787 1

Sort (cost=49.48..49.49 rows=1 width=15) (actual time=124.195..128.767 rows=8,787 loops=1)

  • Sort Key: process_game_player_rating_2.player_id, process_game_player_rating_2.param_value
  • Sort Method: quicksort Memory: 1071kB
18. 23.424 115.898 ↓ 8,787.0 8,787 1

Hash Join (cost=8.39..49.47 rows=1 width=15) (actual time=92.530..115.898 rows=8,787 loops=1)

  • Hash Cond: (temp_process_game_player_id_2.id = process_game_player_rating_2.player_id)
19. 0.030 0.030 ↑ 22.6 100 1

Seq Scan on temp_process_game_player_id temp_process_game_player_id_2 (cost=0.00..32.60 rows=2,260 width=4) (actual time=0.014..0.030 rows=100 loops=1)

20. 36.914 92.444 ↓ 77,481.0 77,481 1

Hash (cost=8.37..8.37 rows=1 width=15) (actual time=92.444..92.444 rows=77,481 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
21. 55.530 55.530 ↓ 77,481.0 77,481 1

Index Scan using ix_process_game_player_rating_process_id on process_game_player_rating process_game_player_rating_2 (cost=0.43..8.37 rows=1 width=15) (actual time=0.038..55.530 rows=77,481 loops=1)

  • Index Cond: (process_id = 52)
  • Filter: (param_type = 'map'::paramtype)