explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SOB5 : v2

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 40.028 607.314 ↓ 3.4 484 1

Hash Join (cost=20,002,944.61..20,012,813.46 rows=143 width=487) (actual time=487.121..607.314 rows=484 loops=1)

  • Hash Cond: (learnerset_learner.learnerid = alc.learnerid)
2. 237.135 561.567 ↑ 1.0 394,697 1

HashAggregate (cost=20,002,194.39..20,006,141.36 rows=394,697 width=16) (actual time=480.044..561.567 rows=394,697 loops=1)

  • Group Key: learnerset_learner.learnerid
3.          

CTE descendantsets

4. 1.893 8.544 ↑ 1,121.5 4,283 1

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

5. 1.980 3.200 ↓ 1.9 4,283 1

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

  • Group Key: (NULL::uuid), childid.childid
6. 0.408 1.220 ↓ 1.9 4,283 1

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

7. 0.003 0.003 ↓ 0.0 0 1

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

8. 0.809 0.809 ↓ 2.0 4,283 1

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

  • Filter: COALESCE((parentid = ANY (NULL::uuid[])), true)
9. 0.875 3.451 ↓ 0.0 0 1

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

  • Merge Cond: (child.parentid = ancestor.childid)
10. 0.795 0.795 ↑ 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.008..0.795 rows=4,283 loops=1)

11. 1.440 1.781 ↑ 5.3 4,196 1

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

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

13. 128.725 324.432 ↑ 2,767.0 394,697 1

Merge Join (cost=676,062.07..17,081,669.64 rows=1,092,119,778 width=16) (actual time=11.237..324.432 rows=394,697 loops=1)

  • Merge Cond: (learnerset_learner.learnersetid = descendantsets.childid)
14. 156.620 156.620 ↑ 1.0 394,697 1

Index Scan using learnerset_learner_ix_learnersetid on learnerset_learner (cost=0.42..10,815.88 rows=394,697 width=32) (actual time=0.014..156.620 rows=394,697 loops=1)

15. 27.146 39.087 ↑ 12.2 394,887 1

Materialize (cost=676,061.65..700,079.06 rows=4,803,482 width=16) (actual time=11.220..39.087 rows=394,887 loops=1)

16. 1.988 11.941 ↑ 1,121.5 4,283 1

Sort (cost=676,061.65..688,070.35 rows=4,803,482 width=16) (actual time=11.218..11.941 rows=4,283 loops=1)

  • Sort Key: descendantsets.childid
  • Sort Method: quicksort Memory: 393kB
17. 9.953 9.953 ↑ 1,121.5 4,283 1

CTE Scan on descendantsets (cost=0.00..96,069.64 rows=4,803,482 width=16) (actual time=2.398..9.953 rows=4,283 loops=1)

18. 0.102 5.719 ↓ 3.4 484 1

Hash (cost=748.43..748.43 rows=143 width=487) (actual time=5.719..5.719 rows=484 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 263kB
19. 5.617 5.617 ↓ 3.4 484 1

Seq Scan on assessmentlearnercontracts alc (cost=0.00..748.43 rows=143 width=487) (actual time=0.017..5.617 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
Planning time : 0.400 ms
Execution time : 665.773 ms