explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0bCj

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

Sort (cost=197,122.64..197,122.64 rows=1 width=1,845) (actual rows= loops=)

  • Sort Key: ((dep.name)::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.userduplicatename2, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=197,117.10..197,117.13 rows=1 width=25) (actual rows= loops=)

  • Group Key: ts.userid, ui_1.duplicatename, at.projectid
4. 0.000 0.000 ↓ 0.0

Sort (cost=197,117.10..197,117.11 rows=1 width=25) (actual rows= loops=)

  • Sort Key: ts.userid, ui_1.duplicatename, at.projectid
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=887.76..197,117.09 rows=1 width=25) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=887.48..197,115.16 rows=1 width=24) (actual rows= loops=)

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

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=54.69..3,079.41 rows=11 width=20) (actual rows= loops=)

  • Index Cond: ((startdate >= '2020-06-01'::date) AND (startdate <= '2020-06-30'::date) AND (enddate >= '2020-06-01'::date) AND (enddate <= '2020-06-30'::date))
  • Filter: ((NOT (hashed SubPlan 1)) AND ((hashed SubPlan 2) OR (NOT (hashed SubPlan 3))))
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ud_userdivisionstartend on userdivision userdivision4 (cost=0.15..23.71 rows=126 width=4) (actual rows= loops=)

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

Seq Scan on userlocation userlocation5 (cost=0.00..15.15 rows=263 width=4) (actual rows= loops=)

  • Filter: ((startdate <= '2020-07-06'::date) AND (enddate >= '2020-07-06'::date) AND (locationid = ANY ('{f90224c8-82cd-43ab-867c-4241074d5168,aa631d7e-0124-4688-ad2d-7152b1e8f171,12a4e33b-337b-491f-ba83-a0841a74af52}'::uuid[])))
11. 0.000 0.000 ↓ 0.0

Seq Scan on userlocation userlocation6 (cost=0.00..13.32 rows=445 width=4) (actual rows= loops=)

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

Materialize (cost=832.79..193,943.46 rows=568 width=36) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=832.79..193,940.62 rows=568 width=36) (actual rows= loops=)

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

Nested Loop Left Join (cost=733.96..193,462.93 rows=30,004 width=616) (actual rows= loops=)

  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate) AND (pj_3.id = at.projectid) AND (pc_1.clientid = at.userspecifiedclientid))
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=733.68..177,020.79 rows=30,004 width=97) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj_2.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Filter: (CASE WHEN (pj_2.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END = ANY ('{406,21}'::integer[]))
16. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..106,638.08 rows=3,000,408 width=65) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=664.33..664.33 rows=5,548 width=52) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=532.39..664.33 rows=5,548 width=52) (actual rows= loops=)

  • Hash Cond: (pc.projectid = pj_2.id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..117.36 rows=5,549 width=48) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=462.70..462.70 rows=5,575 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..462.70 rows=5,575 width=8) (actual rows= loops=)

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

Materialize (cost=0.28..465.01 rows=1 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

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

24. 0.000 0.000 ↓ 0.0

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

  • Filter: (clientbillingallocationmethod = 1)
25. 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_3.id)
26. 0.000 0.000 ↓ 0.0

Hash (cost=97.48..97.48 rows=108 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = ANY ('{3893,14833,1923,15459,15789,14669,4182,2011,3939,5331,2088,10037,3716,4165,12392,4174,4168,3056,4173,2989,3599,3020,4181,3823,4179,4193,4575,4208,4113,4128,9323,12391,8233,8234,8235,1924,2424,2138,2140,3019,3085,8236,2004,8237,5031,2830,8238,2947,2005,8232,3100,2003,3440,4180,3249,3251,2251,2006,3021,2899,10577,2977,4276,4673,4714,4925,3871,8762,2959,2973,2995,2953,4171,4183,4170,3038,4797,4185,4175,2996,4667,3057,4371,3058,2934,4689,4177,4076,2935,8763,5066,5330,8566,5650,5764,8430,8431,3766,6780,6815,9123,9651,9750,11334,4176,11499,12390,12753}'::integer[]))
28. 0.000 0.000 ↓ 0.0

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

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

Nested Loop Left Join (cost=1.26..5.50 rows=1 width=1,845) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..3.20 rows=1 width=111) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..2.98 rows=1 width=92) (actual rows= loops=)

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

Nested Loop (cost=0.56..2.65 rows=1 width=92) (actual rows= loops=)

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

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

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = dmv_timesheetday_facts9.userid1)
36. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (userid = login.userid)
  • Filter: (departmentid = ANY ('{126,119,120,121,123,124,125,127,122}'::integer[]))
38. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (dmv_timesheetday_facts9.projectid3 = id)