explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FQcVS : argo

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

Sort (cost=1,380,039.63..1,380,039.64 rows=1 width=3,541) (actual rows= loops=)

  • Sort Key: ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts7.userduplicatename6, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((ui.info2)::character varying(255)) COLLATE "en_US", ((ui.info1)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts7.timeentryinfo22 COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", dmv_timesheetday_facts7.entrydate4, dmv_timesheetday_facts7.comments5 COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,380,030.21..1,380,030.26 rows=1 width=177) (actual rows= loops=)

  • Group Key: ts.userid, (upper(((CASE WHEN (at.timeoffcodeid IS NULL) THEN at.timeentryinfo2 ELSE NULL::character varying END))::text)), at.id, at.entrydate, (upper(at.comments)), ui_1.duplicatename, at.projectid, at.taskid
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,380,030.21..1,380,030.22 rows=1 width=192) (actual rows= loops=)

  • Sort Key: ts.userid, (upper(((CASE WHEN (at.timeoffcodeid IS NULL) THEN at.timeentryinfo2 ELSE NULL::character varying END))::text)), at.id, at.entrydate, (upper(at.comments)), ui_1.duplicatename, at.projectid, at.taskid
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=338.20..1,380,030.20 rows=1 width=192) (actual rows= loops=)

  • Join Filter: (at.projectid = project4.id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=337.57..1,379,966.86 rows=1 width=132) (actual rows= loops=)

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

Nested Loop (cost=0.42..2,594.69 rows=1 width=21) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_1 (cost=0.00..57.25 rows=1,625 width=5) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((userid = ui_1.id) AND (startdate >= '2020-06-01'::date) AND (startdate <= '2020-06-30'::date))
  • Filter: ((enddate >= '2020-06-01'::date) AND (enddate <= '2020-06-30'::date))
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=337.15..1,377,351.18 rows=1,679 width=143) (actual rows= loops=)

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

Hash Left Join (cost=334.84..1,347,469.46 rows=2,365,207 width=631) (actual rows= loops=)

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

Hash Left Join (cost=135.98..87,940.12 rows=2,365,207 width=135) (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))
13. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..70,065.07 rows=2,365,207 width=139) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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

16. 0.000 0.000 ↓ 0.0

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

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

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

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

Hash (cost=182.79..182.79 rows=1,286 width=44) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=151.32..182.79 rows=1,286 width=44) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..28.07 rows=1,286 width=44) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=134.66..134.66 rows=1,333 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..134.66 rows=1,333 width=4) (actual rows= loops=)

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

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

24. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (upper((code)::text) = 'P064SS01'::text)
25. 0.000 0.000 ↓ 0.0

Unique (cost=0.63..57.67 rows=252 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Merge Join (cost=0.63..57.04 rows=252 width=4) (actual rows= loops=)

  • Merge Cond: (project4.id = projectclient5.projectid)
27. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project4 (cost=0.28..31.27 rows=1,333 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient projectclient5 (cost=0.28..19.44 rows=252 width=4) (actual rows= loops=)

  • Index Cond: (clientid = 9)
29.          

Initplan (for Sort)

30. 0.000 0.000 ↓ 0.0

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

31. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.98..8.35 rows=1 width=3,541) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.69..8.00 rows=1 width=233) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.25..5.50 rows=1 width=233) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.97..3.20 rows=1 width=203) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..3.04 rows=1 width=190) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts7.userid1 = du.userid)
36. 0.000 0.000 ↓ 0.0

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

  • Join Filter: (dmv_timesheetday_facts7.userid1 = login.userid)
37. 0.000 0.000 ↓ 0.0

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

38. 0.000 0.000 ↓ 0.0

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

39. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = dmv_timesheetday_facts7.userid1)
40. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (userid = ui.id)
  • Filter: (departmentid = ANY ('{1,62,28,8,41,18,61,59,27,47,45,14,49,7,12,52,5,9,3,58,73,74,44,53,30,29,2,10,16,56,55,75,6,24,21,13,76,32,20,57,4,60,11,51,54,43,48,42,50,78,22,15,46,17,63,68,71,64,69,66,72,70,65}'::integer[]))
42. 0.000 0.000 ↓ 0.0

Index Scan using departments_pkey on departments dep (cost=0.14..0.16 rows=1 width=21) (actual rows= loops=)

  • Index Cond: (id = du.departmentid)
43. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.28..2.29 rows=1 width=34) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts7.projectid7 = id)
44. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.44..2.50 rows=1 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.29..2.31 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts7.taskid8 = id)
46. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..0.18 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)
47. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..0.35 rows=1 width=74) (actual rows= loops=)

  • Index Cond: (tk.id = taskid)