explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b2nq

Settings
# exclusive inclusive rows x rows loops node
1. 2.831 41,961.561 ↑ 45.7 15,919 1

Unique (cost=1,226,056.37..1,235,152.24 rows=727,669 width=62) (actual time=41,958.024..41,961.561 rows=15,919 loops=1)

2. 52.078 41,958.730 ↑ 45.7 15,919 1

Sort (cost=1,226,056.37..1,227,875.55 rows=727,669 width=62) (actual time=41,958.022..41,958.730 rows=15,919 loops=1)

  • Sort Key: profile.external_id, learning_plan.external_id, (count(*) FILTER (WHERE (assignment.completed_at IS NOT NULL))), (count(*))
  • Sort Method: quicksort Memory: 2623kB
3. 467.350 41,906.652 ↑ 45.7 15,919 1

Finalize GroupAggregate (cost=1,068,769.57..1,139,182.31 rows=727,669 width=62) (actual time=35,864.235..41,906.652 rows=15,919 loops=1)

  • Group Key: learning_plan.external_id, profile.external_id
  • Filter: (count(*) FILTER (WHERE (assignment.completed_at IS NOT NULL)) > 0)
  • Rows Removed by Filter: 537065
4. 821.387 41,439.302 ↓ 2.6 1,092,168 1

Gather Merge (cost=1,068,769.57..1,126,555.11 rows=428,041 width=62) (actual time=35,863.818..41,439.302 rows=1,092,168 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 1,003.471 40,617.915 ↓ 1.3 546,084 2 / 2

Partial GroupAggregate (cost=1,067,769.56..1,077,400.49 rows=428,041 width=62) (actual time=35,377.512..40,617.915 rows=546,084 loops=2)

  • Group Key: learning_plan.external_id, profile.external_id
6. 20,404.707 39,614.444 ↓ 6.2 2,666,636 2 / 2

Sort (cost=1,067,769.56..1,068,839.67 rows=428,041 width=54) (actual time=35,377.494..39,614.444 rows=2,666,636 loops=2)

  • Sort Key: learning_plan.external_id, profile.external_id
  • Sort Method: external merge Disk: 145072kB
7. 2,436.105 19,209.737 ↓ 6.2 2,666,636 2 / 2

Hash Join (cost=534,192.09..1,019,160.87 rows=428,041 width=54) (actual time=8,914.102..19,209.737 rows=2,666,636 loops=2)

  • Hash Cond: (assignment.profile_id = profile.id)
8. 5,978.192 16,228.430 ↓ 6.2 2,666,636 2 / 2

Hash Join (cost=486,213.10..954,764.28 rows=428,041 width=34) (actual time=8,366.433..16,228.430 rows=2,666,636 loops=2)

  • Hash Cond: (assignment.id = assignment_lesson.assignment_id)
9. 1,886.363 1,886.363 ↑ 1.1 6,187,642 2 / 2

Parallel Seq Scan on assignment (cost=0.00..322,926.89 rows=7,042,589 width=40) (actual time=0.035..1,886.363 rows=6,187,642 loops=2)

10. 1,911.531 8,363.875 ↓ 7.3 5,333,273 2 / 2

Hash (cost=472,142.24..472,142.24 rows=727,669 width=26) (actual time=8,363.875..8,363.875 rows=5,333,273 loops=2)

  • Buckets: 524288 (originally 524288) Batches: 16 (originally 4) Memory Usage: 23351kB
11. 3,285.232 6,452.344 ↓ 7.3 5,333,273 2 / 2

Hash Join (cost=28.32..472,142.24 rows=727,669 width=26) (actual time=0.138..6,452.344 rows=5,333,273 loops=2)

  • Hash Cond: (assignment_lesson.lesson_id = lesson.id)
12. 3,167.016 3,167.016 ↓ 1.0 12,632,654 2 / 2

Seq Scan on assignment_lesson (cost=0.00..419,054.71 rows=12,208,671 width=32) (actual time=0.022..3,167.016 rows=12,632,654 loops=2)

13. 0.016 0.096 ↑ 1.7 43 2 / 2

Hash (cost=27.42..27.42 rows=72 width=26) (actual time=0.096..0.096 rows=43 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.013 0.080 ↑ 1.7 43 2 / 2

Nested Loop (cost=0.28..27.42 rows=72 width=26) (actual time=0.051..0.080 rows=43 loops=2)

15. 0.027 0.027 ↑ 1.2 4 2 / 2

Seq Scan on learning_plan (cost=0.00..3.37 rows=5 width=26) (actual time=0.024..0.027 rows=4 loops=2)

  • Filter: (external_id = ANY ('{EDP1_P_2019,EDP1_S_2019,EDP2_P_2019,EDP_2_S_2019,NDP_2019}'::text[]))
  • Rows Removed by Filter: 57
16. 0.040 0.040 ↑ 1.7 11 8 / 2

Index Scan using lesson_learning_plan_id_idx on lesson (cost=0.28..4.62 rows=19 width=32) (actual time=0.008..0.010 rows=11 loops=8)

  • Index Cond: (learning_plan_id = learning_plan.id)
17. 337.476 545.202 ↓ 1.0 881,033 2 / 2

Hash (cost=28,362.77..28,362.77 rows=880,977 width=52) (actual time=545.202..545.202 rows=881,033 loops=2)

  • Buckets: 524288 Batches: 4 Memory Usage: 22388kB
18. 207.726 207.726 ↓ 1.0 881,033 2 / 2

Seq Scan on profile (cost=0.00..28,362.77 rows=880,977 width=52) (actual time=0.013..207.726 rows=881,033 loops=2)