explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TshQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 141,611.122 ↑ 43,472.3 7 1

HashAggregate (cost=8,575,312.03..8,578,355.09 rows=304,306 width=4) (actual time=141,609.383..141,611.122 rows=7 loops=1)

  • Group Key: game.game_id
2.          

Initplan (for HashAggregate)

3. 0.004 0.027 ↑ 1.0 1 1

Result (cost=0.61..0.62 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)

4.          

Initplan (for Result)

5. 0.002 0.023 ↑ 1.0 1 1

Limit (cost=0.56..0.61 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=1)

6. 0.021 0.021 ↑ 20,044,608.0 1 1

Index Only Scan Backward using _ix_bluemedia_42pro_pitch__updated on pitch pitch_1 (cost=0.56..922,783.14 rows=20,044,608 width=8) (actual time=0.021..0.021 rows=1 loops=1)

  • Index Cond: (_updated IS NOT NULL)
  • Heap Fetches: 3
7. 1,444.539 142,594.494 ↑ 13,041.2 1,084 1

Gather (cost=3,764,550.07..8,539,969.68 rows=14,136,691 width=4) (actual time=126,368.861..142,594.494 rows=1,084 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 2
8. 4,314.859 141,149.955 ↑ 16,316.6 361 3 / 3

Parallel Hash Join (cost=3,763,550.07..7,125,300.58 rows=5,890,288 width=4) (actual time=126,366.261..141,149.955 rows=361 loops=3)

  • Hash Cond: (pitch.game_id = game.game_id)
  • Join Filter: (GREATEST(game._updated, plate_app._updated, pitch._updated, pitch_metric._updated, pitch_tracking._updated, pitch_type._updated, bip_metric._updated) > COALESCE($1, '-infinity'::timestamp with time zone))
  • Rows Removed by Join Filter: 14,129,312
9. 8,838.309 136,773.690 ↑ 1.3 14,129,673 3 / 3

Parallel Hash Left Join (cost=3,751,554.20..7,066,918.39 rows=17,670,863 width=52) (actual time=67,994.619..136,773.690 rows=14,129,673 loops=3)

  • Hash Cond: (pitch.pitch_id = bip_metric.pitch_id)
10. 9,201.896 126,695.954 ↑ 1.3 14,129,673 3 / 3

Parallel Hash Left Join (cost=3,568,917.61..6,762,117.32 rows=17,670,863 width=60) (actual time=66,730.035..126,695.954 rows=14,129,673 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_type.pitch_id)
11. 9,902.492 110,263.541 ↑ 1.3 14,129,673 3 / 3

Parallel Hash Left Join (cost=3,015,147.95..6,161,961.65 rows=17,670,863 width=52) (actual time=59,449.210..110,263.541 rows=14,129,673 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_metric.pitch_id)
12. 8,621.551 89,895.054 ↑ 1.3 14,129,673 3 / 3

Parallel Hash Left Join (cost=1,859,664.76..4,960,092.44 rows=17,670,863 width=44) (actual time=48,923.553..89,895.054 rows=14,129,673 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_tracking.pitch_id)
13. 12,189.034 59,550.050 ↑ 1.3 14,129,673 3 / 3

Parallel Hash Left Join (cost=684,075.05..3,738,116.71 rows=17,670,863 width=36) (actual time=27,118.100..59,550.050 rows=14,129,673 loops=3)

  • Hash Cond: ((pitch.game_id = plate_app.game_id) AND (pitch.pa_id = plate_app.pa_id))
  • Join Filter: (pitch.last_pitch_of_pa IS TRUE)
  • Rows Removed by Join Filter: 10,300,039
14. 36,660.401 36,660.401 ↑ 1.3 14,129,673 3 / 3

Parallel Seq Scan on pitch (cost=0.00..2,961,269.63 rows=17,670,863 width=45) (actual time=16,371.363..36,660.401 rows=14,129,673 loops=3)

15. 3,991.547 10,700.615 ↑ 1.3 7,138,815 3 / 3

Parallel Hash (cost=550,159.82..550,159.82 rows=8,927,682 width=28) (actual time=10,700.614..10,700.615 rows=7,138,815 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,603,360kB
16. 6,709.068 6,709.068 ↑ 1.3 7,138,815 3 / 3

Parallel Seq Scan on plate_app (cost=0.00..550,159.82 rows=8,927,682 width=28) (actual time=0.531..6,709.068 rows=7,138,815 loops=3)

17. 3,321.574 21,723.453 ↑ 1.3 6,010,793 3 / 3

Parallel Hash (cost=1,080,923.76..1,080,923.76 rows=7,573,276 width=24) (actual time=21,723.453..21,723.453 rows=6,010,793 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,250,272kB
18. 18,401.879 18,401.879 ↑ 1.3 6,010,793 3 / 3

Parallel Seq Scan on pitch_tracking (cost=0.00..1,080,923.76 rows=7,573,276 width=24) (actual time=1.462..18,401.879 rows=6,010,793 loops=3)

19. 3,968.925 10,465.995 ↑ 1.2 8,982,933 3 / 3

Parallel Hash (cost=1,016,233.64..1,016,233.64 rows=11,139,964 width=24) (actual time=10,465.995..10,465.995 rows=8,982,933 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,738,848kB
20. 6,497.070 6,497.070 ↑ 1.2 8,982,933 3 / 3

Parallel Seq Scan on pitch_metric (cost=0.00..1,016,233.64 rows=11,139,964 width=24) (actual time=0.027..6,497.070 rows=8,982,933 loops=3)

21. 3,549.095 7,230.517 ↑ 1.2 8,456,041 3 / 3

Parallel Hash (cost=421,808.18..421,808.18 rows=10,556,918 width=24) (actual time=7,230.517..7,230.517 rows=8,456,041 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,652,224kB
22. 3,681.422 3,681.422 ↑ 1.2 8,456,041 3 / 3

Parallel Seq Scan on pitch_type (cost=0.00..421,808.18 rows=10,556,918 width=24) (actual time=0.013..3,681.422 rows=8,456,041 loops=3)

23. 869.599 1,239.427 ↑ 1.2 2,252,274 3 / 3

Parallel Hash (cost=147,471.82..147,471.82 rows=2,813,182 width=24) (actual time=1,239.427..1,239.427 rows=2,252,274 loops=3)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 435,808kB
24. 369.828 369.828 ↑ 1.2 2,252,274 3 / 3

Parallel Seq Scan on bip_metric (cost=0.00..147,471.82 rows=2,813,182 width=24) (actual time=0.012..369.828 rows=2,252,274 loops=3)

25. 37.158 61.406 ↑ 1.3 100,461 3 / 3

Parallel Hash (cost=10,410.94..10,410.94 rows=126,794 width=12) (actual time=61.406..61.406 rows=100,461 loops=3)

  • Buckets: 524,288 Batches: 1 Memory Usage: 18,272kB
26. 24.248 24.248 ↑ 1.3 100,461 3 / 3

Parallel Seq Scan on game (cost=0.00..10,410.94 rows=126,794 width=12) (actual time=0.012..24.248 rows=100,461 loops=3)

Planning time : 2.566 ms
Execution time : 142,598.735 ms