explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ScR7

Settings
# exclusive inclusive rows x rows loops node
1. 843.889 7,257.573 ↑ 28,466,380.6 3,725 1

Merge Join (cost=203,853,678.44..1,797,319,039.18 rows=106,037,267,617 width=120) (actual time=259.539..7,257.573 rows=3,725 loops=1)

  • Merge Cond: (sa.learnerid = descentantlearners.learnerid)
2.          

CTE descendantsets

3. 0.020 1.683 ↑ 117,672.6 39 1

Recursive Union (cost=0.00..181,705.65 rows=4,589,232 width=32) (actual time=0.020..1.683 rows=39 loops=1)

4. 0.404 0.404 ↑ 54.9 39 1

Seq Scan on learnerset_learnerset base (cost=0.00..80.18 rows=2,142 width=32) (actual time=0.018..0.404 rows=39 loops=1)

  • Filter: COALESCE((parentid = ANY ('{ac3c7115-9978-4a3d-830e-9d5201007bae}'::uuid[])), true)
  • Rows Removed by Filter: 4244
5. 0.473 1.259 ↓ 0.0 0 1

Merge Join (cost=1,969.49..8,984.08 rows=458,709 width=32) (actual time=1.259..1.259 rows=0 loops=1)

  • Merge Cond: (child.parentid = ancestor.childid)
6. 0.766 0.766 ↑ 1.0 4,263 1

Index Scan using learnerset_learnerset_ix_parentid on learnerset_learnerset child (cost=0.28..123.53 rows=4,283 width=32) (actual time=0.005..0.766 rows=4,263 loops=1)

7. 0.015 0.020 ↑ 549.2 39 1

Sort (cost=1,969.21..2,022.76 rows=21,420 width=16) (actual time=0.017..0.020 rows=39 loops=1)

  • Sort Key: ancestor.childid
  • Sort Method: quicksort Memory: 26kB
8. 0.005 0.005 ↑ 549.2 39 1

WorkTable Scan on descendantsets ancestor (cost=0.00..428.40 rows=21,420 width=16) (actual time=0.001..0.005 rows=39 loops=1)

9.          

CTE descentantlearners

10. 45.011 183.127 ↑ 7,452,913.5 140 1

Merge Join (cost=644,397.48..16,318,791.16 rows=1,043,407,893 width=16) (actual time=6.352..183.127 rows=140 loops=1)

  • Merge Cond: (learnerset_learner.learnersetid = descendantsets.childid)
11. 136.365 136.365 ↑ 1.0 389,959 1

Index Scan using learnerset_learner_ix_learnersetid on learnerset_learner (cost=0.42..10,815.88 rows=394,697 width=32) (actual time=0.010..136.365 rows=389,959 loops=1)

12. 0.028 1.751 ↑ 28,863.1 159 1

Materialize (cost=644,397.06..667,343.22 rows=4,589,232 width=16) (actual time=1.707..1.751 rows=159 loops=1)

13. 0.029 1.723 ↑ 117,672.6 39 1

Sort (cost=644,397.06..655,870.14 rows=4,589,232 width=16) (actual time=1.706..1.723 rows=39 loops=1)

  • Sort Key: descendantsets.childid
  • Sort Method: quicksort Memory: 26kB
14. 1.694 1.694 ↑ 117,672.6 39 1

CTE Scan on descendantsets (cost=0.00..91,784.64 rows=4,589,232 width=16) (actual time=0.021..1.694 rows=39 loops=1)

15. 6,229.931 6,229.931 ↑ 1.0 6,216,594 1

Index Scan using idx_studentattendances on studentattendances sa (cost=0.56..282,042.77 rows=6,313,814 width=120) (actual time=0.014..6,229.931 rows=6,216,594 loops=1)

16. 0.383 183.753 ↑ 280,034.3 3,726 1

Materialize (cost=187,353,181.08..192,570,220.55 rows=1,043,407,893 width=16) (actual time=183.244..183.753 rows=3,726 loops=1)

17. 0.182 183.370 ↑ 7,452,913.5 140 1

Sort (cost=187,353,181.08..189,961,700.82 rows=1,043,407,893 width=16) (actual time=183.243..183.370 rows=140 loops=1)

  • Sort Key: descentantlearners.learnerid
  • Sort Method: quicksort Memory: 31kB
18. 183.188 183.188 ↑ 7,452,913.5 140 1

CTE Scan on descentantlearners (cost=0.00..20,868,157.86 rows=1,043,407,893 width=16) (actual time=6.354..183.188 rows=140 loops=1)

Planning time : 0.271 ms
Execution time : 7,312.897 ms