explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lH1M

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

Sort (cost=1,591,081.58..1,591,081.58 rows=1 width=2,433) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", dmv_timesheetday_facts5.entrydate4, ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((userinfo10.displayname)::text) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,591,069.44..1,591,069.48 rows=1 width=69) (actual rows= loops=)

  • Group Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, at.taskid
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,591,069.44..1,591,069.45 rows=1 width=69) (actual rows= loops=)

  • Sort Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, at.taskid
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,440.48..1,591,069.43 rows=1 width=69) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,440.20..1,591,067.13 rows=1 width=68) (actual rows= loops=)

  • Join Filter: (at.timesheetid = ts.id)
7. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..3,096.22 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-10-11'::date) AND (startdate <= '2020-10-17'::date))
  • Filter: ((enddate >= '2020-10-11'::date) AND (enddate <= '2020-10-17'::date))
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,439.78..1,587,968.93 rows=158 width=80) (actual rows= loops=)

  • Hash Cond: (at.projectid = project3.id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,437.47..1,553,761.17 rows=2,709,203 width=616) (actual rows= loops=)

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

Hash Left Join (cost=1,828.76..99,960.83 rows=2,709,203 width=69) (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))
11. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..77,813.03 rows=2,709,203 width=69) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=1,828.61..1,828.61 rows=10 width=16) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,828.61 rows=10 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..1,808.46 rows=10 width=4) (actual rows= loops=)

  • Filter: (clientbillingallocationmethod = 1)
15. 0.000 0.000 ↓ 0.0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.29..2.01 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (projectid = pj_2.id)
16. 0.000 0.000 ↓ 0.0

Hash (cost=2,403.39..2,403.39 rows=16,425 width=52) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,013.80..2,403.39 rows=16,425 width=52) (actual rows= loops=)

  • Hash Cond: (pc.projectid = pj_1.id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..346.44 rows=16,435 width=48) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=1,808.46..1,808.46 rows=16,427 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..1,808.46 rows=16,427 width=8) (actual rows= loops=)

  • Filter: (clientbillingallocationmethod = 0)
21. 0.000 0.000 ↓ 0.0

Hash (cost=2.31..2.31 rows=1 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using ixprj_code on project project3 (cost=0.29..2.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (upper((code)::text) = 'ENT-3318-002'::text)
23. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..2.29 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (id = ts.userid)
24.          

Initplan (for Sort)

25. 0.000 0.000 ↓ 0.0

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

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..11.08 rows=1 width=2,433) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.52..10.91 rows=1 width=201) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts5.userid3 = du.userid)
28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.25..10.57 rows=1 width=205) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.97..10.23 rows=1 width=194) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.69..9.87 rows=1 width=190) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..9.69 rows=1 width=194) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.13..7.25 rows=1 width=174) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..4.95 rows=1 width=137) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..2.64 rows=1 width=114) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts5.userid3 = login.userid)
35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..2.32 rows=1 width=101) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..0.02 rows=1 width=69) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.29 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = dmv_timesheetday_facts5.userid3)
38. 0.000 0.000 ↓ 0.0

Index Scan using login_pkey on login (cost=0.28..0.32 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (userid = ui.id)
39. 0.000 0.000 ↓ 0.0

Index Scan using clients_pkey on clients cl (cost=0.29..2.30 rows=1 width=27) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts5.clientid6 = id)
40. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.29..2.31 rows=1 width=41) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts5.projectid7 = id)
41. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.42..2.44 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts5.taskid8 = id)
42. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..0.17 rows=1 width=4) (actual rows= loops=)

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

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy11 (cost=0.28..0.35 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((ui.id = userid) AND ('2020-10-15'::date >= startdate))
  • Filter: ('2020-10-15'::date <= enddate)
44. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo10 (cost=0.28..0.34 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (userhierarchy11.supervisorid = id)
45. 0.000 0.000 ↓ 0.0

Index Scan using ixduuserid on departmentusers du (cost=0.28..0.32 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (userid = ui.id)
  • Filter: (departmentid = ANY ('{73,152,159,105,162,65,10,45,32,12,46,11,146,163,160}'::integer[]))
46. 0.000 0.000 ↓ 0.0

Index Only Scan using departments_pkey on departments dep (cost=0.14..0.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = du.departmentid)