explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZTE4

Settings
# exclusive inclusive rows x rows loops node
1. 139.748 101,041.718 ↑ 52.3 362,532 1

Unique (cost=22,718,003.03..23,286,397.55 rows=18,946,484 width=997) (actual time=100,878.488..101,041.718 rows=362,532 loops=1)

  • Buffers: shared hit=1,642, local read=352,046
2. 491.663 100,901.970 ↑ 52.3 362,532 1

Sort (cost=22,718,003.03..22,765,369.24 rows=18,946,484 width=997) (actual time=100,878.486..100,901.970 rows=362,532 loops=1)

  • Sort Key: sd.clndr_month_start_dt, sd.clndr_month_end_dt, sd.coach_expert_id, em.agent_id, em.expert_first_name, em.expert_last_name, sd.client, sd.subclient, sd.metric, (sum(sd.metric_numerator)), (sum(sd.metric_denominator))
  • Sort Method: quicksort Memory: 82,347kB
  • Buffers: shared hit=1,642, local read=352,046
3. 9,843.508 100,410.307 ↑ 52.3 362,532 1

GroupAggregate (cost=11,191,393.65..12,138,717.85 rows=18,946,484 width=997) (actual time=88,964.003..100,410.307 rows=362,532 loops=1)

  • Group Key: sd.clndr_month_start_dt, sd.clndr_month_end_dt, sd.coach_expert_id, em.agent_id, em.expert_first_name, em.expert_last_name, sd.client, sd.subclient, sd.metric
  • Buffers: shared hit=1,642, local read=352,046
4. 81,319.025 90,566.799 ↓ 1.0 19,038,862 1

Sort (cost=11,191,393.65..11,238,759.86 rows=18,946,484 width=973) (actual time=88,963.967..90,566.799 rows=19,038,862 loops=1)

  • Sort Key: sd.clndr_month_start_dt, sd.clndr_month_end_dt, sd.coach_expert_id, em.agent_id, em.expert_first_name, em.expert_last_name, sd.client, sd.subclient, sd.metric
  • Sort Method: quicksort Memory: 3,487,131kB
  • Buffers: shared hit=1,642, local read=352,046
5. 4,876.674 9,247.774 ↓ 1.0 19,038,862 1

Hash Join (cost=3,405.89..806,382.96 rows=18,946,484 width=973) (actual time=31.433..9,247.774 rows=19,038,862 loops=1)

  • Hash Cond: ((sd.coach_expert_id)::text = (em.expert_id)::text)
  • Buffers: shared hit=1,642, local read=352,046
6. 4,340.145 4,340.145 ↓ 1.0 19,041,692 1

Seq Scan on source_data sd (cost=0.00..542,462.92 rows=18,946,484 width=944) (actual time=0.014..4,340.145 rows=19,041,692 loops=1)

  • Filter: (coach_expert_id IS NOT NULL)
  • Buffers: local read=352,046
7. 17.949 30.955 ↑ 1.0 78,395 1

Hash (cost=2,425.95..2,425.95 rows=78,395 width=36) (actual time=30.955..30.955 rows=78,395 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,804kB
  • Buffers: shared hit=1,642
8. 13.006 13.006 ↑ 1.0 78,395 1

Seq Scan on expert_master em (cost=0.00..2,425.95 rows=78,395 width=36) (actual time=0.005..13.006 rows=78,395 loops=1)

  • Buffers: shared hit=1,642
Planning time : 0.189 ms
Execution time : 101,226.166 ms