explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H6eG

Settings
# exclusive inclusive rows x rows loops node
1. 0.178 471.134 ↑ 52.9 329 1

Finalize GroupAggregate (cost=53,633.13..57,136.25 rows=17,388 width=86) (actual time=426.538..471.134 rows=329 loops=1)

  • Group Key: plans.name, (CASE WHEN (plans.duration_count > 1) THEN ((((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) || 's'::text) ELSE (((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) END), access_types.name, member_plans.original_plan
2. 47.962 470.956 ↑ 21.1 484 1

Gather Merge (cost=53,633.13..55,704.30 rows=10,228 width=86) (actual time=426.519..470.956 rows=484 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 2.078 422.994 ↑ 42.3 242 2 / 2

Partial GroupAggregate (cost=52,633.12..53,553.64 rows=10,228 width=86) (actual time=420.439..422.994 rows=242 loops=2)

  • Group Key: plans.name, (CASE WHEN (plans.duration_count > 1) THEN ((((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) || 's'::text) ELSE (((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) END), access_types.name, member_plans.original_plan
4. 9.250 420.916 ↑ 1.3 8,122 2 / 2

Sort (cost=52,633.12..52,658.69 rows=10,228 width=86) (actual time=420.431..420.916 rows=8,122 loops=2)

  • Sort Key: plans.name, (CASE WHEN (plans.duration_count > 1) THEN ((((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) || 's'::text) ELSE (((((((plans.autorenew)::text || ' x '::text) || (plans.price)::text) || ' / '::text) || (plans.duration_count)::text) || ' '::text) || (plans.duration)::text) END), access_types.name, member_plans.original_plan
  • Sort Method: quicksort Memory: 1,735kB
5. 6.448 411.666 ↑ 1.3 8,122 2 / 2

Hash Join (cost=49.16..51,951.92 rows=10,228 width=86) (actual time=0.594..411.666 rows=8,122 loops=2)

  • Hash Cond: (plans.default_access_type = access_types.id)
6. 3.584 405.008 ↑ 1.3 8,122 2 / 2

Hash Join (cost=31.23..51,242.12 rows=10,228 width=69) (actual time=0.353..405.008 rows=8,122 loops=2)

  • Hash Cond: (member_plans.home_gym = gyms.id)
7. 4.596 401.285 ↑ 3.6 20,320 2 / 2

Merge Join (cost=0.71..51,012.48 rows=72,961 width=77) (actual time=0.204..401.285 rows=20,320 loops=2)

  • Merge Cond: (member_plans.original_plan = plans.id)
8. 395.697 395.697 ↑ 3.6 20,320 2 / 2

Parallel Index Scan using member_plans_original_plan on member_plans (cost=0.43..103,010.69 rows=72,961 width=24) (actual time=0.166..395.697 rows=20,320 loops=2)

  • Filter: ((renewal_of IS NULL) AND (membership_start <= CURRENT_DATE) AND ((membership_end >= CURRENT_DATE) OR autorenew_forever))
  • Rows Removed by Filter: 386,548
9. 0.992 0.992 ↑ 1.0 2,060 2 / 2

Index Scan using plans_pkey on plans (cost=0.28..81.86 rows=2,063 width=61) (actual time=0.029..0.992 rows=2,060 loops=2)

10. 0.007 0.139 ↑ 1.0 15 2 / 2

Hash (cost=30.34..30.34 rows=15 width=8) (actual time=0.138..0.139 rows=15 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.132 0.132 ↑ 1.0 15 2 / 2

Seq Scan on gyms (cost=0.00..30.34 rows=15 width=8) (actual time=0.026..0.132 rows=15 loops=2)

  • Filter: (company_id = 2)
  • Rows Removed by Filter: 92
12. 0.096 0.210 ↓ 1.1 565 2 / 2

Hash (cost=11.30..11.30 rows=530 width=18) (actual time=0.210..0.210 rows=565 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
13. 0.114 0.114 ↓ 1.1 565 2 / 2

Seq Scan on access_types (cost=0.00..11.30 rows=530 width=18) (actual time=0.016..0.114 rows=565 loops=2)

Planning time : 1.102 ms
Execution time : 471.266 ms