explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zGUv

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 543.760 ↑ 1.0 21 1

Limit (cost=3,825.09..3,837.47 rows=22 width=78) (actual time=539.989..543.760 rows=21 loops=1)

2. 2.958 543.755 ↑ 1.0 21 1

GroupAggregate (cost=3,825.09..3,837.47 rows=22 width=78) (actual time=539.985..543.755 rows=21 loops=1)

  • Group Key: course.id, report.courseid
3. 3.649 540.797 ↓ 649.8 14,296 1

Sort (cost=3,825.09..3,825.15 rows=22 width=62) (actual time=539.867..540.797 rows=14,296 loops=1)

  • Sort Key: course.id, report.courseid
  • Sort Method: quicksort Memory: 2291kB
4. 1.638 537.148 ↓ 649.8 14,296 1

Hash Left Join (cost=3,711.34..3,824.60 rows=22 width=62) (actual time=535.153..537.148 rows=14,296 loops=1)

  • Hash Cond: (course.id = report.courseid)
5. 0.272 2.670 ↑ 1.0 21 1

Hash Semi Join (cost=112.91..226.07 rows=22 width=42) (actual time=2.268..2.670 rows=21 loops=1)

  • Hash Cond: (course.id = c0_.id)
6. 0.289 0.289 ↓ 1.0 2,214 1

Seq Scan on course (cost=0.00..107.11 rows=2,211 width=42) (actual time=0.037..0.289 rows=2,214 loops=1)

7. 0.057 2.109 ↑ 1.0 21 1

