explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c1jY

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=901,278.71..901,797.37 rows=207,465 width=14,252) (actual rows= loops=)

  • Sort Key: ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.entrydate2, ((ui.email)::character varying(255)) COLLATE "en_US", ((login.loginname)::character varying(255)) COLLATE "en_US", ((pj.info11)::character varying(255)) COLLATE "en_US", ((pj.info2)::character varying(255)) COLLATE "en_US", ((pj.info3)::character varying(255)) COLLATE "en_US", ((pj.info6)::character varying(255)) COLLATE "en_US", ((pj.info1)::character varying(255)) COLLATE "en_US", ((pj.info12)::character varying(255)) COLLATE "en_US", ((pj.info8)::character varying(255)) COLLATE "en_US", ((pj.info9)::character varying(255)) COLLATE "en_US", ((pj.info10)::character varying(255)) COLLATE "en_US", ((pj.info7)::character varying(255)) COLLATE "en_US", ((tk.info9)::character varying(255)) COLLATE "en_US", ((pj.info13)::character varying(255)) COLLATE "en_US", ((userinfo7.displayname)::text) COLLATE "en_US", ((userinfo7.email)::character varying(255)) COLLATE "en_US", pj.id, tk.id, ui.id, ((pj.info5)::character varying(255)) COLLATE "en_US", ((tk.info1)::character varying(255)) COLLATE "en_US", ((tk.info2)::character varying(255)) COLLATE "en_US", ((tk.info3)::character varying(255)) COLLATE "en_US", ((tk.info4)::character varying(255)) COLLATE "en_US", ((tk.info5)::character varying(255)) COLLATE "en_US", ((tk.info6)::character varying(255)) COLLATE "en_US", ((tk.info7)::character varying(255)) COLLATE "en_US", ((tk.info8)::character varying(255)) COLLATE "en_US", ((tk.info10)::character varying(255)) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=144,372.00..146,446.65 rows=207,465 width=48) (actual rows= loops=)

  • Group Key: ts.userid, at.entrydate, at.id, at.projectid, at.taskid
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,838.32..141,260.02 rows=207,465 width=48) (actual rows= loops=)

  • Hash Cond: (at.timesheetid = ts.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=35.24..134,837.68 rows=207,465 width=616) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=17.66..27,180.30 rows=207,465 width=81) (actual rows= loops=)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
7. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..25,606.64 rows=207,465 width=85) (actual rows= loops=)

  • Filter: ((entrydate >= '2020-06-01'::date) AND (entrydate <= '2020-09-19'::date))
8. 0.000 0.000 ↓ 0.0

Hash (cost=17.65..17.65 rows=1 width=16) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..17.65 rows=1 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc_1 (cost=0.00..1.08 rows=8 width=16) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj_2 (cost=0.28..2.05 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = pc_1.projectid)
  • Filter: (clientbillingallocationmethod = 1)
12. 0.000 0.000 ↓ 0.0

Hash (cost=17.48..17.48 rows=8 width=44) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..17.48 rows=8 width=44) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..1.10 rows=8 width=44) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj_1 (cost=0.28..2.05 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = pc.projectid)
  • Filter: (clientbillingallocationmethod = 0)
16. 0.000 0.000 ↓ 0.0

Hash (cost=2,375.81..2,375.81 rows=114,181 width=20) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..2,375.81 rows=114,181 width=20) (actual rows= loops=)

18.          

Initplan (for Sort)

19. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,404.90..13,217.29 rows=207,465 width=14,252) (actual rows= loops=)

  • Hash Cond: (pj.projectleaderapproverid = userinfo7.id)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,275.57..12,542.50 rows=207,465 width=486) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.userid1 = login.userid)
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,183.24..11,904.67 rows=207,465 width=458) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.userid1 = ui.id)
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,053.91..11,229.84 rows=207,465 width=411) (actual rows= loops=)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,051.40..8,291.54 rows=207,465 width=415) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.taskid5 = tk.id)
25. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=174.22..4,869.73 rows=207,465 width=242) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.projectid4 = pj.id)
26. 0.000 0.000 ↓ 0.0

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..4,149.30 rows=207,465 width=48) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=156.32..156.32 rows=1,432 width=198) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..156.32 rows=1,432 width=198) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=2,090.97..2,090.97 rows=62,897 width=177) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on task tk (cost=0.00..2,090.97 rows=62,897 width=177) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=2.40..2.40 rows=9 width=4) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.15..2.40 rows=9 width=4) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $3) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
33. 0.000 0.000 ↓ 0.0

Hash (cost=97.48..97.48 rows=2,548 width=47) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..97.48 rows=2,548 width=47) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=60.48..60.48 rows=2,548 width=32) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..60.48 rows=2,548 width=32) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=97.48..97.48 rows=2,548 width=48) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo7 (cost=0.00..97.48 rows=2,548 width=48) (actual rows= loops=)