explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vmeV

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

Sort (cost=1,365,017.07..1,365,017.08 rows=1 width=2,385) (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.userduplicatename3, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts7.timesheetstartdate4, dmv_timesheetday_facts7.timesheetenddate5
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,365,011.60..1,365,011.63 rows=1 width=49) (actual rows= loops=)

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

Sort (cost=1,365,011.60..1,365,011.60 rows=1 width=49) (actual rows= loops=)

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

Nested Loop (cost=1,509.55..1,365,011.59 rows=1 width=49) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,509.27..1,365,009.28 rows=1 width=48) (actual rows= loops=)

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

Nested Loop (cost=1,508.85..1,365,008.83 rows=1 width=96) (actual rows= loops=)

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

Nested Loop (cost=1,508.42..1,365,008.37 rows=1 width=72) (actual rows= loops=)

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

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

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

Hash Join (cost=1,508.00..1,358,921.98 rows=24,284 width=52) (actual rows= loops=)

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

Hash Left Join (cost=1,428.25..1,329,421.01 rows=2,330,036 width=616) (actual rows= loops=)

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

Hash Left Join (cost=556.62..80,672.27 rows=2,330,036 width=81) (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))
13. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..62,640.36 rows=2,330,036 width=81) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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

16. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..555.30 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_1.id)
18. 0.000 0.000 ↓ 0.0

Hash (cost=790.62..790.62 rows=6,480 width=52) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=636.60..790.62 rows=6,480 width=52) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..137.00 rows=6,480 width=48) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=555.30..555.30 rows=6,504 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..555.30 rows=6,504 width=8) (actual rows= loops=)

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

Hash (cost=79.01..79.01 rows=60 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=77.81..78.41 rows=60 width=4) (actual rows= loops=)

  • Group Key: project4.id
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..77.66 rows=60 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using ixpcclientid on projectclient projectclient5 (cost=0.28..22.66 rows=60 width=4) (actual rows= loops=)

  • Index Cond: (clientid = 656)
27. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = projectclient5.projectid)
28. 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)
29. 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)
30. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ts.userid)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..5.43 rows=1 width=2,385) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..5.28 rows=1 width=117) (actual rows= loops=)

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

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

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..2.65 rows=1 width=99) (actual rows= loops=)

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

Nested Loop (cost=0.29..2.32 rows=1 width=87) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

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

37. 0.000 0.000 ↓ 0.0

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

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

Index Scan using login_pkey on login (cost=0.29..0.32 rows=1 width=12) (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.27..2.29 rows=1 width=22) (actual rows= loops=)

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

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

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

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

  • Index Cond: (id = du.departmentid)