explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cp5r

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

Sort (cost=1,276,505.16..1,277,276.70 rows=308,616 width=13,704) (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", 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=194,226.12..197,312.28 rows=308,616 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,036.74..189,596.88 rows=308,616 width=48) (actual rows= loops=)

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

Hash Left Join (cost=35.28..182,699.11 rows=308,616 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.70..22,556.78 rows=308,616 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..20,224.45 rows=308,616 width=85) (actual rows= loops=)

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

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

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..17.68 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=1,876.76..1,876.76 rows=89,976 width=20) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,876.76 rows=89,976 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 Join (cost=2,956.78..16,707.11 rows=308,616 width=13,704) (actual rows= loops=)

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

Hash Join (cost=2,878.07..15,816.51 rows=308,616 width=433) (actual rows= loops=)

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

Hash Left Join (cost=2,768.35..14,894.90 rows=308,616 width=387) (actual rows= loops=)

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

Hash Left Join (cost=2,765.84..10,561.27 rows=308,616 width=391) (actual rows= loops=)

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

Hash Left Join (cost=147.47..7,132.72 rows=308,616 width=225) (actual rows= loops=)

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

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..6,172.32 rows=308,616 width=48) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=132.21..132.21 rows=1,221 width=181) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..132.21 rows=1,221 width=181) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=1,888.72..1,888.72 rows=58,372 width=170) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on task tk (cost=0.00..1,888.72 rows=58,372 width=170) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

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

31. 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)
32. 0.000 0.000 ↓ 0.0

Hash (cost=85.43..85.43 rows=1,943 width=46) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..85.43 rows=1,943 width=46) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=54.43..54.43 rows=1,943 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..54.43 rows=1,943 width=32) (actual rows= loops=)