explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0VWv

Settings
# exclusive inclusive rows x rows loops node
1. 2,768.290 334,606.356 ↑ 1.0 6,313,790 1

Hash Join (cost=1,157,291,875.14..1,157,490,231.22 rows=6,313,814 width=16) (actual time=330,899.184..334,606.356 rows=6,313,790 loops=1)

  • Hash Cond: (sa.learnerid = learnerset_learner.learnerid)
2. 939.370 939.370 ↑ 1.0 6,313,790 1

Seq Scan on studentattendances sa (cost=0.00..181,782.14 rows=6,313,814 width=16) (actual time=0.015..939.370 rows=6,313,790 loops=1)

3. 87.973 330,898.696 ↑ 1.0 394,697 1

Hash (cost=1,157,286,941.42..1,157,286,941.42 rows=394,697 width=16) (actual time=330,898.696..330,898.696 rows=394,697 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 22598kB
4. 393.962 330,810.723 ↑ 1.0 394,697 1

HashAggregate (cost=1,157,279,047.48..1,157,282,994.45 rows=394,697 width=16) (actual time=330,719.768..330,810.723 rows=394,697 loops=1)

  • Group Key: learnerset_learner.learnerid
5. 149.540 330,416.761 ↑ 115,541.1 789,394 1

Nested Loop (cost=20,393.74..929,260,444.31 rows=91,207,441,271 width=16) (actual time=10.513..330,416.761 rows=789,394 loops=1)

6. 9.464 9.464 ↓ 2.0 4,353 1

Seq Scan on learnerset (cost=0.00..107.53 rows=2,176 width=16) (actual time=0.009..9.464 rows=4,353 loops=1)

  • Filter: COALESCE((id = ANY (NULL::uuid[])), true)
7. 172,265.622 330,257.757 ↑ 231,575.6 181 4,353

Hash Join (cost=20,393.74..28,282.31 rows=41,915,184 width=16) (actual time=27.580..75.869 rows=181 loops=4,353)

  • Hash Cond: (learnerset_learner.learnersetid = descendantsets.childid)
8. 147,679.878 147,679.878 ↑ 1.0 394,697 4,353

Seq Scan on learnerset_learner (cost=0.00..6,849.97 rows=394,697 width=32) (actual time=0.005..33.926 rows=394,697 loops=4,353)

9. 3,086.277 10,312.257 ↑ 92,178.0 2 4,353

Hash (cost=18,089.29..18,089.29 rows=184,356 width=16) (actual time=2.369..2.369 rows=2 loops=4,353)

  • Buckets: 262144 Batches: 1 Memory Usage: 2049kB
10. 3,996.054 7,225.980 ↑ 92,178.0 2 4,353

HashAggregate (cost=14,402.17..16,245.73 rows=184,356 width=16) (actual time=1.202..1.660 rows=2 loops=4,353)

  • Group Key: descendantsets.childid
11.          

CTE descendantsets

12. 21.765 178.473 ↑ 184,256.0 1 4,353

Recursive Union (cost=0.28..8,412.08 rows=184,256 width=32) (actual time=0.029..0.041 rows=1 loops=4,353)

13. 39.177 39.177 ↑ 86.0 1 4,353

Index Scan using learnerset_learnerset_ix_parentid on learnerset_learnerset base (cost=0.28..30.89 rows=86 width=32) (actual time=0.009..0.009 rows=1 loops=4,353)

  • Index Cond: (parentid = $1)
14. 26.118 117.531 ↓ 0.0 0 4,353

Merge Join (cost=59.40..469.61 rows=18,417 width=32) (actual time=0.027..0.027 rows=0 loops=4,353)

  • Merge Cond: (child.parentid = ancestor.childid)
15. 60.942 60.942 ↑ 107.1 40 4,353

Index Scan using learnerset_learnerset_ix_parentid on learnerset_learnerset child (cost=0.28..123.53 rows=4,283 width=32) (actual time=0.006..0.014 rows=40 loops=4,353)

16. 30.471 30.471 ↑ 860.0 1 4,353

Sort (cost=59.12..61.27 rows=860 width=16) (actual time=0.007..0.007 rows=1 loops=4,353)

  • Sort Key: ancestor.childid
  • Sort Method: quicksort Memory: 25kB
17. 0.000 0.000 ↑ 860.0 1 4,353

WorkTable Scan on descendantsets ancestor (cost=0.00..17.20 rows=860 width=16) (actual time=0.000..0.000 rows=1 loops=4,353)

18. 4.353 3,229.926 ↑ 92,178.0 2 4,353

Append (cost=0.00..5,529.20 rows=184,356 width=16) (actual time=0.728..0.742 rows=2 loops=4,353)

19. 3,195.102 3,195.102 ↑ 184,256.0 1 4,353

CTE Scan on descendantsets (cost=0.00..3,685.12 rows=184,256 width=16) (actual time=0.720..0.734 rows=1 loops=4,353)

20. 26.118 30.471 ↑ 100.0 1 4,353

ProjectSet (cost=0.00..0.52 rows=100 width=16) (actual time=0.006..0.007 rows=1 loops=4,353)

21. 4.353 4.353 ↑ 1.0 1 4,353

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=4,353)

Planning time : 0.403 ms
Execution time : 334,902.865 ms