explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 48Au : v4

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.319 254.918 ↓ 484.0 484 1

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

2. 5.823 5.823 ↓ 3.4 484 1

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

  • Filter: (COALESCE((subjectid = ANY (NULL::uuid[])), true) AND COALESCE((assessmenttype = ANY (NULL::text[])), true) AND COALESCE((assessmentstatus = ANY (NULL::text[])), true) AND COALESCE((servicetype = ANY (NULL::text[])), true) AND COALESCE((effectivestart <= '2019-05-04 00:00:00+00'::timestamp with time zone), true) AND COALESCE((effectiveend >= '2019-06-04 00:00:00+00'::timestamp with time zone), true))
  • Rows Removed by Filter: 8678
3. 0.000 248.776 ↑ 1.0 1 484

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

  • Filter: (alc.learnerid = dl.learnerid)
4. 0.484 248.776 ↑ 1.0 1 484

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

5.          

CTE descendantsets

6. 1.759 4.870 ↑ 1,148.3 4,183 1

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

7. 1.953 3.111 ↓ 1.9 4,183 1

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

  • Group Key: (NULL::uuid), childid.childid
8. 0.400 1.158 ↓ 1.9 4,283 1

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

9. 0.002 0.002 ↓ 0.0 0 1

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

10. 0.756 0.756 ↓ 2.0 4,283 1

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

  • Filter: COALESCE((parentid = ANY (NULL::uuid[])), true)
11. 0.000 0.000 ↓ 0.0 0

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

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

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

13. 0.000 0.000 ↓ 0.0 0

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

  • 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)

15. 129.712 248.292 ↑ 2,767.0 1 484

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

  • Hash Cond: (descendantsets.childid = learnerset_learner.learnersetid)
16. 116.160 116.160 ↑ 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.240 rows=2,620 loops=484)

17. 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)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 1.936 1.936 ↑ 1.0 1 484

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

  • Index Cond: (learnerid = alc.learnerid)
Planning time : 0.374 ms
Execution time : 307.868 ms