explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9jRs

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

Sort (cost=547,259.70..547,292.36 rows=13,063 width=2,731) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename3, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", dmv_timesheetday_facts5.userstatus4, ((dep.name)::character varying(255)) COLLATE "en_US", ((dep.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((currencyinfo9.symbol)::character varying(50)) COLLATE "en_US", ubrh.hourlyrate, ui.enddate, ((ui.info1)::character varying(255)) COLLATE "en_US
2.          

CTE dmv_timesheetday_facts0cte

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=521,596.96..528,721.55 rows=712,459 width=33) (actual rows= loops=)

  • Group Key: ts.userid, at.entrydate, ui_1.duplicatename, CASE WHEN ui_1.disabled THEN 0 ELSE 1 END, at.projectid
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,579.53..510,910.07 rows=712,459 width=33) (actual rows= loops=)

  • Hash Cond: (ts.userid = ui_1.id)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,461.53..508,918.45 rows=712,459 width=28) (actual rows= loops=)

  • Hash Cond: (at.timesheetid = ts.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,205.79..502,787.83 rows=714,215 width=40) (actual rows= loops=)

  • Hash Cond: (at.projectid = project3.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=823.89..491,333.47 rows=876,839 width=616) (actual rows= loops=)

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

Hash Left Join (cost=321.40..28,715.09 rows=876,839 width=61) (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))
9. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..21,817.39 rows=876,839 width=65) (actual rows= loops=)

  • Filter: (entrydate IS NOT NULL)
10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

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

12. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=456.64..456.64 rows=3,668 width=44) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=369.15..456.64 rows=3,668 width=44) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..77.85 rows=3,668 width=44) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=320.07..320.07 rows=3,926 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..320.07 rows=3,926 width=4) (actual rows= loops=)

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

Hash (cost=339.71..339.71 rows=3,376 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on project project3 (cost=0.00..339.71 rows=3,376 width=4) (actual rows= loops=)

  • Filter: (projectstatuslabelid = ANY ('{cf141220-274e-42c5-9b59-bf2d3e19dcff,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[]))
21. 0.000 0.000 ↓ 0.0

Hash (cost=2,667.55..2,667.55 rows=127,055 width=20) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..2,667.55 rows=127,055 width=20) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=89.11..89.11 rows=2,311 width=6) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_1 (cost=0.00..89.11 rows=2,311 width=6) (actual rows= loops=)

25.          

Initplan (for Sort)

26. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Join (cost=504.33..17,644.08 rows=13,063 width=2,731) (actual rows= loops=)

  • Hash Cond: (ubrh.currencyid = currencyinfo9.id)
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=503.17..17,587.87 rows=13,063 width=213) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.projectid5 = pj.id)
29. 0.000 0.000 ↓ 0.0

Hash Join (cost=143.83..17,194.21 rows=13,063 width=158) (actual rows= loops=)

  • Hash Cond: (dmv_timesheetday_facts5.userid1 = ui.id)
30. 0.000 0.000 ↓ 0.0

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..14,249.18 rows=712,459 width=33) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=143.31..143.31 rows=42 width=137) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.17..143.31 rows=42 width=137) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.03..136.39 rows=42 width=113) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.75..122.38 rows=44 width=89) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.46..107.47 rows=45 width=24) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.19..92.87 rows=46 width=16) (actual rows= loops=)

  • Hash Cond: (ubrh.currencyid = exchangerate.fixedcurrencyid)
37. 0.000 0.000 ↓ 0.0

Seq Scan on userbillingratehistory ubrh (cost=0.00..81.85 rows=2,234 width=12) (actual rows= loops=)

  • Filter: ((('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
38. 0.000 0.000 ↓ 0.0

Hash (cost=2.17..2.17 rows=1 width=4) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..2.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $2) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
40. 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 = ubrh.userid)
41. 0.000 0.000 ↓ 0.0

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

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

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

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

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

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

Hash (cost=310.26..310.26 rows=3,926 width=59) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..310.26 rows=3,926 width=59) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=122) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on currencyinfo currencyinfo9 (cost=0.00..1.07 rows=7 width=122) (actual rows= loops=)