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. 0.000 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. 2,006.942 81,235.830 ↓ 1.3 546,084 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. 40,809.414 79,228.888 ↓ 6.2 2,666,636 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. 4,872.210 38,419.474 ↓ 6.2 2,666,636 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. 11,956.384 32,456.860 ↓ 6.2 2,666,636 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. 3,772.726 3,772.726 ↑ 1.1 6,187,642 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. 3,823.062 16,727.750 ↓ 7.3 5,333,273 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. 6,570.464 12,904.688 ↓ 7.3 5,333,273 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. 6,334.032 6,334.032 ↓ 1.0 12,632,654 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.032 0.192 ↑ 1.7 43 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.026 0.160 ↑ 1.7 43 2

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

15. 0.054 0.054 ↑ 1.2 4 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.080 0.080 ↑ 1.7 11 8

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. 674.952 1,090.404 ↓ 1.0 881,033 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. 415.452 415.452 ↓ 1.0 881,033 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)