explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gDL9

Settings
# exclusive inclusive rows x rows loops node
1. 1.179 8,639.498 ↓ 506.0 506 1

Sort (cost=52,733.19..52,733.19 rows=1 width=21) (actual time=8,639.475..8,639.498 rows=506 loops=1)

  • Sort Key: this_.lastname, this_.firstname
  • Sort Method: quicksort Memory: 64kB
2. 0.892 8,638.319 ↓ 506.0 506 1

Nested Loop Left Join (cost=25,344.06..52,733.18 rows=1 width=21) (actual time=1,191.373..8,638.319 rows=506 loops=1)

  • Filter: (((studentcou5_.enrolment_department_id)::text = '7e17606d-731f-4506-84d4-3e9498d7ae0b'::text) OR ((route4_.department_id)::text = '7e17606d-731f-4506-84d4-3e9498d7ae0b'::text) OR ((this_.home_department_id)::text = '7e17606d-731f-4506-84d4-3e9498d7ae0b'::text) OR ((department2_.parent_id)::text = '7e17606d-731f-4506-84d4-3e9498d7ae0b'::text) OR (route4_.teachingdepartmentsactive AND ((teachingin1_.department_id)::text = '7e17606d-731f-4506-84d4-3e9498d7ae0b'::text)))
  • Rows Removed by Filter: 13,560
3. 10.756 8,596.879 ↓ 6,758.0 13,516 1

Nested Loop (cost=25,343.79..52,732.54 rows=2 width=207) (actual time=343.926..8,596.879 rows=13,516 loops=1)

4. 5,557.399 8,545.575 ↓ 6,758.0 13,516 1

Nested Loop (cost=25,343.51..52,731.92 rows=2 width=170) (actual time=343.903..8,545.575 rows=13,516 loops=1)

  • Join Filter: ((mostsignif3_.routecode)::text = (route4_.code)::text)
  • Rows Removed by Join Filter: 53,104,364
5. 9.236 9.236 ↑ 1.0 3,930 1

Index Scan using pk_route on route route4_ (cost=0.28..185.27 rows=3,973 width=80) (actual time=0.012..9.236 rows=3,930 loops=1)

6. 2,295.492 2,978.940 ↓ 6,758.0 13,516 3,930

Materialize (cost=25,343.23..52,427.46 rows=2 width=99) (actual time=0.033..0.758 rows=13,516 loops=3,930)

7. 40.448 683.448 ↓ 6,758.0 13,516 1

Hash Join (cost=25,343.23..52,427.45 rows=2 width=99) (actual time=130.390..683.448 rows=13,516 loops=1)

  • Hash Cond: (((this_.mostsignificantcourse)::text = (studentcou5_.scjcode)::text) AND ((SubPlan 1) = studentcou5_.scesequencenumber))
8. 0.000 119.911 ↓ 2.8 29,626 1

Gather (cost=8,354.96..33,042.77 rows=10,530 width=82) (actual time=38.373..119.911 rows=29,626 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
9. 13.090 151.433 ↓ 2.2 7,406 4 / 4

Hash Join (cost=7,354.96..30,989.77 rows=3,397 width=82) (actual time=51.382..151.433 rows=7,406 loops=4)

  • Hash Cond: ((this_.mostsignificantcourse)::text = (mostsignif3_.scjcode)::text)
10. 87.511 87.511 ↑ 1.6 26,082 4 / 4

Parallel Seq Scan on member this_ (cost=0.00..23,522.44 rows=42,809 width=68) (actual time=0.025..87.511 rows=26,082 loops=4)

  • Filter: ((missingfromimportsince IS NULL) AND ((usertype)::text = 'S'::text))
  • Rows Removed by Filter: 81,639
11. 8.858 50.832 ↑ 1.2 37,145 4 / 4

Hash (cost=6,802.16..6,802.16 rows=44,224 width=14) (actual time=50.832..50.832 rows=37,145 loops=4)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,218kB
12. 36.927 41.974 ↑ 1.2 37,145 4 / 4

Bitmap Heap Scan on studentcoursedetails mostsignif3_ (cost=499.36..6,802.16 rows=44,224 width=14) (actual time=5.692..41.974 rows=37,145 loops=4)

  • Recheck Cond: ((sprstatuscode)::text = 'C'::text)
  • Heap Blocks: exact=4,547
13. 5.047 5.047 ↓ 1.3 56,635 4 / 4

Bitmap Index Scan on idx_studentcoursedetails_sprstatus (cost=0.00..488.30 rows=44,224 width=0) (actual time=5.046..5.047 rows=56,635 loops=4)

  • Index Cond: ((sprstatuscode)::text = 'C'::text)
14. 5.682 91.669 ↑ 1.0 17,659 1

Hash (cost=16,710.75..16,710.75 rows=18,502 width=49) (actual time=91.669..91.669 rows=17,659 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,671kB
15. 85.987 85.987 ↑ 1.0 17,659 1

Index Scan using idx_studentcourseyeardetails_year on studentcourseyeardetails studentcou5_ (cost=0.42..16,710.75 rows=18,502 width=49) (actual time=0.075..85.987 rows=17,659 loops=1)

  • Index Cond: (yearofstudy = 1)
  • Filter: (academicyear = 2,019)
  • Rows Removed by Filter: 179,914
16.          

SubPlan (for Hash Join)

17. 0.000 431.420 ↑ 1.0 1 43,142

Aggregate (cost=4.44..4.45 rows=1 width=2) (actual time=0.010..0.010 rows=1 loops=43,142)

18. 431.420 431.420 ↑ 1.0 1 43,142

Index Scan using idx_studentcourseyeardetails_ck on studentcourseyeardetails latestscyd_ (cost=0.42..4.43 rows=1 width=2) (actual time=0.009..0.010 rows=1 loops=43,142)

  • Index Cond: ((scjcode)::text = (mostsignif3_.scjcode)::text)
  • Filter: ((missingfromimportsince IS NULL) AND (academicyear = 2,019))
  • Rows Removed by Filter: 1
19. 40.548 40.548 ↑ 1.0 1 13,516

Index Scan using pk_department on department department2_ (cost=0.27..0.31 rows=1 width=74) (actual time=0.003..0.003 rows=1 loops=13,516)

  • Index Cond: ((id)::text = (route4_.department_id)::text)
20. 40.548 40.548 ↓ 0.0 0 13,516

Index Scan using idx_routeteachinginformation_route on routeteachinginformation teachingin1_ (cost=0.28..0.30 rows=1 width=74) (actual time=0.003..0.003 rows=0 loops=13,516)

  • Index Cond: ((route4_.id)::text = (route_id)::text)
Planning time : 4.169 ms
Execution time : 8,641.180 ms