explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6MyV

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

Sort (cost=1,393,544.70..1,393,544.70 rows=1 width=2,947) (actual rows= loops=)

  • Sort Key: ((pj.info3)::character varying(255)) COLLATE "en_US", ((pj.info12)::character varying(255)) COLLATE "en_US", ((pj.info13)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts7.projectstatustype4, ((userinfo8.displayname)::text) COLLATE "en_US", ((currencyinfo9.symbol)::character varying(50)) COLLATE "en_US", pbrh.hourlyrate
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,393,530.83..1,393,530.86 rows=1 width=80) (actual rows= loops=)

  • Group Key: ts.userid, projectstatuslabel2.projectstatustype, at.projectid, (CASE WHEN (pj_2.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.id
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,393,530.83..1,393,530.83 rows=1 width=80) (actual rows= loops=)

  • Sort Key: ts.userid, projectstatuslabel2.projectstatustype, at.projectid, (CASE WHEN (pj_2.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.id
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=363.00..1,393,530.82 rows=1 width=80) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=362.87..1,393,530.66 rows=1 width=92) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=362.59..1,393,530.37 rows=1 width=76) (actual rows= loops=)

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

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..2,992.08 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

Hash Join (cost=362.17..1,390,240.10 rows=23,855 width=88) (actual rows= loops=)

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

Hash Left Join (cost=344.70..1,360,082.52 rows=2,386,099 width=616) (actual rows= loops=)

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

Hash Left Join (cost=139.41..89,370.15 rows=2,386,099 width=81) (actual rows= loops=)

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

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..71,334.99 rows=2,386,099 width=81) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=189.41..189.41 rows=1,271 width=52) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=158.18..189.41 rows=1,271 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..27.89 rows=1,271 width=48) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=138.09..138.09 rows=1,607 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..138.09 rows=1,607 width=8) (actual rows= loops=)

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

Hash (cost=17.26..17.26 rows=17 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using ixprjprojectleaderapproverid on project project4 (cost=0.28..17.26 rows=17 width=4) (actual rows= loops=)

  • Index Cond: (projectleaderapproverid = 1,512)
24. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj_1 (cost=0.28..0.30 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (at.projectid = id)
25. 0.000 0.000 ↓ 0.0

Index Scan using projectstatuslabel_pkey on projectstatuslabel projectstatuslabel2 (cost=0.13..0.15 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (pj_1.projectstatuslabelid = id)
26.          

Initplan (for Sort)

27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.07..11.80 rows=1 width=2,947) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.93..11.64 rows=1 width=279) (actual rows= loops=)

  • Join Filter: (dmv_timesheetday_facts7.userid3 = du.userid)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.66..11.25 rows=1 width=279) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.38..9.95 rows=1 width=275) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.25..9.80 rows=1 width=161) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.70..4.83 rows=1 width=170) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.42..4.50 rows=1 width=159) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..2.32 rows=1 width=142) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

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

38. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (dmv_timesheetday_facts7.projectid5 = id)
39. 0.000 0.000 ↓ 0.0

Index Scan using clients_pkey on clients cl (cost=0.14..2.16 rows=1 width=21) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo userinfo8 (cost=0.28..0.32 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (pj.projectleaderapproverid = id)
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..4.59 rows=38 width=23) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..3.25 rows=1 width=31) (actual rows= loops=)

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

Nested Loop (cost=0.85..2.92 rows=1 width=40) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..2.60 rows=1 width=60) (actual rows= loops=)

45. 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_facts7.timeallocationid7 = id)
46. 0.000 0.000 ↓ 0.0

Index Only Scan using billingrate_pkey on billingrate br (cost=0.14..0.16 rows=1 width=16) (actual rows= loops=)

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

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

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $4) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $5) AND (userid IS NULL)))
48. 0.000 0.000 ↓ 0.0

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.28..0.31 rows=1 width=31) (actual rows= loops=)

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

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

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

Index Scan using currencyinfo_pkey on currencyinfo currencyinfo9 (cost=0.13..0.15 rows=1 width=122) (actual rows= loops=)

  • Index Cond: (pbrh.currencyid = id)
51. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo ui (cost=0.28..1.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = dmv_timesheetday_facts7.userid3)
52. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (userid = ui.id)
  • Filter: (departmentid = ANY ('{1,62,28,8,41,18,61,59,27,47,45,14,49,7,12,52,5,9,3,58,44,53,30,29,2,10,16,56,55,6,24,21,13,32,20,57,4,60,11,51,54,43,48,42,50,22,15,46,17,63,68,71,64,69,66,72,70,65}'::integer[]))
53. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = du.departmentid)