explain.depesz.com

PostgreSQL's explain analyze made readable

Result: svYh

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

Sort (cost=1,032,162.27..1,032,162.28 rows=1 width=2,877) (actual rows= loops=)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((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_facts9.userduplicatename4, ((login.loginname)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts9.timesheetstatus5
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,032,154.49..1,032,154.52 rows=1 width=81) (actual rows= loops=)

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

Sort (cost=1,032,154.49..1,032,154.49 rows=1 width=81) (actual rows= loops=)

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

Nested Loop (cost=739.78..1,032,154.48 rows=1 width=81) (actual rows= loops=)

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

Nested Loop (cost=739.36..1,032,154.02 rows=1 width=109) (actual rows= loops=)

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

Nested Loop (cost=739.08..1,032,153.57 rows=1 width=112) (actual rows= loops=)

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

Nested Loop (cost=738.66..1,032,153.11 rows=1 width=96) (actual rows= loops=)

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

Nested Loop (cost=0.70..1,221.37 rows=1 width=24) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Only Scan using departmentusers_pkey on departmentusers departmentusers4 (cost=0.28..24.79 rows=573 width=4) (actual rows= loops=)

  • Index Cond: (departmentid = 28)
11. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((userid = departmentusers4.userid) AND (startdate >= '2019-10-20'::date) AND (startdate <= '2019-10-26'::date))
  • Filter: ((enddate >= '2019-10-20'::date) AND (enddate <= '2019-10-26'::date))
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=737.96..1,023,026.87 rows=632,390 width=72) (actual rows= loops=)

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

Hash Left Join (cost=526.45..1,000,683.39 rows=1,752,581 width=616) (actual rows= loops=)

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

Hash Left Join (cost=188.56..62,900.74 rows=1,752,581 width=65) (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))
15. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..49,567.81 rows=1,752,581 width=65) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=188.53..188.53 rows=2 width=16) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..188.53 rows=2 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

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

  • Filter: (clientbillingallocationmethod = 1)
19. 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_2.id)
20. 0.000 0.000 ↓ 0.0

Hash (cost=298.50..298.50 rows=3,151 width=52) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=223.80..298.50 rows=3,151 width=52) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..66.41 rows=3,153 width=48) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=183.91..183.91 rows=3,191 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..183.91 rows=3,191 width=8) (actual rows= loops=)

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

Hash (cost=195.89..195.89 rows=1,250 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on project project5 (cost=0.00..195.89 rows=1,250 width=4) (actual rows= loops=)

  • Filter: (projectstatuslabelid = ANY ('{d607d1a4-93d0-4974-94ad-c2de7ba27473,22e9c44c-f33f-4840-8b6d-638837469dd8,944e8d0d-340f-4a1f-8379-b33b89547da1,f6918709-d5cf-436b-b0ff-2191cd2dd6a9,264de22a-9dbd-44d9-bc35-e588c184b80f}'::uuid[]))
27. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (id = departmentusers4.userid)
29. 0.000 0.000 ↓ 0.0

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.45 rows=1 width=20) (actual rows= loops=)

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

Nested Loop (cost=1.54..7.74 rows=1 width=2,877) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.39..7.57 rows=1 width=172) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts9.userid3 = du.userid)
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.11..7.26 rows=1 width=172) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts9.userid3 = login.userid)
33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..6.92 rows=1 width=155) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.55..4.62 rows=1 width=121) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.27..2.32 rows=1 width=77) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

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

  • Filter: (timesheetstatus5 = ANY ('{0,1,2,3}'::integer[]))
37. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (dmv_timesheetday_facts9.projectid7 = id)
39. 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.userid3)
40. 0.000 0.000 ↓ 0.0

Index Scan using login_pkey on login (cost=0.28..0.32 rows=1 width=17) (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.31 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (userid = login.userid)
42. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = du.departmentid)