explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5R6Q : v3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.146 429.939 ↓ 3.4 484 1

Nested Loop (cost=190,227.76..42,748,787.92 rows=143 width=487) (actual time=5.246..429.939 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.809 5.809 ↓ 3.4 484 1

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

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

  • Output: learnerset_learner.learnerid
  • Buffers: shared hit=2532
4.          

CTE descendantsets

5. 1.845 8.498 ↑ 1,121.5 4,283 1

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

  • Buffers: shared hit=590
6. 1.914 3.127 ↓ 1.9 4,283 1

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

  • Output: (NULL::uuid), childid.childid
  • Group Key: (NULL::uuid), childid.childid
  • Buffers: shared hit=32
7. 0.451 1.213 ↓ 1.9 4,283 1

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

  • Buffers: shared hit=32
8. 0.003 0.003 ↓ 0.0 0 1

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

  • Output: NULL::uuid, childid.childid
  • Function Call: unnest(NULL::uuid[])
9. 0.759 0.759 ↓ 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.008..0.759 rows=4,283 loops=1)

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

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

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

  • Output: child.parentid, child.childid
  • Buffers: shared hit=558
12. 1.456 1.821 ↑ 5.3 4,196 1

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

  • Output: ancestor.childid
  • Sort Key: ancestor.childid
  • Sort Method: quicksort Memory: 393kB
13. 0.365 0.365 ↑ 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.365 rows=4,283 loops=1)

  • Output: ancestor.childid
14. 229.416 423.500 ↑ 2,767.0 1 484

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

  • Output: learnerset_learner.learnerid
  • Inner Unique: true
  • Hash Cond: (descendantsets.childid = learnerset_learner.learnersetid)
  • Buffers: shared hit=2532
15. 191.664 191.664 ↑ 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.396 rows=4,283 loops=484)

  • Output: descendantsets.parentid, descendantsets.childid
  • Buffers: shared hit=590
16. 0.484 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. 1.936 1.936 ↑ 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.004..0.004 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.366 ms
Execution time : 482.979 ms