explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QEbY

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

Sort (cost=188,331.50..188,331.51 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_facts6.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=188,325.98..188,326.00 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=188,325.98..188,325.98 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=770.64..188,325.97 rows=1 width=25) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=770.36..188,323.67 rows=1 width=24) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=770.08..188,290.91 rows=1 width=24) (actual rows= loops=)

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

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

  • Index Cond: ((startdate >= '2020-08-01'::date) AND (startdate <= '2020-08-31'::date))
  • Filter: ((enddate >= '2020-08-01'::date) AND (enddate <= '2020-08-31'::date))
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=769.66..185,537.99 rows=30,827 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))
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=769.38..168,640.90 rows=30,827 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[]))
11. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..107,461.42 rows=3,082,742 width=65) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=699.44..699.44 rows=5,595 width=52) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=565.79..699.44 rows=5,595 width=52) (actual rows= loops=)

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

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

15. 0.000 0.000 ↓ 0.0

Hash (cost=479.40..479.40 rows=6,911 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..479.40 rows=6,911 width=8) (actual rows= loops=)

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

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

18. 0.000 0.000 ↓ 0.0

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

19. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_3 (cost=0.00..479.40 rows=1 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.28..2.30 rows=1 width=16) (actual rows= loops=)

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

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

  • Index Cond: ((id = at.projectid) AND (id = ANY ('{3893,14833,1923,15459,15789,18167,4173,16416,14669,4182,2011,3939,5331,2088,10037,3716,4165,12392,4174,4168,3056,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,4170,3038,4797,4185,4175,4183,2996,4667,3057,4371,2934,4689,3058,4177,4076,2935,8763,5066,5330,8566,5650,5764,8430,8431,3766,6780,6815,9123,9651,9750,11334,4176,11499,12390,12753}'::integer[])))
22. 0.000 0.000 ↓ 0.0

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

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

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

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

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

  • Join Filter: (dmv_timesheetday_facts6.userid1 = du.userid)
26. 0.000 0.000 ↓ 0.0

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

  • Join Filter: (dmv_timesheetday_facts6.userid1 = login.userid)
27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

29. 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_facts6.userid1)
30. 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)
31. 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[]))
32. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = du.departmentid)
33. 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_facts6.projectid3 = id)