explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RTif

Settings
# exclusive inclusive rows x rows loops node
1. 49.967 1,519.866 ↑ 1.8 49,413 1

GroupAggregate (cost=170,348.34..172,353.48 rows=89,117 width=20) (actual time=1,464.211..1,519.866 rows=49,413 loops=1)

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

Sort (cost=170,348.34..170,571.14 rows=89,117 width=12) (actual time=1,464.196..1,469.899 rows=54,007 loops=1)

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

Hash Join (cost=56,538.69..163,021.40 rows=89,117 width=12) (actual time=332.123..1,449.376 rows=54,007 loops=1)

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

Seq Scan on user_course_assignment_detail u6_ (cost=0.00..70,378.08 rows=3,012,392 width=4) (actual time=0.011..519.860 rows=3,011,298 loops=1)

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

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

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

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

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

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

  • Hash Cond: (d2_.organization_user_id = o1_.id)
8. 12.464 12.464 ↓ 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.024..12.464 rows=114,987 loops=1)

9. 30.072 71.537 ↑ 1.0 103,292 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3053kB
10. 41.465 41.465 ↑ 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..41.465 rows=103,292 loops=1)

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

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

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

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

  • Hash Cond: (u5_.user_id = u0_.id)
13. 21.552 31.361 ↑ 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=11.838..31.361 rows=48,682 loops=1)

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

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

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2942kB
16. 30.977 30.977 ↑ 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..30.977 rows=108,560 loops=1)

Planning time : 3.774 ms
Execution time : 1,522.172 ms