explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t9ek : v3

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.123 420.251 ↓ 3.4 484 1

Nested Loop (cost=190,227.76..42,748,787.92 rows=143 width=487) (actual time=5.293..420.251 rows=484 loops=1)

2. 6.308 6.308 ↓ 3.4 484 1

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

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

4.          

CTE descendantsets

5. 1.881 8.535 ↑ 1,121.5 4,283 1

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

6. 2.054 3.187 ↓ 1.9 4,283 1

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

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

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

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

9. 0.731 0.731 ↓ 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.731 rows=4,283 loops=1)

  • Filter: COALESCE((parentid = ANY (NULL::uuid[])), true)
10. 0.867 3.467 ↓ 0.0 0 1

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

  • Merge Cond: (child.parentid = ancestor.childid)
11. 0.802 0.802 ↑ 1.0 4,283 1

Index Scan using learnerset_learnerset_ix_parentid on learnerset_learnerset child (cost=0.28..123.53 rows=4,283 width=32) (actual time=0.009..0.802 rows=4,283 loops=1)

12. 1.445 1.798 ↑ 5.3 4,196 1

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

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

14. 218.768 413.336 ↑ 2,767.0 1 484

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

  • Hash Cond: (descendantsets.childid = learnerset_learner.learnersetid)
15. 191.180 191.180 ↑ 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.395 rows=4,283 loops=484)

16. 0.484 3.388 ↑ 1.0 1 484

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 2.904 2.904 ↑ 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.006..0.006 rows=1 loops=484)

  • Index Cond: (learnerid = alc.learnerid)
Planning time : 0.366 ms
Execution time : 474.524 ms