Hash (cost=112.64..112.64 rows=22 width=4) (actual time=2.109..2.109 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 2.052 2.052 ↑ 1.0 21 1

Seq Scan on course c0_ (cost=0.00..112.64 rows=22 width=4) (actual time=0.089..2.052 rows=21 loops=1)

  • Filter: ((NOT draft) AND (deleted_at IS NULL) AND (type = 'assessment'::text))
  • Rows Removed by Filter: 2193
9. 1.951 532.840 ↓ 72.9 14,289 1

Hash (cost=3,595.98..3,595.98 rows=196 width=20) (actual time=532.840..532.840 rows=14,289 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 854kB
10. 1.153 530.889 ↓ 72.9 14,289 1

Subquery Scan on report (cost=3,589.12..3,595.98 rows=196 width=20) (actual time=523.610..530.889 rows=14,289 loops=1)

11. 4.466 529.736 ↓ 72.9 14,289 1

GroupAggregate (cost=3,589.12..3,594.02 rows=196 width=44) (actual time=523.608..529.736 rows=14,289 loops=1)

  • Group Key: c0__1.id, c3_.id, o2_.id
12. 17.127 525.270 ↓ 72.9 14,289 1

Sort (cost=3,589.12..3,589.61 rows=196 width=20) (actual time=523.600..525.270 rows=14,289 loops=1)

  • Sort Key: c0__1.id, c3_.id, o2_.id
  • Sort Method: quicksort Memory: 1501kB
13. 13.505 508.143 ↓ 72.9 14,289 1

Nested Loop Semi Join (cost=116.75..3,581.65 rows=196 width=20) (actual time=2.081..508.143 rows=14,289 loops=1)

  • Join Filter: (q1_.organization_user_id = d7_.organization_user_id)
14. 2.337 180.192 ↓ 72.9 14,293 1

Nested Loop (cost=113.93..1,368.94 rows=196 width=24) (actual time=1.828..180.192 rows=14,293 loops=1)

15. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on organization o4_ (cost=0.00..1.06 rows=1 width=4) (actual time=0.018..0.020 rows=1 loops=1)

  • Filter: ((deleted_at IS NULL) AND (id = 1))
  • Rows Removed by Filter: 4
16. 1.782 177.835 ↓ 72.9 14,293 1

Nested Loop (cost=113.93..1,365.92 rows=196 width=28) (actual time=1.805..177.835 rows=14,293 loops=1)

17. 6.836 118.881 ↓ 72.6 14,293 1

Nested Loop (cost=113.64..1,254.75 rows=197 width=32) (actual time=1.782..118.881 rows=14,293 loops=1)

18. 9.294 54.873 ↓ 46.0 14,293 1

Nested Loop (cost=113.22..1,075.06 rows=311 width=20) (actual time=1.754..54.873 rows=14,293 loops=1)

19. 10.455 16.993 ↓ 35.2 14,293 1

Hash Join (cost=112.94..942.92 rows=406 width=20) (actual time=1.707..16.993 rows=14,293 loops=1)

  • Hash Cond: (q1_.course_id = c0__1.id)
20. 4.882 4.882 ↓ 1.0 37,378 1

Seq Scan on quiz_organization_user_report q1_ (cost=0.00..685.76 rows=37,376 width=20) (actual time=0.011..4.882 rows=37,378 loops=1)

21. 0.043 1.656 ↓ 1.0 25 1

Hash (cost=112.64..112.64 rows=24 width=4) (actual time=1.656..1.656 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 1.613 1.613 ↓ 1.0 25 1

Seq Scan on course c0__1 (cost=0.00..112.64 rows=24 width=4) (actual time=0.068..1.613 rows=25 loops=1)

  • Filter: ((deleted_at IS NULL) AND (type = 'assessment'::text))
  • Rows Removed by Filter: 2189
23. 28.586 28.586 ↑ 1.0 1 14,293

Index Scan using course_lecture_pkey on course_lecture c3_ (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=14,293)

  • Index Cond: (id = q1_.lecture_id)
  • Filter: (deleted_at IS NULL)
24. 57.172 57.172 ↑ 1.0 1 14,293

Index Scan using organization_user_pkey on organization_user o2_ (cost=0.42..0.57 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=14,293)

  • Index Cond: (id = q1_.organization_user_id)
  • Filter: ((deleted_at IS NULL) AND (organization_id = 1))
25. 57.172 57.172 ↑ 1.0 1 14,293

Index Scan using user_pkey on "user" u5_ (cost=0.29..0.55 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=14,293)

  • Index Cond: (id = o2_.user_id)
  • Filter: (deleted_at IS NULL)
26. 14.309 314.446 ↑ 1.0 1 14,293

Nested Loop Left Join (cost=2.82..11.28 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=14,293)

27. 14.313 242.981 ↑ 1.0 1 14,293

Nested Loop Left Join (cost=1.97..6.38 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=14,293)

28. 0.008 157.223 ↑ 1.0 1 14,293

Nested Loop Left Join (cost=1.26..1.63 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=14,293)

29. 14.301 128.637 ↑ 1.0 1 14,293

Nested Loop Left Join (cost=0.99..1.15 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=14,293)

30. 14.301 85.758 ↑ 1.0 1 14,293

Nested Loop (cost=0.71..0.85 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=14,293)

31. 42.879 42.879 ↑ 1.0 1 14,293

Index Scan using idx_2f89b11c6abc5bd6 on department_user d7_ (cost=0.29..0.34 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=14,293)

  • Index Cond: (organization_user_id = o2_.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
32. 28.578 28.578 ↑ 1.0 1 14,289

Index Scan using organization_user_pkey on organization_user o6_ (cost=0.42..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=14,289)

  • Index Cond: (id = d7_.organization_user_id)
  • Filter: ((deleted_at IS NULL) AND (organization_id = 1))
33. 28.578 28.578 ↑ 1.0 1 14,289

Index Scan using department_pkey on department d8_ (cost=0.28..0.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=14,289)

  • Index Cond: (d7_.department_id = id)
  • Filter: (deleted_at IS NULL)
34. 28.578 28.578 ↑ 1.0 1 14,289

Index Scan using department_pkey on department d9_ (cost=0.28..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14,289)

  • Index Cond: (id = d8_.id)
  • Filter: ((deleted_at IS NULL) AND (level = 3))
35. 28.578 71.445 ↑ 1.0 1 14,289

Bitmap Heap Scan on department d10_ (cost=0.71..4.74 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=14,289)

  • Recheck Cond: ((id = d8_.id) OR (id = d9_.parent_id))
  • Filter: ((deleted_at IS NULL) AND (level = 2))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=18681
36. 0.000 42.867 ↓ 0.0 0 14,289

BitmapOr (cost=0.71..0.71 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=14,289)

37. 14.289 14.289 ↑ 1.0 1 14,289

Bitmap Index Scan on department_pkey (cost=0.00..0.33 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=14,289)

  • Index Cond: (id = d8_.id)
38. 28.578 28.578 ↑ 1.0 1 14,289

Bitmap Index Scan on department_pkey (cost=0.00..0.34 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=14,289)

  • Index Cond: (id = d9_.parent_id)
39. 14.289 57.156 ↑ 1.0 1 14,289

Bitmap Heap Scan on department d11_ (cost=0.85..4.89 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=14,289)

  • Recheck Cond: ((id = d8_.id) OR (id = d10_.parent_id))
  • Filter: ((deleted_at IS NULL) AND (level = 1))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=21221
40. 14.289 42.867 ↓ 0.0 0 14,289

BitmapOr (cost=0.85..0.85 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=14,289)

41. 14.289 14.289 ↑ 1.0 1 14,289

Bitmap Index Scan on department_pkey (cost=0.00..0.33 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=14,289)

  • Index Cond: (id = d8_.id)
42. 14.289 14.289 ↑ 1.0 1 14,289

Bitmap Index Scan on department_pkey (cost=0.00..0.51 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=14,289)

  • Index Cond: (id = d10_.parent_id)
Planning time : 5.850 ms
Execution time : 544.934 ms