explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lrfr

Settings
# exclusive inclusive rows x rows loops node
1. 0.319 25,212.948 ↑ 1.9 81 1

Sort (cost=10,274.98..10,275.37 rows=156 width=596) (actual time=25,212.942..25,212.948 rows=81 loops=1)

  • Sort Key: p.lastname
  • Sort Method: quicksort Memory: 45kB
2.          

CTE allcoachesdata

3. 11.542 16,843.379 ↑ 1.0 1 1

Aggregate (cost=3,126.57..3,126.61 rows=1 width=224) (actual time=16,843.379..16,843.379 rows=1 loops=1)

4. 1.493 16,831.837 ↓ 1.8 2,126 1

Nested Loop (cost=1,310.10..1,600.02 rows=1,202 width=1,743) (actual time=14,853.130..16,831.837 rows=2,126 loops=1)

5. 3.552 14,857.416 ↓ 1.8 2,126 1

Hash Join (cost=1,309.85..1,575.73 rows=1,202 width=1,715) (actual time=14,852.498..14,857.416 rows=2,126 loops=1)

  • Hash Cond: (pa_1.id = allclients.coach_person_availability_id)
6. 1.648 1.648 ↓ 1.0 7,721 1

Seq Scan on person_availability pa_1 (cost=0.00..198.72 rows=7,672 width=8) (actual time=0.007..1.648 rows=7,721 loops=1)

7. 4.594 14,852.216 ↓ 1.8 2,126 1

Hash (cost=1,294.82..1,294.82 rows=1,202 width=1,715) (actual time=14,852.215..14,852.216 rows=2,126 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1,527kB
8. 14,847.622 14,847.622 ↓ 1.8 2,131 1

Seq Scan on client allclients (cost=0.00..1,294.82 rows=1,202 width=1,715) (actual time=5.937..14,847.622 rows=2,131 loops=1)

  • Filter: f_client_receivedfeedbackrequestmail(id)
  • Rows Removed by Filter: 1,444
9. 1,972.928 1,972.928 ↑ 1.0 1 2,126

Function Scan on f_coach_getjobratio jobratios (cost=0.25..0.26 rows=1 width=32) (actual time=0.928..0.928 rows=1 loops=2,126)

10. 8,318.253 25,212.629 ↑ 1.9 81 1

HashAggregate (cost=7,087.70..7,142.69 rows=156 width=596) (actual time=16,972.427..25,212.629 rows=81 loops=1)

  • Group Key: p.id, allcoachesdata.cl_avg, allcoachesdata.hp_avg, allcoachesdata.gg_avg, allcoachesdata.atm_avg, allcoachesdata.fbr_avg, allcoachesdata.tmp_avg, jobratio.hasjobratio
11. 0.732 16,894.376 ↓ 1.0 2,538 1

Hash Join (cost=331.79..772.44 rows=2,479 width=268) (actual time=16,891.210..16,894.376 rows=2,538 loops=1)

  • Hash Cond: (pa.person_id = p.id)
12. 1.821 4.409 ↑ 1.3 2,857 1

Hash Join (cost=294.62..697.16 rows=3,607 width=28) (actual time=1.969..4.409 rows=2,857 loops=1)

  • Hash Cond: (c.coach_person_availability_id = pa.id)
13. 0.639 0.639 ↑ 1.0 3,575 1

Seq Scan on client c (cost=0.00..393.07 rows=3,607 width=28) (actual time=0.005..0.639 rows=3,575 loops=1)

14. 0.873 1.949 ↓ 1.0 7,721 1

Hash (cost=198.72..198.72 rows=7,672 width=8) (actual time=1.949..1.949 rows=7,721 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 366kB
15. 1.076 1.076 ↓ 1.0 7,721 1

Seq Scan on person_availability pa (cost=0.00..198.72 rows=7,672 width=8) (actual time=0.003..1.076 rows=7,721 loops=1)

16. 0.087 16,889.235 ↑ 1.0 156 1

Hash (cost=35.22..35.22 rows=156 width=244) (actual time=16,889.234..16,889.235 rows=156 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
17. 0.044 16,889.148 ↑ 1.0 156 1

Nested Loop (cost=0.25..35.22 rows=156 width=244) (actual time=16,843.754..16,889.148 rows=156 loops=1)

18. 0.026 16,843.552 ↑ 1.0 156 1

Nested Loop (cost=0.00..31.85 rows=156 width=212) (actual time=16,843.390..16,843.552 rows=156 loops=1)

19. 16,843.383 16,843.383 ↑ 1.0 1 1

CTE Scan on allcoachesdata (cost=0.00..0.02 rows=1 width=192) (actual time=16,843.382..16,843.383 rows=1 loops=1)

20. 0.143 0.143 ↑ 1.0 156 1

Seq Scan on person p (cost=0.00..30.27 rows=156 width=20) (actual time=0.007..0.143 rows=156 loops=1)

  • Filter: isactive
  • Rows Removed by Filter: 71
21. 45.552 45.552 ↑ 1.0 1 156

Function Scan on f_coach_getjobratio jobratio (cost=0.25..0.26 rows=1 width=32) (actual time=0.292..0.292 rows=1 loops=156)