explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S5Yb : that query executes in at least an order of magnitude slower than i was expecting, perhaps even two. it's pretty simple and the data set it not huge. anyone able to see what the issue is from that explain analyze

Settings
# exclusive inclusive rows x rows loops node
1. 49.156 316.187 ↓ 2.3 12,574 1

Hash Join (cost=17.23..24,849.62 rows=5,370 width=218) (actual time=0.270..316.187 rows=12,574 loops=1)

  • Hash Cond: (member_plans.access_type = access_types.id)
2. 4.162 266.938 ↓ 2.3 12,574 1

Nested Loop (cost=7.30..24,288.41 rows=5,370 width=131) (actual time=0.131..266.938 rows=12,574 loops=1)

3. 2.262 225.054 ↓ 2.3 12,574 1

Nested Loop (cost=7.01..22,182.03 rows=5,370 width=81) (actual time=0.124..225.054 rows=12,574 loops=1)

4. 0.048 0.096 ↑ 1.0 9 1

Hash Join (cost=6.59..13.08 rows=9 width=28) (actual time=0.050..0.096 rows=9 loops=1)

  • Hash Cond: (gyms.id = gyms_1.id)
5. 0.028 0.028 ↑ 1.0 38 1

Seq Scan on gyms (cost=0.00..6.38 rows=38 width=20) (actual time=0.003..0.028 rows=38 loops=1)

6. 0.002 0.020 ↑ 1.0 9 1

Hash (cost=6.47..6.47 rows=9 width=8) (actual time=0.020..0.020 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.018 0.018 ↑ 1.0 9 1

Seq Scan on gyms gyms_1 (cost=0.00..6.47 rows=9 width=8) (actual time=0.004..0.018 rows=9 loops=1)

  • Filter: (company_id = 2)
  • Rows Removed by Filter: 29
8. 222.696 222.696 ↓ 1.6 1,397 9

Index Scan using member_plans_home_gym on member_plans (cost=0.42..2,454.50 rows=872 width=77) (actual time=0.033..24.744 rows=1,397 loops=9)

  • Index Cond: (home_gym = gyms.id)
  • Filter: ((renewal_of IS NULL) AND (membership_end >= CURRENT_DATE))
  • Rows Removed by Filter: 42,132
9. 37.722 37.722 ↑ 1.0 1 12,574

Index Scan using members_pkey on members (cost=0.29..0.39 rows=1 width=66) (actual time=0.003..0.003 rows=1 loops=12,574)

  • Index Cond: (id = member_plans.member_id)
10. 0.038 0.093 ↑ 1.2 265 1

Hash (cost=6.08..6.08 rows=308 width=8) (actual time=0.093..0.093 rows=265 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
11. 0.055 0.055 ↑ 1.2 265 1

Seq Scan on access_types (cost=0.00..6.08 rows=308 width=8) (actual time=0.017..0.055 rows=265 loops=1)

Planning time : 1.786 ms
Execution time : 317.305 ms