explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mitj

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

Sort (cost=5,983,654.14..5,983,654.16 rows=9 width=4,580) (actual rows= loops=)

  • Sort Key: ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((program.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.code)::character varying(50)) COLLATE "en_US", at.entrydate, tah.timestamputc, ((pj.info2)::character varying(255)) COLLATE "en_US", ts_1.startdate, ts_1.enddate, ((pj.info3)::character varying(255)) COLLATE "en_US", ((tk.info1)::character varying(255)) COLLATE "en_US", ((at.comments)::text) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) 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

Nested Loop Left Join (cost=139,104.25..5,983,652.99 rows=9 width=4,580) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=139,103.83..5,983,648.77 rows=9 width=334) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=139,103.68..5,983,647.11 rows=9 width=338) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=139,103.26..5,983,643.14 rows=9 width=273) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=139,103.12..5,983,641.72 rows=9 width=253) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=139,102.83..5,983,638.97 rows=9 width=169) (actual rows= loops=)

  • Hash Cond: (at.timesheetid = ts_1.id)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4,919.45..5,711,307.00 rows=10,047,163 width=623) (actual rows= loops=)

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

Hash Left Join (cost=1,927.24..320,856.61 rows=10,047,163 width=124) (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))
12. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..243,575.63 rows=10,047,163 width=124) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,927.23 rows=1 width=16) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..1,924.91 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.29..2.31 rows=1 width=16) (actual rows= loops=)

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

Hash (cost=2,726.88..2,726.88 rows=21,226 width=52) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,190.82..2,726.88 rows=21,226 width=52) (actual rows= loops=)

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

Seq Scan on projectclient pc (cost=0.00..480.32 rows=21,226 width=48) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=1,924.91..1,924.91 rows=21,273 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..1,924.91 rows=21,273 width=8) (actual rows= loops=)

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

Hash (cost=134,183.37..134,183.37 rows=1 width=114) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.42..134,183.37 rows=1 width=114) (actual rows= loops=)

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

Nested Loop (cost=2.42..133,126.79 rows=1 width=94) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.14..133,126.49 rows=1 width=84) (actual rows= loops=)

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

Nested Loop (cost=1.71..133,124.03 rows=1 width=88) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..133,123.57 rows=1 width=68) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..133,123.11 rows=1 width=44) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=0.43..133,120.66 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (timestamputc >= '2020-07-24 04:00:00'::timestamp without time zone) AND (timestamputc < '2020-07-25 04:00:00'::timestamp without time zone))
30. 0.000 0.000 ↓ 0.0

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.43..2.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = tah.timesheetid)
  • Filter: (timesheetstatus = 2)
31. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.43..0.46 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = tslist.timesheetid)
32. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts (cost=0.43..0.46 rows=1 width=20) (actual rows= loops=)

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

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

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.29..0.31 rows=1 width=10) (actual rows= loops=)

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

Seq Scan on login (cost=0.00..677.37 rows=30,337 width=24) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.29..0.31 rows=1 width=88) (actual rows= loops=)

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

Index Scan using program_pkey on program (cost=0.14..0.16 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (pj.programid = id)
38. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (at.taskid = id)
39. 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)
40. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.42..0.47 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (tk.id = taskid)