explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aFrV

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 238,828.797 ↑ 4,988.6 61 1

HashAggregate (cost=8,574,591.36..8,577,634.42 rows=304,306 width=4) (actual time=238,826.460..238,828.797 rows=61 loops=1)

  • Group Key: game.game_id
2.          

Initplan (for HashAggregate)

3. 0.005 0.046 ↑ 1.0 1 1

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

4.          

Initplan (for Result)

5. 0.001 0.041 ↑ 1.0 1 1

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

6. 0.040 0.040 ↑ 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.040..0.040 rows=1 loops=1)

  • Index Cond: (_updated IS NOT NULL)
  • Heap Fetches: 7
7. 1,937.306 240,207.506 ↑ 864.0 16,353 1

Gather (cost=3,764,827.79..8,539,266.54 rows=14,129,681 width=4) (actual time=108,567.536..240,207.506 rows=16,353 loops=1)

  • Workers Planned: 2
  • Params Evaluated: $1
  • Workers Launched: 2
8. 7,083.073 238,270.200 ↑ 1,080.1 5,451 3 / 3

Parallel Hash Join (cost=3,763,827.79..7,125,298.44 rows=5,887,367 width=4) (actual time=107,991.606..238,270.200 rows=5,451 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,124,237
9. 11,516.033 231,131.052 ↑ 1.2 14,129,688 3 / 3

Parallel Hash Left Join (cost=3,751,831.93..7,066,939.25 rows=17,662,102 width=52) (actual time=104,256.223..231,131.052 rows=14,129,688 loops=3)

  • Hash Cond: (pitch.pitch_id = bip_metric.pitch_id)
10. 12,136.779 218,306.198 ↑ 1.2 14,129,688 3 / 3

Parallel Hash Left Join (cost=3,569,187.68..6,762,184.79 rows=17,662,102 width=60) (actual time=102,932.617..218,306.198 rows=14,129,688 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_type.pitch_id)
11. 12,922.003 198,544.388 ↑ 1.2 14,129,688 3 / 3

Parallel Hash Left Join (cost=3,015,418.02..6,162,052.11 rows=17,662,102 width=52) (actual time=95,257.503..198,544.388 rows=14,129,688 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_metric.pitch_id)
12. 11,372.452 170,781.245 ↑ 1.2 14,129,688 3 / 3

Parallel Hash Left Join (cost=1,859,934.83..4,960,205.90 rows=17,662,102 width=44) (actual time=80,339.467..170,781.245 rows=14,129,688 loops=3)

  • Hash Cond: (pitch.pitch_id = pitch_tracking.pitch_id)
13. 19,598.252 101,422.197 ↑ 1.2 14,129,688 3 / 3

Parallel Hash Left Join (cost=684,107.70..3,738,015.75 rows=17,662,102 width=36) (actual time=22,234.347..101,422.197 rows=14,129,688 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,050
14. 59,672.464 59,672.464 ↑ 1.2 14,129,688 3 / 3

Parallel Seq Scan on pitch (cost=0.00..2,961,182.02 rows=17,662,102 width=45) (actual time=15.649..59,672.464 rows=14,129,688 loops=3)

15. 6,378.624 22,151.481 ↑ 1.3 7,138,818 3 / 3

Parallel Hash (cost=550,186.08..550,186.08 rows=8,928,108 width=28) (actual time=22,151.480..22,151.481 rows=7,138,818 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,603,328kB
16. 15,772.857 15,772.857 ↑ 1.3 7,138,818 3 / 3

Parallel Seq Scan on plate_app (cost=0.00..550,186.08 rows=8,928,108 width=28) (actual time=11.297..15,772.857 rows=7,138,818 loops=3)

17. 3,736.632 57,986.596 ↑ 1.3 6,010,808 3 / 3

Parallel Hash (cost=1,081,142.06..1,081,142.06 rows=7,574,806 width=24) (actual time=57,986.596..57,986.596 rows=6,010,808 loops=3)

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

Parallel Seq Scan on pitch_tracking (cost=0.00..1,081,142.06 rows=7,574,806 width=24) (actual time=0.540..54,249.964 rows=6,010,808 loops=3)

19. 4,023.077 14,841.140 ↑ 1.2 8,982,948 3 / 3

Parallel Hash (cost=1,016,233.64..1,016,233.64 rows=11,139,964 width=24) (actual time=14,841.140..14,841.140 rows=8,982,948 loops=3)

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,738,848kB
20. 10,818.063 10,818.063 ↑ 1.2 8,982,948 3 / 3

Parallel Seq Scan on pitch_metric (cost=0.00..1,016,233.64 rows=11,139,964 width=24) (actual time=0.046..10,818.063 rows=8,982,948 loops=3)

21. 3,742.238 7,625.031 ↑ 1.2 8,456,056 3 / 3

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

  • Buckets: 33,554,432 Batches: 1 Memory Usage: 1,652,256kB
22. 3,882.793 3,882.793 ↑ 1.2 8,456,056 3 / 3

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

23. 899.846 1,308.821 ↑ 1.2 2,252,278 3 / 3

Parallel Hash (cost=147,478.00..147,478.00 rows=2,813,300 width=24) (actual time=1,308.821..1,308.821 rows=2,252,278 loops=3)

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

Parallel Seq Scan on bip_metric (cost=0.00..147,478.00 rows=2,813,300 width=24) (actual time=0.010..408.975 rows=2,252,278 loops=3)

25. 33.879 56.075 ↑ 1.3 100,461 3 / 3

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 18,304kB
26. 22.196 22.196 ↑ 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.007..22.196 rows=100,461 loops=3)

Planning time : 2.567 ms
Execution time : 240,244.883 ms