explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qXbP : v3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 486.648 1,023.116 ↑ 2.4 161,886 1

HashAggregate (cost=578,649,804.20..578,653,751.17 rows=394,697 width=16) (actual time=865.559..1,023.116 rows=161,886 loops=1)

  • Output: learnerset_learner.learnerid
  • Group Key: learnerset_learner.learnerid
  • Buffers: shared hit=3638
2. 75.747 536.468 ↑ 281,702.7 161,886 1

Nested Loop (cost=20,407.24..464,640,502.61 rows=45,603,720,636 width=16) (actual time=20.049..536.468 rows=161,886 loops=1)

  • Output: learnerset_learner.learnerid
  • Buffers: shared hit=3638
3. 3.525 6.693 ↑ 1,088.0 1 1

Seq Scan on biz.learnerset (cost=13.50..137.35 rows=1,088 width=16) (actual time=4.756..6.693 rows=1 loops=1)

  • Output: learnerset.id, learnerset.version, learnerset.createdon, learnerset.updatedon, learnerset.tenantid, learnerset.isenabled, learnerset.code, learnerset.name
  • Filter: ((hashed SubPlan 1) AND COALESCE((learnerset.id = ANY ('{1bf91618-07e0-465e-a55d-9cfe00f75c2a}'::uuid[])), true))
  • Rows Removed by Filter: 4352
  • Buffers: shared hit=131
4.          

SubPlan (for Seq Scan)

5. 3.168 3.168 ↓ 4.4 4,353 1

Function Scan on biz.learnerset_canselect (cost=1.00..11.00 rows=1,000 width=16) (actual time=2.463..3.168 rows=4,353 loops=1)

  • Output: learnerset_canselect.id
  • Function Call: biz.learnerset_canselect(NULL::uuid, biz.current_tenantid(), biz.current_identityid(), biz.current_actor())
  • Buffers: shared hit=67
6. 265.464 454.028 ↑ 258.9 161,886 1

Hash Join (cost=20,393.74..28,282.31 rows=41,915,184 width=16) (actual time=15.289..454.028 rows=161,886 loops=1)

  • Output: learnerset_learner.learnerid
  • Inner Unique: true
  • Hash Cond: (learnerset_learner.learnersetid = descendantsets.childid)
  • Buffers: shared hit=3507
7. 173.567 173.567 ↑ 1.0 394,697 1

Seq Scan on biz.learnerset_learner (cost=0.00..6,849.97 rows=394,697 width=32) (actual time=0.011..173.567 rows=394,697 loops=1)

  • Output: learnerset_learner.learnersetid, learnerset_learner.learnerid
  • Buffers: shared hit=2903
8. 0.345 14.997 ↑ 174.6 1,056 1

Hash (cost=18,089.29..18,089.29 rows=184,356 width=16) (actual time=14.997..14.997 rows=1,056 loops=1)

  • Output: descendantsets.childid
  • Buckets: 262144 Batches: 1 Memory Usage: 2098kB
  • Buffers: shared hit=604
9. 9.900 14.652 ↑ 174.6 1,056 1

HashAggregate (cost=14,402.17..16,245.73 rows=184,356 width=16) (actual time=5.304..14.652 rows=1,056 loops=1)

  • Output: descendantsets.childid
  • Group Key: descendantsets.childid
  • Buffers: shared hit=604
10.          

CTE descendantsets

11. 0.582 3.516 ↑ 174.7 1,055 1

Recursive Union (cost=0.28..8,412.08 rows=184,256 width=32) (actual time=0.020..3.516 rows=1,055 loops=1)

  • Buffers: shared hit=604
12. 0.359 0.359 ↓ 12.3 1,055 1

Index Scan using learnerset_learnerset_ix_parentid on biz.learnerset_learnerset base (cost=0.28..30.89 rows=86 width=32) (actual time=0.017..0.359 rows=1,055 loops=1)

  • Output: base.parentid, base.childid
  • Index Cond: (base.parentid = $2)
  • Buffers: shared hit=46
13. 0.704 2.575 ↓ 0.0 0 1

Merge Join (cost=59.40..469.61 rows=18,417 width=32) (actual time=2.575..2.575 rows=0 loops=1)

  • Output: child.parentid, child.childid
  • Merge Cond: (child.parentid = ancestor.childid)
  • Buffers: shared hit=558
14. 1.235 1.235 ↑ 1.0 4,283 1

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

  • Output: child.parentid, child.childid
  • Buffers: shared hit=558
15. 0.498 0.636 ↓ 1.2 1,033 1

Sort (cost=59.12..61.27 rows=860 width=16) (actual time=0.545..0.636 rows=1,033 loops=1)

  • Output: ancestor.childid
  • Sort Key: ancestor.childid
  • Sort Method: quicksort Memory: 98kB
16. 0.138 0.138 ↓ 1.2 1,055 1

WorkTable Scan on descendantsets ancestor (cost=0.00..17.20 rows=860 width=16) (actual time=0.002..0.138 rows=1,055 loops=1)

  • Output: ancestor.childid
17. 0.118 4.752 ↑ 174.6 1,056 1

Append (cost=0.00..5,529.20 rows=184,356 width=16) (actual time=0.776..4.752 rows=1,056 loops=1)

  • Buffers: shared hit=604
18. 4.625 4.625 ↑ 174.7 1,055 1

CTE Scan on descendantsets (cost=0.00..3,685.12 rows=184,256 width=16) (actual time=0.775..4.625 rows=1,055 loops=1)

  • Output: descendantsets.childid
  • Buffers: shared hit=604
19. 0.008 0.009 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Output: unnest(ARRAY[learnerset.id])
20. 0.001 0.001 ↑ 1.0 1 1

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

Planning time : 0.469 ms
Execution time : 1,054.095 ms