explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9cfJ : v4

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.174 274.255 ↓ 484.0 484 1

Nested Loop (cost=190,227.76..42,748,788.28 rows=1 width=487) (actual time=5.325..274.255 rows=484 loops=1)

  • Output: alc.id, alc.tenantid, alc.learnerid, alc.effectivestart, alc.effectiveend, alc.unitid, alc.courseid, alc.servicetype, alc.relationship, alc.subjectid, alc.assessmentcompletedon, alc.digitalcompletedon, alc.isoral, alc.oralcompletedon, alc.oralstatus, alc.subjectpublicname, alc.learningpathcourseid, alc.coursename, alc.coursecode, alc.learningpathcoursename, alc.unitname, alc.unitcode, alc.username, alc.firstname, alc.lastname, alc.contactinfoemail, alc.addressinfocity, alc.companyid, alc.countrycode, alc.countryname, alc.companyname, alc.partnerid, alc.partnername, alc.cachedon, alc.currenton, alc.abouttoexpireon, alc.assessmentstatus, alc.isdigital, alc.digitalstatus, alc.assessmenttype
  • Inner Unique: true
  • Buffers: shared hit=2585
2. 5.945 5.945 ↓ 3.4 484 1

Seq Scan on dw.assessmentlearnercontracts alc (cost=0.00..748.43 rows=143 width=487) (actual time=0.018..5.945 rows=484 loops=1)

  • Output: alc.id, alc.tenantid, alc.learnerid, alc.effectivestart, alc.effectiveend, alc.unitid, alc.courseid, alc.servicetype, alc.relationship, alc.subjectid, alc.assessmentcompletedon, alc.digitalcompletedon, alc.isoral, alc.oralcompletedon, alc.oralstatus, alc.subjectpublicname, alc.learningpathcourseid, alc.coursename, alc.coursecode, alc.learningpathcoursename, alc.unitname, alc.unitcode, alc.username, alc.firstname, alc.lastname, alc.contactinfoemail, alc.addressinfocity, alc.companyid, alc.countrycode, alc.countryname, alc.companyname, alc.partnerid, alc.partnername, alc.cachedon, alc.currenton, alc.abouttoexpireon, alc.assessmentstatus, alc.isdigital, alc.digitalstatus, alc.assessmenttype
  • Filter: (COALESCE((alc.subjectid = ANY (NULL::uuid[])), true) AND COALESCE((alc.assessmenttype = ANY (NULL::text[])), true) AND COALESCE((alc.assessmentstatus = ANY (NULL::text[])), true) AND COALESCE((alc.servicetype = ANY (NULL::text[])), true) AND COALESCE((alc.effectivestart <= '2019-05-04 00:00:00+00'::timestamp with time zone), true) AND COALESCE((alc.effectiveend >= '2019-06-04 00:00:00+00'::timestamp with time zone), true))
  • Rows Removed by Filter: 8678
  • Buffers: shared hit=611
3. 0.484 268.136 ↑ 1.0 1 484

Subquery Scan on dl (cost=190,227.76..298,937.33 rows=1 width=16) (actual time=0.554..0.554 rows=1 loops=484)

  • Output: dl.learnerid
  • Filter: (alc.learnerid = dl.learnerid)
  • Buffers: shared hit=1974
4. 0.000 267.652 ↑ 1.0 1 484

Unique (cost=190,227.76..298,937.32 rows=1 width=16) (actual time=0.553..0.553 rows=1 loops=484)

  • Output: learnerset_learner.learnerid
  • Buffers: shared hit=1974
5.          

CTE descendantsets

6. 1.815 5.046 ↑ 1,148.3 4,183 1

Recursive Union (cost=109.46..190,225.30 rows=4,803,482 width=32) (actual time=2.374..5.046 rows=4,183 loops=1)

  • Buffers: shared hit=32
7. 2.038 3.231 ↓ 1.9 4,183 1

HashAggregate (cost=109.46..131.88 rows=2,242 width=32) (actual time=2.372..3.231 rows=4,183 loops=1)

  • Output: (NULL::uuid), childid.childid
  • Group Key: (NULL::uuid), childid.childid
  • Buffers: shared hit=32
8. 0.449 1.193 ↓ 1.9 4,283 1

Append (cost=0.00..98.25 rows=2,242 width=32) (actual time=0.010..1.193 rows=4,283 loops=1)

  • Buffers: shared hit=32
9. 0.002 0.002 ↓ 0.0 0 1

Function Scan on pg_catalog.unnest childid (cost=0.00..1.00 rows=100 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: NULL::uuid, childid.childid
  • Function Call: unnest(NULL::uuid[])
10. 0.742 0.742 ↓ 2.0 4,283 1

Seq Scan on biz.learnerset_learnerset base (cost=0.00..74.83 rows=2,142 width=32) (actual time=0.007..0.742 rows=4,283 loops=1)

  • Output: base.parentid, base.childid
  • Filter: COALESCE((base.parentid = ANY (NULL::uuid[])), true)
  • Buffers: shared hit=32
11. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=2,068.81..9,404.62 rows=480,124 width=32) (never executed)

  • Output: child.parentid, child.childid
  • Merge Cond: (child.parentid = ancestor.childid)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using learnerset_learnerset_ix_parentid on biz.learnerset_learnerset child (cost=0.28..123.53 rows=4,283 width=32) (never executed)

  • Output: child.parentid, child.childid
13. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,068.53..2,124.58 rows=22,420 width=16) (never executed)

  • Output: ancestor.childid
  • Sort Key: ancestor.childid
14. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on descendantsets ancestor (cost=0.00..448.40 rows=22,420 width=16) (never executed)

  • Output: ancestor.childid
15. 141.812 267.652 ↑ 2,767.0 1 484

Hash Join (cost=2.45..108,712.02 rows=2,767 width=16) (actual time=0.553..0.553 rows=1 loops=484)

  • Output: learnerset_learner.learnerid
  • Inner Unique: true
  • Hash Cond: (descendantsets.childid = learnerset_learner.learnersetid)
  • Buffers: shared hit=1974
16. 122.936 122.936 ↑ 1,833.4 2,620 484

CTE Scan on descendantsets (cost=0.00..96,069.64 rows=4,803,482 width=16) (actual time=0.005..0.254 rows=2,620 loops=484)

  • Output: descendantsets.parentid, descendantsets.childid
  • Buffers: shared hit=32
17. 0.484 2.904 ↑ 1.0 1 484

Hash (cost=2.44..2.44 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=484)

  • Output: learnerset_learner.learnerid, learnerset_learner.learnersetid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1942
18. 2.420 2.420 ↑ 1.0 1 484

Index Scan using learnerset_learner_ix_learnerid on biz.learnerset_learner (cost=0.42..2.44 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=484)

  • Output: learnerset_learner.learnerid, learnerset_learner.learnersetid
  • Index Cond: (learnerset_learner.learnerid = alc.learnerid)
  • Buffers: shared hit=1942
Planning time : 0.392 ms
Execution time : 327.543 ms