explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jmSr : v5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.113 431.764 ↓ 11.0 484 1

Nested Loop (cost=190,227.76..42,753,732.51 rows=44 width=487) (actual time=5.344..431.764 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
  • Buffers: shared hit=3143
2. 5.731 5.731 ↓ 3.4 484 1

Seq Scan on dw.assessmentlearnercontracts alc (cost=0.00..748.43 rows=143 width=487) (actual time=0.018..5.731 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 425.920 ↑ 1.0 1 484

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

  • Output: dl.learnerid
  • Filter: (alc.learnerid = dl.learnerid)
  • Buffers: shared hit=2532
4. 230.868 425.436 ↑ 2,767.0 1 484

Hash Join (cost=190,227.76..298,937.32 rows=2,767 width=16) (actual time=0.537..0.879 rows=1 loops=484)

  • Output: learnerset_learner.learnerid
  • Inner Unique: true
  • Hash Cond: (descendantsets.childid = learnerset_learner.learnersetid)
  • Buffers: shared hit=2532
5.          

CTE descendantsets

6. 1.952 8.638 ↑ 1,121.5 4,283 1

Recursive Union (cost=109.46..190,225.30 rows=4,803,482 width=32) (actual time=2.341..8.638 rows=4,283 loops=1)

  • Buffers: shared hit=590
7. 1.969 3.159 ↓ 1.9 4,283 1

HashAggregate (cost=109.46..131.88 rows=2,242 width=32) (actual time=2.340..3.159 rows=4,283 loops=1)

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

Append (cost=0.00..98.25 rows=2,242 width=32) (actual time=0.010..1.190 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.741 0.741 ↓ 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.741 rows=4,283 loops=1)

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

Merge Join (cost=2,068.81..9,404.62 rows=480,124 width=32) (actual time=3.527..3.527 rows=0 loops=1)

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

  • Output: child.parentid, child.childid
  • Buffers: shared hit=558
13. 1.440 1.792 ↑ 5.3 4,196 1

Sort (cost=2,068.53..2,124.58 rows=22,420 width=16) (actual time=1.539..1.792 rows=4,196 loops=1)

  • Output: ancestor.childid
  • Sort Key: ancestor.childid
  • Sort Method: quicksort Memory: 393kB
14. 0.352 0.352 ↑ 5.2 4,283 1

WorkTable Scan on descendantsets ancestor (cost=0.00..448.40 rows=22,420 width=16) (actual time=0.002..0.352 rows=4,283 loops=1)

  • Output: ancestor.childid
15. 192.148 192.148 ↑ 1,121.5 4,283 484

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

  • Output: descendantsets.parentid, descendantsets.childid
  • Buffers: shared hit=590
16. 0.000 2.420 ↑ 1.0 1 484

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

  • Output: learnerset_learner.learnerid, learnerset_learner.learnersetid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1942
17. 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.391 ms
Execution time : 483.182 ms