explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C7t0 : pike TDT

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

Sort (cost=2,515,623.54..2,515,623.54 rows=1 width=1,617) (actual rows= loops=)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts9.timesheetstartdate3, dmv_timesheetday_facts9.timesheetenddate4, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts9.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((activities12.name)::character varying(50)) COLLATE "en_US", ((br.name)::character varying(50)) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,515,607.88..2,515,607.92 rows=1 width=69) (actual rows= loops=)

  • Group Key: ts.id, ts_1.startdate, ts_1.enddate, ui_1.duplicatename, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.activityid, at.id
4. 0.000 0.000 ↓ 0.0

Sort (cost=2,515,607.88..2,515,607.89 rows=1 width=69) (actual rows= loops=)

  • Sort Key: ts.id, ts_1.startdate, ts_1.enddate, ui_1.duplicatename, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.activityid, at.id
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,398.88..2,515,607.87 rows=1 width=69) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,398.60..2,515,607.57 rows=1 width=77) (actual rows= loops=)

  • Join Filter: (ts.userid = ui_1.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,398.31..2,515,607.08 rows=1 width=80) (actual rows= loops=)

  • Join Filter: (ts.id = tslist.timesheetid)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,397.89..2,515,606.63 rows=1 width=112) (actual rows= loops=)

  • Join Filter: (ts.id = ts_1.id)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,397.47..2,515,606.17 rows=1 width=88) (actual rows= loops=)

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

Nested Loop Semi Join (cost=0.70..7,587.98 rows=1 width=24) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..7,580.13 rows=6 width=20) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-07-20'::date) AND (startdate <= '2020-07-26'::date))
  • Filter: ((enddate >= '2020-07-20'::date) AND (enddate <= '2020-07-26'::date))
12. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation4 (cost=0.28..1.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((userid = ts.userid) AND (locationid = '01efd2df-bb0f-4e53-bf5e-3dffc572f0d7'::uuid) AND (startdate <= '2020-07-27'::date) AND (enddate >= '2020-07-27'::date))
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,396.76..2,507,992.52 rows=2,053 width=64) (actual rows= loops=)

  • Hash Cond: (at.projectid = project5.id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,388.43..2,454,430.72 rows=4,241,256 width=616) (actual rows= loops=)

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

Hash Left Join (cost=949.03..184,622.02 rows=4,241,256 width=85) (actual rows= loops=)

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

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..151,863.56 rows=4,241,256 width=85) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=948.48..948.48 rows=37 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..948.48 rows=37 width=16) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..909.60 rows=38 width=4) (actual rows= loops=)

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

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

  • Index Cond: (projectid = pj_1.id)
21. 0.000 0.000 ↓ 0.0

Hash (cost=1,304.51..1,304.51 rows=10,791 width=52) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,046.72..1,304.51 rows=10,791 width=52) (actual rows= loops=)

  • Hash Cond: (pc.projectid = pj.id)
23. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..229.35 rows=10,828 width=48) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=909.60..909.60 rows=10,970 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..909.60 rows=10,970 width=8) (actual rows= loops=)

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

Hash (cost=8.30..8.30 rows=3 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Unique (cost=8.25..8.27 rows=3 width=4) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Sort (cost=8.25..8.26 rows=3 width=4) (actual rows= loops=)

  • Sort Key: project5.id
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..8.23 rows=3 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using ixpcclientid on projectclient projectclient6 (cost=0.29..4.32 rows=3 width=4) (actual rows= loops=)

  • Index Cond: (clientid = 3,498)
31. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project5 (cost=0.29..1.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = projectclient6.projectid)
32. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = at.timesheetid)
33. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (timesheetid = at.timesheetid)
34. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = userlocation4.userid)
35. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project project7 (cost=0.29..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = at.projectid)
  • Filter: (projectstatuslabelid = '3bbf6214-ea06-4044-8d23-558674d217f1'::uuid)
36.          

Initplan (for Sort)

37. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.29..13.59 rows=1 width=1,617) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.87..11.15 rows=1 width=151) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.87..6.39 rows=1 width=140) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = login.userid)
42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.59..6.06 rows=1 width=126) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.31..3.76 rows=1 width=92) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=0.03..1.46 rows=1 width=73) (actual rows= loops=)

  • Hash Cond: (activities12.id = dmv_timesheetday_facts9.activityid7)
45. 0.000 0.000 ↓ 0.0

Seq Scan on activities activities12 (cost=0.00..1.30 rows=30 width=12) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=69) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (dmv_timesheetday_facts9.clientid6 = id)
49. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = dmv_timesheetday_facts9.userid2)
50. 0.000 0.000 ↓ 0.0

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

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

Nested Loop (cost=1.99..4.62 rows=13 width=43) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.57..3.70 rows=1 width=51) (actual rows= loops=)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.14..3.22 rows=1 width=67) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..2.75 rows=1 width=87) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..2.45 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (dmv_timesheetday_facts9.timeallocationid8 = id)
56. 0.000 0.000 ↓ 0.0

Index Scan using billingrate_pkey on billingrate br (cost=0.28..0.30 rows=1 width=43) (actual rows= loops=)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
57. 0.000 0.000 ↓ 0.0

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.43..0.46 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $8) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $9) AND (userid IS NULL)))
58. 0.000 0.000 ↓ 0.0

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.43..0.46 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (projectbillingrateid = pbr.id)
59. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtsuseridstartdateenddate on timesheet dmvts (cost=0.42..0.84 rows=9 width=12) (actual rows= loops=)

  • Index Cond: ((userid = dm_attendancetimeallocation_facts.userid) AND (startdate <= dm_attendancetimeallocation_facts.entrydate) AND (enddate >= dm_attendancetimeallocation_facts.entrydate))
60. 0.000 0.000 ↓ 0.0

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts dm_timesheetlist_facts14 (cost=0.42..2.44 rows=1 width=48) (actual rows= loops=)

  • Index Cond: (timesheetid = dmv_timesheetday_facts9.timesheetid1)