explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J5Ag

Settings
# exclusive inclusive rows x rows loops node
1. 305.193 534.825 ↓ 37.1 18,147 1

Hash Left Join (cost=6,576.94..34,796.33 rows=489 width=567) (actual time=65.012..534.825 rows=18,147 loops=1)

  • Hash Cond: (member_plans.bonus_access = bonus_access_types.id)
2. 7.544 229.524 ↓ 37.1 18,147 1

Hash Join (cost=6,567.53..34,328.42 rows=489 width=179) (actual time=64.814..229.524 rows=18,147 loops=1)

  • Hash Cond: (access_types.company_id = companies.id)
3. 8.078 221.948 ↓ 37.1 18,147 1

Hash Join (cost=6,566.17..34,325.51 rows=489 width=187) (actual time=64.764..221.948 rows=18,147 loops=1)

  • Hash Cond: (member_plans.access_type = access_types.id)
4. 20.141 213.753 ↓ 37.1 18,147 1

Hash Join (cost=6,556.76..34,314.79 rows=489 width=177) (actual time=64.628..213.753 rows=18,147 loops=1)

  • Hash Cond: (member_plans.id = access_tags.member_plan)
5. 9.699 176.495 ↓ 2.5 28,674 1

Hash Left Join (cost=5,208.85..32,786.75 rows=11,683 width=148) (actual time=47.318..176.495 rows=28,674 loops=1)

  • Hash Cond: (member_plans.home_gym = gyms.id)
6. 17.075 166.737 ↓ 2.5 28,674 1

Hash Left Join (cost=5,202.00..32,745.81 rows=11,683 width=148) (actual time=47.233..166.737 rows=28,674 loops=1)

  • Hash Cond: (members.id = member_payment_details.member_id)
7. 21.314 140.341 ↓ 2.4 28,409 1

Hash Join (cost=3,967.19..31,317.13 rows=11,683 width=148) (actual time=37.709..140.341 rows=28,409 loops=1)

  • Hash Cond: (member_plans.member_id = members.id)
8. 15.498 87.182 ↓ 2.4 28,409 1

Nested Loop (cost=1,409.89..28,729.16 rows=11,683 width=98) (actual time=5.821..87.182 rows=28,409 loops=1)

9. 0.098 0.098 ↑ 1.0 9 1

Index Scan using gyms_pkey on gyms gyms_1 (cost=0.14..9.43 rows=9 width=8) (actual time=0.023..0.098 rows=9 loops=1)

  • Filter: (company_id = 2)
  • Rows Removed by Filter: 29
10. 22.473 71.586 ↓ 1.7 3,157 9

Bitmap Heap Scan on member_plans (cost=1,409.75..3,172.81 rows=1,827 width=98) (actual time=5.580..7.954 rows=3,157 loops=9)

  • Recheck Cond: ((home_gym = gyms_1.id) AND (renewal_of IS NULL))
  • Heap Blocks: exact=7300
11. 1.917 49.113 ↓ 0.0 0 9

BitmapAnd (cost=1,409.75..1,409.75 rows=1,827 width=0) (actual time=5.457..5.457 rows=0 loops=9)

12. 23.643 23.643 ↓ 1.6 43,873 9

Bitmap Index Scan on member_plans_home_gym (cost=0.00..453.22 rows=26,672 width=0) (actual time=2.627..2.627 rows=43,873 loops=9)

  • Index Cond: (home_gym = gyms_1.id)
13. 23.553 23.553 ↓ 1.0 49,395 9

Bitmap Index Scan on member_plans_renewal_of (cost=0.00..943.49 rows=49,329 width=0) (actual time=2.617..2.617 rows=49,395 loops=9)

  • Index Cond: (renewal_of IS NULL)
14. 14.483 31.845 ↓ 1.0 43,887 1

Hash (cost=2,013.80..2,013.80 rows=43,480 width=74) (actual time=31.845..31.845 rows=43,887 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4141kB
15. 17.362 17.362 ↓ 1.0 43,887 1

Seq Scan on members (cost=0.00..2,013.80 rows=43,480 width=74) (actual time=0.014..17.362 rows=43,887 loops=1)

16. 3.867 9.321 ↑ 1.0 22,483 1

Hash (cost=944.32..944.32 rows=23,239 width=8) (actual time=9.321..9.321 rows=22,483 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1135kB
17. 5.454 5.454 ↑ 1.0 22,483 1

Seq Scan on member_payment_details (cost=0.00..944.32 rows=23,239 width=8) (actual time=0.011..5.454 rows=22,483 loops=1)

  • Filter: (member_default IS TRUE)
  • Rows Removed by Filter: 3488
18. 0.018 0.059 ↑ 1.0 38 1

Hash (cost=6.38..6.38 rows=38 width=24) (actual time=0.059..0.059 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.041 0.041 ↑ 1.0 38 1

Seq Scan on gyms (cost=0.00..6.38 rows=38 width=24) (actual time=0.011..0.041 rows=38 loops=1)

20. 8.200 17.117 ↓ 1.0 30,173 1

Hash (cost=970.89..970.89 rows=30,161 width=45) (actual time=17.117..17.117 rows=30,173 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2325kB
21. 8.917 8.917 ↓ 1.0 30,173 1

Seq Scan on access_tags (cost=0.00..970.89 rows=30,161 width=45) (actual time=0.008..8.917 rows=30,173 loops=1)

  • Filter: (cancelled IS NULL)
  • Rows Removed by Filter: 12507
22. 0.067 0.117 ↓ 1.0 290 1

Hash (cost=5.85..5.85 rows=285 width=26) (actual time=0.117..0.117 rows=290 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
23. 0.050 0.050 ↓ 1.0 290 1

Seq Scan on access_types (cost=0.00..5.85 rows=285 width=26) (actual time=0.005..0.050 rows=290 loops=1)

24. 0.008 0.032 ↓ 1.3 21 1

Hash (cost=1.16..1.16 rows=16 width=8) (actual time=0.032..0.032 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.024 0.024 ↓ 1.3 21 1

Seq Scan on companies (cost=0.00..1.16 rows=16 width=8) (actual time=0.008..0.024 rows=21 loops=1)

26. 0.059 0.108 ↓ 1.0 290 1

Hash (cost=5.85..5.85 rows=285 width=18) (actual time=0.108..0.108 rows=290 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
27. 0.049 0.049 ↓ 1.0 290 1

Seq Scan on access_types bonus_access_types (cost=0.00..5.85 rows=285 width=18) (actual time=0.006..0.049 rows=290 loops=1)

Planning time : 3.875 ms
Execution time : 537.142 ms