explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ur8P

Settings
# exclusive inclusive rows x rows loops node
1. 1.128 17,477.121 ↓ 1.0 2,360 1

Sort (cost=1,343,086.01..1,343,091.83 rows=2,329 width=98) (actual time=17,476.977..17,477.121 rows=2,360 loops=1)

  • Sort Key: (count(entry_log.id)) DESC
  • Sort Method: quicksort Memory: 433kB
2. 4.732 17,475.993 ↓ 1.0 2,360 1

GroupAggregate (cost=1,342,880.06..1,342,955.75 rows=2,329 width=98) (actual time=17,471.053..17,475.993 rows=2,360 loops=1)

  • Group Key: members.id
3. 1.702 17,471.261 ↓ 1.1 2,562 1

Sort (cost=1,342,880.06..1,342,885.88 rows=2,329 width=34) (actual time=17,470.923..17,471.261 rows=2,562 loops=1)

  • Sort Key: members.id
  • Sort Method: quicksort Memory: 301kB
4. 2.786 17,469.559 ↓ 1.1 2,562 1

Nested Loop (cost=73,234.68..1,342,749.80 rows=2,329 width=34) (actual time=17,265.420..17,469.559 rows=2,562 loops=1)

5. 4.347 17,446.277 ↓ 1.1 2,562 1

Hash Join (cost=73,234.39..1,341,933.66 rows=2,329 width=16) (actual time=17,265.301..17,446.277 rows=2,562 loops=1)

  • Hash Cond: (entry_log.member_plan_id = member_plans.id)
6. 16,421.346 16,421.346 ↑ 25.8 5,198 1

Seq Scan on entry_log (cost=0.00..1,268,173.83 rows=133,924 width=24) (actual time=16,244.214..16,421.346 rows=5,198 loops=1)

  • Filter: (((timezone('Australia/Melbourne'::text, entry_time))::date >= firstdom(monthdelta(0))) AND ((timezone('Australia/Melbourne'::text, entry_time))::date <= lastdom(monthdelta(0))))
  • Rows Removed by Filter: 1,162,834
7. 18.466 1,020.584 ↓ 1.7 41,495 1

Hash (cost=72,923.59..72,923.59 rows=24,864 width=16) (actual time=1,020.584..1,020.584 rows=41,495 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2,458kB
8. 29.209 1,002.118 ↓ 1.7 41,495 1

Hash Join (cost=11.01..72,923.59 rows=24,864 width=16) (actual time=0.218..1,002.118 rows=41,495 loops=1)

  • Hash Cond: (member_plans.access_type = access_types.id)
9. 972.744 972.744 ↓ 1.0 112,583 1

Seq Scan on member_plans (cost=0.00..72,622.01 rows=109,816 width=24) (actual time=0.039..972.744 rows=112,583 loops=1)

  • Filter: (renewal_of IS NULL)
  • Rows Removed by Filter: 1,316,131
10. 0.025 0.165 ↑ 1.0 120 1

Hash (cost=9.51..9.51 rows=120 width=8) (actual time=0.165..0.165 rows=120 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
11. 0.140 0.140 ↑ 1.0 120 1

Index Scan using access_type_company_id_name on access_types (cost=0.28..9.51 rows=120 width=8) (actual time=0.073..0.140 rows=120 loops=1)

  • Index Cond: (company_id = 2)
12. 20.496 20.496 ↑ 1.0 1 2,562

Index Scan using members_pkey on members (cost=0.29..0.35 rows=1 width=26) (actual time=0.008..0.008 rows=1 loops=2,562)

  • Index Cond: (id = member_plans.member_id)
Planning time : 2.218 ms
Execution time : 17,477.657 ms