explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tZPz

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

Sort (cost=1,441,712.28..1,441,712.28 rows=1 width=2,477) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts7.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts7.timesheetenddate6, dmv_timesheetday_facts7.timesheetstartdate7, dmv_timesheetday_facts7.timesheetenddate8, dmv_timesheetday_facts7.comments4 COLLATE "en_US", ((userinfo12.displayname)::text) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,441,630.08..1,441,630.25 rows=3 width=157) (actual rows= loops=)

  • Group Key: (upper(at.comments)), ui_1.duplicatename, ((date_part('month'::text, (ts_2.enddate)::timestamp without time zone))::integer), ts_2.startdate, ts_2.enddate, (CASE WHEN (pj_3.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, at.taskid, ts_1.id
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,441,630.08..1,441,630.09 rows=3 width=170) (actual rows= loops=)

  • Sort Key: (upper(at.comments)), ui_1.duplicatename, ((date_part('month'::text, (ts_2.enddate)::timestamp without time zone))::integer), ts_2.startdate, ts_2.enddate, (CASE WHEN (pj_3.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, at.taskid, ts_1.id
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=875.71..1,441,630.06 rows=3 width=170) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=875.43..1,441,629.14 rows=3 width=138) (actual rows= loops=)

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

Nested Loop (cost=1.54..2,035.18 rows=1 width=61) (actual rows= loops=)

  • Join Filter: (ts_1.id = ts_2.id)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..2,034.71 rows=1 width=37) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..2,032.27 rows=1 width=21) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.42..2,029.97 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-07-19'::date) AND (startdate <= '2020-07-25'::date))
  • Filter: ((enddate >= '2020-07-19'::date) AND (enddate <= '2020-07-25'::date))
11. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ts_1.userid)
12. 0.000 0.000 ↓ 0.0

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist_1 (cost=0.42..2.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_1.id)
13. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts_2 (cost=0.42..0.46 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = tslist_1.timesheetid)
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=873.89..1,433,288.90 rows=504,405 width=125) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj_2.id)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=530.42..1,401,897.64 rows=2,458,498 width=629) (actual rows= loops=)

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

Hash Left Join (cost=195.03..83,937.76 rows=2,458,498 width=114) (actual rows= loops=)

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

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..65,303.98 rows=2,458,498 width=114) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

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

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (projectid = pj_4.id)
22. 0.000 0.000 ↓ 0.0

Hash (cost=299.21..299.21 rows=2,894 width=52) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Join (cost=229.41..299.21 rows=2,894 width=52) (actual rows= loops=)

  • Hash Cond: (pc.projectid = pj_3.id)
24. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..62.19 rows=2,895 width=48) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=192.71..192.71 rows=2,936 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_3 (cost=0.00..192.71 rows=2,936 width=8) (actual rows= loops=)

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

Hash (cost=335.89..335.89 rows=606 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

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

  • Filter: ((upper((info3)::text) = ANY ('{"WCG HOLDCO IV LLC","WCG HOLDCO IV LLC ELIM","WCG HOLDCO LLC","CW HOLDCO INC.",CENTERWATCH,"CW ELIM","CW ELIM (OLD)",FDANEWS,"KMR GROUP, INC.",MAGI,"WCG HOLDINGS IV INC.","EPS INC.","APPLIED CLINICAL INTELLIGENCE, LLC","CLINTRAX GLOBAL, INC.","EPS INC - ELIM","EPS LLC","MEDAVANTE-PROPHASE, INC.","MEDAVANTE ELIMINATION","MEDAVANTE EUROPE, GMBH (USD) [TO BE CONVERTED]","MEDAVANTE, KK (USD) [TO BE CONVERTED]","MEDAVANTE-PROPHASE EUROPE, INC.","MEDAVANTE, KK","MEDAVANTE-PROPHASE EUROPE GMBH","WCG AUSTRALIA PTY LTD","PROPHASE, INC.","THREEWIRE, INC.","THREEWIRE ELIMINATION","THREEWIRE GMBH (GERMANY) (EURO)","THREEWIRE GMBH (GERMANY) [TO BE CONVERTED]","VELOS, LLC","WIRB COPERNICUS GROUP, INC",AIRB,CGIRB,IRBNET,MLIRB,NEIRB,"WCG INC-ELIM",WIRB,VIGILARE}'::text[])) OR (info3 IS NULL))
29. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = at.projectid)
30.          

Initplan (for Sort)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.09..81.01 rows=1 width=2,477) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..80.42 rows=1 width=277) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.25..78.32 rows=1 width=261) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.97..77.99 rows=1 width=249) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.69..76.11 rows=1 width=245) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..75.93 rows=1 width=249) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.12..73.49 rows=1 width=225) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..71.19 rows=1 width=204) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..68.89 rows=1 width=182) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts7.userid3 = ui.id)
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..6.95 rows=3 width=152) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts7 (cost=0.00..0.06 rows=3 width=125) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (userid = dmv_timesheetday_facts7.userid3)
44. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((id = login.userid) AND (id = ANY ('{2591,2590,2594,2600,2605,2601,2595,2602,2592,2597,2593,2603,2596,2604,2598,2708,2599,2673,2699,2906,2670,2672,12782,2677,2680,2679,2681,2682,2684,2873,2872,2929,2930,2687,2931,2685,2691,2690,2693,2704,2695,2696,2697,2698,2701,2702,2703,2706,2705,2707,2709,2710,2711,2712,2713,2714,2715,12749,2678,12783,2694,3939,2692,2700,8691,8692,8690,2686,2689,2688,2683}'::integer[])))
45. 0.000 0.000 ↓ 0.0

Index Scan using clients_pkey on clients cl (cost=0.28..2.29 rows=1 width=26) (actual rows= loops=)

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

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

  • Index Cond: (dmv_timesheetday_facts7.projectid10 = id)
47. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (dmv_timesheetday_facts7.taskid11 = id)
48. 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 = $6) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
49. 0.000 0.000 ↓ 0.0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy13 (cost=0.29..1.87 rows=1 width=8) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo userinfo12 (cost=0.28..0.33 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (userhierarchy13.supervisorid = id)
51. 0.000 0.000 ↓ 0.0

Index Only Scan using timesheet_pkey on timesheet ts (cost=0.42..2.10 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = dmv_timesheetday_facts7.timesheetid12)
52. 0.000 0.000 ↓ 0.0

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..0.59 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts.id)
  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))