explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iCM5

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

Sort (cost=2,526,492.93..2,526,492.93 rows=2 width=4,014) (actual rows= loops=)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.info19)::character varying(255)) COLLATE "en_US", tah.timestamputc, ((br.name)::character varying(50)) COLLATE "en_US", ((currencyinfo9.symbol)::character varying(50)) COLLATE "en_US", pbrh.hourlyrate, ts_1.startdate, ts_1.enddate, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((cl.code)::character varying(50)) COLLATE "en_US", ((pj.info1)::character varying(255)) COLLATE "en_US
2.          

Initplan (for Sort)

3. 0.000 0.000 ↓ 0.0

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

4. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation (cost=0.00..16.50 rows=650 width=16) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

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

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,324.21..2,526,458.91 rows=2 width=4,014) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Only Scan using program_pkey on program program13 (cost=0.14..1.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = '150aa787-d9ee-41d8-8179-354749dd8e60'::uuid)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,324.07..2,526,457.73 rows=2 width=478) (actual rows= loops=)

  • Join Filter: (ts.userid = login.userid)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,323.79..2,526,457.06 rows=2 width=464) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,323.51..2,526,452.45 rows=2 width=429) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,320.36..2,526,447.67 rows=2 width=394) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,319.80..2,526,441.22 rows=2 width=342) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,319.23..2,526,434.78 rows=2 width=274) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,318.38..2,526,431.73 rows=2 width=246) (actual rows= loops=)

  • Merge Cond: (ts_1.id = ts.id)
15. 0.000 0.000 ↓ 0.0

Merge Join (cost=0.84..43,709.93 rows=453,513 width=40) (actual rows= loops=)

  • Merge Cond: (ts_1.id = tslist.timesheetid)
16. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..14,395.12 rows=453,513 width=24) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..22,512.12 rows=453,513 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Materialize (cost=3,317.54..2,481,587.99 rows=2 width=254) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,317.54..2,481,587.99 rows=2 width=254) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,317.39..2,481,587.62 rows=2 width=258) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,316.97..2,481,586.73 rows=2 width=213) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3,316.69..2,481,586.14 rows=2 width=178) (actual rows= loops=)

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

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..18,930.25 rows=6 width=20) (actual rows= loops=)

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

Materialize (cost=3,316.27..2,450,544.32 rows=138,418 width=158) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,316.27..2,449,852.23 rows=138,418 width=158) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj.id)
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,403.36..2,396,242.04 rows=4,173,730 width=616) (actual rows= loops=)

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

Hash Left Join (cost=970.45..183,461.74 rows=4,173,730 width=85) (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))
28. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..151,188.30 rows=4,173,730 width=85) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=969.90..969.90 rows=37 width=16) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..969.90 rows=37 width=16) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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

  • Filter: (clientbillingallocationmethod = 1)
32. 0.000 0.000 ↓ 0.0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.29..1.62 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (projectid = pj_2.id)
33. 0.000 0.000 ↓ 0.0

Hash (cost=1,299.53..1,299.53 rows=10,670 width=52) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,043.58..1,299.53 rows=10,670 width=52) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..227.84 rows=10,707 width=48) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=908.02..908.02 rows=10,844 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..908.02 rows=10,844 width=8) (actual rows= loops=)

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

Hash (cost=908.02..908.02 rows=391 width=86) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..908.02 rows=391 width=86) (actual rows= loops=)

  • Filter: (programid = '150aa787-d9ee-41d8-8179-354749dd8e60'::uuid)
40. 0.000 0.000 ↓ 0.0

Index Scan using clients_pkey on clients cl (cost=0.28..0.29 rows=1 width=35) (actual rows= loops=)

  • Index Cond: ((CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END) = id)
41. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.42..0.45 rows=1 width=49) (actual rows= loops=)

  • Index Cond: (at.taskid = id)
42. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((variablecurrencyid = $0) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..1.51 rows=1 width=44) (actual rows= loops=)

  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
44. 0.000 0.000 ↓ 0.0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.43..0.60 rows=2 width=48) (actual rows= loops=)

  • Index Cond: (ts_1.id = timesheetid)
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (action = ANY ('{2,3,6}'::integer[])))
45. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.42..0.45 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (tah.timesheetid = timesheetid)
46. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue6 (cost=0.56..3.20 rows=2 width=84) (actual rows= loops=)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
47. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue7 (cost=0.56..3.20 rows=2 width=84) (actual rows= loops=)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
48. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3.15..3.84 rows=13 width=51) (actual rows= loops=)

  • Hash Cond: (pbrh.currencyid = currencyinfo9.id)
49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.99..2.62 rows=13 width=52) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.57..1.73 rows=1 width=60) (actual rows= loops=)

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

Nested Loop (cost=1.14..1.25 rows=1 width=67) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..0.78 rows=1 width=87) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..0.48 rows=1 width=44) (actual rows= loops=)

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

Index Scan using billingrate_pkey on billingrate br (cost=0.28..0.30 rows=1 width=43) (actual rows= loops=)

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

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

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $1) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $2) AND (userid IS NULL)))
56. 0.000 0.000 ↓ 0.0

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.43..0.46 rows=1 width=33) (actual rows= loops=)

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

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

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

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

59. 0.000 0.000 ↓ 0.0

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

60. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (userid = ui.id)