explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GrVXQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.082 300.648 ↑ 173.0 2 1

HashAggregate (cost=2,572.81..2,579.73 rows=346 width=60) (actual time=300.646..300.648 rows=2 loops=1)

  • Group Key: info.week, info.wdate
2. 0.813 300.566 ↑ 73.5 47 1

HashAggregate (cost=2,279.13..2,443.24 rows=3,455 width=48) (actual time=300.328..300.566 rows=47 loops=1)

  • Group Key: info.week, info.wdate, info.id, ((date_part('epoch'::text, ((((((sum(((CASE WHEN ((CASE WHEN ((schedule.mone)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.mone END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.mone)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.mone END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.mons)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.mons END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.mons)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.mons END END)::time without time zone)) + sum(((CASE WHEN ((CASE WHEN ((schedule.tuee)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.tuee END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.tuee)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.tuee END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.tues)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.tues END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.tues)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.tues END END)::time without time zone))) + sum(((CASE WHEN ((CASE WHEN ((schedule.wede)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.wede END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.wede)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.wede END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.weds)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.weds END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.weds)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.weds END END)::time without time zone))) + sum(((CASE WHEN ((CASE WHEN ((schedule.thue)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.thue END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.thue)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.thue END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.thus)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.thus END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.thus)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.thus END END)::time without time zone))) + sum(((CASE WHEN ((CASE WHEN ((schedule.frie)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.frie END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.frie)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.frie END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.fris)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.fris END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.fris)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.fris END END)::time without time zone))) + sum(((CASE WHEN ((CASE WHEN ((schedule.sate)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sate END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.sate)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sate END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.sats)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sats END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.sats)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sats END END)::time without time zone))) + sum(((CASE WHEN ((CASE WHEN ((schedule.sune)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sune END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.sune)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.sune END END)::time without time zone - (CASE WHEN ((CASE WHEN ((schedule.suns)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.suns END)::text = '0'::text) THEN '00:00'::character varying ELSE CASE WHEN ((schedule.suns)::text = '0:00'::text) THEN '00:00'::character varying ELSE schedule.suns END END)::time without time zone)))) / '3600'::double precision))
3. 2.307 299.753 ↑ 8.8 393 1

Hash Right Join (cost=1,977.92..2,201.39 rows=3,455 width=88) (actual time=293.425..299.753 rows=393 loops=1)

  • Hash Cond: (schedule.id = info.id)
4. 35.453 38.766 ↓ 1.0 2,461 1

HashAggregate (cost=656.78..728.75 rows=2,399 width=12) (actual time=34.698..38.766 rows=2,461 loops=1)

  • Group Key: schedule.id
5. 3.313 3.313 ↓ 1.0 2,461 1

Seq Scan on schedule (cost=0.00..146.99 rows=2,399 width=78) (actual time=0.052..3.313 rows=2,461 loops=1)

6. 0.417 258.680 ↓ 1.4 393 1

Hash (cost=1,317.55..1,317.55 rows=288 width=80) (actual time=258.680..258.680 rows=393 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
7. 0.590 258.263 ↓ 1.4 393 1

Subquery Scan on info (cost=1,300.99..1,317.55 rows=288 width=80) (actual time=222.619..258.263 rows=393 loops=1)

8. 21.678 257.673 ↓ 1.4 393 1

GroupAggregate (cost=1,300.99..1,314.67 rows=288 width=105) (actual time=222.615..257.673 rows=393 loops=1)

  • Group Key: l.week, wdate.wdate, a.employeeid, l.statuskey, l.userid
9. 45.852 235.995 ↓ 53.6 15,423 1

Sort (cost=1,300.99..1,301.71 rows=288 width=49) (actual time=222.559..235.995 rows=15,423 loops=1)

  • Sort Key: wdate.wdate, a.employeeid, l.statuskey, l.userid
  • Sort Method: quicksort Memory: 2548kB
10. 47.582 190.143 ↓ 53.6 15,423 1

Hash Join (cost=37.67..1,289.22 rows=288 width=49) (actual time=1.373..190.143 rows=15,423 loops=1)

  • Hash Cond: ((l.week)::double precision = date_part('week'::text, wdate.wdate))
  • Join Filter: ((date_part('epoch'::text, CASE WHEN ((a.enddate > wdate.wdate) OR (a.enddate IS NULL)) THEN (wdate.wdate - (a.hiredate)::timestamp with time zone) ELSE NULL::interval END) / '86400'::double precision) >= '35'::double precision)
  • Rows Removed by Join Filter: 10697
11. 23.025 142.313 ↓ 75.5 13,060 1

Nested Loop (cost=15.17..1,179.79 rows=173 width=49) (actual time=1.103..142.313 rows=13,060 loops=1)

12. 6.318 15.248 ↓ 60.0 180 1

Hash Join (cost=14.61..463.38 rows=3 width=22) (actual time=0.369..15.248 rows=180 loops=1)

  • Hash Cond: ((a.pname = p.positionname) AND (a.officename = p.office))
13. 8.778 8.778 ↑ 1.0 6,487 1

Seq Scan on agenttable a (cost=0.00..383.87 rows=6,487 width=56) (actual time=0.059..8.778 rows=6,487 loops=1)

14. 0.024 0.152 ↓ 7.0 14 1

Hash (cost=14.58..14.58 rows=2 width=32) (actual time=0.152..0.152 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.128 0.128 ↓ 7.0 14 1

Seq Scan on ptype p (cost=0.00..14.58 rows=2 width=32) (actual time=0.019..0.128 rows=14 loops=1)

  • Filter: ((stype = 'STAFF'::text) AND (workarea = 'PRODUCTION'::text) AND (office = ANY ('{"USAgencies MGMT SVCS","PERSONABLE FNOL","MS AFA","AS AFA","CELESTITE CLAIM STATUS","CLAIMS PROCESSING"}'::text[])))
  • Rows Removed by Filter: 427
16. 104.040 104.040 ↑ 1.5 73 180

Index Scan using ix_weektime on agentactivitylog l (cost=0.56..237.67 rows=113 width=37) (actual time=0.035..0.578 rows=73 loops=180)

  • Index Cond: ((week = 1) AND (userid = a.useri3))
17. 0.120 0.248 ↑ 9.5 105 1

Hash (cost=10.00..10.00 rows=1,000 width=8) (actual time=0.248..0.248 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
18. 0.128 0.128 ↑ 9.5 105 1

Function Scan on generate_series wdate (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.054..0.128 rows=105 loops=1)

Planning time : 2.480 ms
Execution time : 301.971 ms