explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G9w : Optimization for: Optimization for: Optimization for: v3; plan #qXbP; plan #1hSH; plan #Lp8E

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 92.609 225.782 ↑ 2.4 161,886 1

HashAggregate (cost=1,157,279,052.92..1,157,282,999.89 rows=394,697 width=16) (actual time=190.702..225.782 rows=161,886 loops=1)

  • Output: learnerset_learner.learnerid
  • Group Key: learnerset_learner.learnerid
  • Buffers: shared hit=3571
2. 20.143 133.173 ↑ 563,405.4 161,886 1

Nested Loop (cost=20,393.74..929,260,449.75 rows=91,207,441,271 width=16) (actual time=6.247..133.173 rows=161,886 loops=1)

  • Output: learnerset_learner.learnerid
  • Buffers: shared hit=3571
3. 0.494 0.494 ↑ 2,176.0 1 1

Seq Scan on biz.learnerset (cost=0.00..112.97 rows=2,176 width=16) (actual time=0.019..0.494 rows=1 loops=1)

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

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

  • Output: learnerset_learner.learnerid
  • Inner Unique: true
  • Hash Cond: (learnerset_learner.learnersetid = descendantsets.childid)
  • Buffers: shared hit=3507
5. 38.111 38.111 ↑ 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.005..38.111 rows=394,697 loops=1)

  • Output: learnerset_learner.learnersetid, learnerset_learner.learnerid
  • Buffers: shared hit=2903
6. 0.220 5.324 ↑ 174.6 1,056 1

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

  • Output: descendantsets.childid
  • Buckets: 262144 Batches: 1 Memory Usage: 2098kB
  • Buffers: shared hit=604
7. 1.417 5.104 ↑ 174.6 1,056 1

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

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

CTE descendantsets

9. 0.446 2.606 ↑ 174.7 1,055 1

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

  • Buffers: shared hit=604
10. 0.281 0.281 ↓ 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.024..0.281 rows=1,055 loops=1)

  • Output: base.parentid, base.childid
  • Index Cond: (base.parentid = $1)
  • Buffers: shared hit=46
11. 0.628 1.879 ↓ 0.0 0 1

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

  • Output: child.parentid, child.childid
  • Merge Cond: (child.parentid = ancestor.childid)
  • Buffers: shared hit=558
12. 0.766 0.766 ↑ 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.011..0.766 rows=4,283 loops=1)

  • Output: child.parentid, child.childid
  • Buffers: shared hit=558
13. 0.391 0.485 ↓ 1.2 1,033 1

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

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

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

  • Output: ancestor.childid
15. 0.116 3.687 ↑ 174.6 1,056 1

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

  • Buffers: shared hit=604
16. 3.564 3.564 ↑ 174.7 1,055 1

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

  • Output: descendantsets.childid
  • Buffers: shared hit=604
17. 0.007 0.007 ↑ 100.0 1 1

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

  • Output: unnest(ARRAY[learnerset.id])
18. 0.000 0.000 ↑ 1.0 1 1

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

Planning time : 0.404 ms
Execution time : 245.385 ms