explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Ctc

Settings
# exclusive inclusive rows x rows loops node
1. 1.286 23,542.862 ↑ 12.8 2,360 1

Sort (cost=1,361,403.81..1,361,479.62 rows=30,322 width=98) (actual time=23,542.695..23,542.862 rows=2,360 loops=1)

  • Sort Key: (count(entry_log.id)) DESC
  • Sort Method: quicksort Memory: 433kB
2. 6.452 23,541.576 ↑ 12.8 2,360 1

GroupAggregate (cost=1,351,699.42..1,359,146.63 rows=30,322 width=98) (actual time=23,381.339..23,541.576 rows=2,360 loops=1)

  • Group Key: members.id
3. 19.938 23,535.124 ↑ 11.8 2,562 1

Merge Join (cost=1,351,699.42..1,358,236.97 rows=30,322 width=34) (actual time=23,381.200..23,535.124 rows=2,562 loops=1)

  • Merge Cond: (members.id = member_plans.member_id)
4. 133.701 133.701 ↑ 1.0 106,604 1

Index Scan using members_pkey on members (cost=0.29..5,815.79 rows=107,079 width=26) (actual time=0.025..133.701 rows=106,604 loops=1)

5. 1.824 23,381.485 ↑ 11.8 2,562 1

Sort (cost=1,351,699.11..1,351,774.92 rows=30,322 width=16) (actual time=23,380.924..23,381.485 rows=2,562 loops=1)

  • Sort Key: member_plans.member_id
  • Sort Method: quicksort Memory: 217kB
6. 26.289 23,379.661 ↑ 11.8 2,562 1

Hash Join (cost=80,463.41..1,349,441.93 rows=30,322 width=16) (actual time=23,158.567..23,379.661 rows=2,562 loops=1)

  • Hash Cond: (entry_log.member_plan_id = member_plans.id)
7. 21,175.699 21,175.699 ↑ 25.8 5,198 1

Seq Scan on entry_log (cost=0.00..1,268,173.83 rows=133,924 width=24) (actual time=20,972.113..21,175.699 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
8. 440.507 2,177.673 ↓ 2.1 675,374 1

Hash (cost=76,416.52..76,416.52 rows=323,751 width=16) (actual time=2,177.672..2,177.673 rows=675,374 loops=1)

  • Buckets: 1,048,576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 39,851kB
9. 867.384 1,737.166 ↓ 2.1 675,374 1

Hash Join (cost=11.01..76,416.52 rows=323,751 width=16) (actual time=0.373..1,737.166 rows=675,374 loops=1)

  • Hash Cond: (member_plans.access_type = access_types.id)
10. 869.503 869.503 ↑ 1.0 1,428,714 1

Seq Scan on member_plans (cost=0.00..72,622.01 rows=1,429,901 width=24) (actual time=0.053..869.503 rows=1,428,714 loops=1)

11. 0.032 0.279 ↑ 1.0 120 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
12. 0.247 0.247 ↑ 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.180..0.247 rows=120 loops=1)

  • Index Cond: (company_id = 2)
Planning time : 1.738 ms
Execution time : 23,546.142 ms