explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PBah

Settings
# exclusive inclusive rows x rows loops node
1. 53.817 1,568.742 ↑ 1.8 49,413 1

GroupAggregate (cost=170,349.42..172,354.60 rows=89,119 width=20) (actual time=1,509.545..1,568.742 rows=49,413 loops=1)

  • Group Key: o1_.id, u0_.id, u5_.id
2. 23.278 1,514.925 ↑ 1.7 54,007 1

Sort (cost=170,349.42..170,572.22 rows=89,119 width=12) (actual time=1,509.529..1,514.925 rows=54,007 loops=1)

  • Sort Key: o1_.id, u0_.id, u5_.id
  • Sort Method: quicksort Memory: 3897kB
3. 643.697 1,491.647 ↑ 1.7 54,007 1

Hash Join (cost=56,538.69..163,022.31 rows=89,119 width=12) (actual time=286.213..1,491.647 rows=54,007 loops=1)

  • Hash Cond: (u6_.assignment_id = u5_.id)
4. 561.873 561.873 ↑ 1.0 3,011,298 1

Seq Scan on user_course_assignment_detail u6_ (cost=0.00..70,378.76 rows=3,012,444 width=4) (actual time=0.018..561.873 rows=3,011,298 loops=1)

  • Filter: (organization_id = 1)
  • Rows Removed by Filter: 176868
5. 12.185 286.077 ↑ 1.4 54,007 1

Hash (cost=55,182.34..55,182.34 rows=78,028 width=12) (actual time=286.077..286.077 rows=54,007 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2183kB
6. 40.357 273.892 ↑ 1.4 54,007 1

Hash Join (cost=48,611.95..55,182.34 rows=78,028 width=12) (actual time=162.867..273.892 rows=54,007 loops=1)

  • Hash Cond: (o1_.user_id = u0_.id)
7. 58.777 132.282 ↓ 1.1 111,131 1

Hash Right Join (cost=4,834.95..9,028.34 rows=103,392 width=8) (actual time=61.212..132.282 rows=111,131 loops=1)

  • Hash Cond: (d2_.organization_user_id = o1_.id)
8. 12.367 12.367 ↓ 1.0 114,987 1

Seq Scan on department_user d2_ (cost=0.00..1,778.86 rows=114,986 width=8) (actual time=0.008..12.367 rows=114,987 loops=1)

9. 29.861 61.138 ↑ 1.0 103,292 1

Hash (cost=3,138.55..3,138.55 rows=103,392 width=8) (actual time=61.138..61.138 rows=103,292 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3053kB
10. 31.277 31.277 ↑ 1.0 103,292 1

Seq Scan on organization_user o1_ (cost=0.00..3,138.55 rows=103,392 width=8) (actual time=0.014..31.277 rows=103,292 loops=1)

  • Filter: (organization_id = 1)
  • Rows Removed by Filter: 71712
11. 8.867 101.253 ↑ 1.7 48,682 1

Hash (cost=42,351.90..42,351.90 rows=81,928 width=12) (actual time=101.253..101.253 rows=48,682 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2073kB
12. 25.537 92.386 ↑ 1.7 48,682 1

Hash Join (cost=9,961.22..42,351.90 rows=81,928 width=12) (actual time=47.595..92.386 rows=48,682 loops=1)

  • Hash Cond: (u5_.user_id = u0_.id)
13. 21.913 27.284 ↑ 1.7 48,682 1

Bitmap Heap Scan on user_course_assignment u5_ (cost=1,649.62..31,846.79 rows=81,928 width=8) (actual time=6.988..27.284 rows=48,682 loops=1)

  • Recheck Cond: (course_id = 3)
  • Filter: is_current
  • Rows Removed by Filter: 38973
  • Heap Blocks: exact=9772
14. 5.371 5.371 ↑ 1.0 87,656 1

Bitmap Index Scan on idx_1738b112591cc992 (cost=0.00..1,629.13 rows=88,094 width=0) (actual time=5.371..5.371 rows=87,656 loops=1)

  • Index Cond: (course_id = 3)
15. 18.813 39.565 ↑ 1.0 108,560 1

Hash (cost=6,529.60..6,529.60 rows=108,560 width=4) (actual time=39.565..39.565 rows=108,560 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2942kB
16. 20.752 20.752 ↑ 1.0 108,560 1

Seq Scan on "user" u0_ (cost=0.00..6,529.60 rows=108,560 width=4) (actual time=0.034..20.752 rows=108,560 loops=1)

Planning time : 1.963 ms
Execution time : 1,571.253 ms