explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5KDS

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

Sort (cost=5,632,164.85..5,632,814.73 rows=259,951 width=953) (actual rows= loops=)

  • Sort Key: ts.startdate, ts.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", tslist.timesheetstatus, ((userinfo7.displayname)::text) COLLATE "en_US
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=853,314.36..5,483,885.02 rows=259,951 width=953) (actual rows= loops=)

  • Hash Cond: (ui.id = userhierarchy8.userid)
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=852,761.61..5,477,161.02 rows=259,951 width=251) (actual rows= loops=)

  • Hash Cond: (timesheet9.id = ts.id)
4. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet timesheet9 (cost=0.00..4,607,561.17 rows=259,127 width=16) (actual rows= loops=)

  • Filter: ((userid = 3,136) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
5.          

SubPlan (for Seq Scan)

6. 0.000 0.000 ↓ 0.0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy10 (cost=0.29..2.31 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((userid = timesheet9.userid) AND (supervisorid = 3,136) AND (startdate <= '2020-07-28'::date))
  • Filter: (enddate >= '2020-07-28'::date)
7. 0.000 0.000 ↓ 0.0

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy10_1 (cost=0.29..5.34 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (supervisorid = 3,136)
  • Filter: ((startdate <= '2020-07-28'::date) AND (enddate >= '2020-07-28'::date))
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..8.74 rows=1 width=0) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using ixtantimesheetid on timesheetapprovalnodes timesheetapprovalnodes12 (cost=0.29..3.32 rows=2 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = timesheet9.id)
10. 0.000 0.000 ↓ 0.0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest11 (cost=0.29..2.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((userid = 3,136) AND (nodeid = timesheetapprovalnodes12.id))
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..14.57 rows=4 width=16) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest11_1 (cost=0.29..5.35 rows=4 width=16) (actual rows= loops=)

  • Index Cond: (userid = 3,136)
13. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes12_1 (cost=0.29..2.31 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = tsapprovalrequest11_1.nodeid)
14. 0.000 0.000 ↓ 0.0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory13 (cost=0.42..4.48 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (timesheetid = timesheet9.id)
  • Filter: (userid = 3,136)
15. 0.000 0.000 ↓ 0.0

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory13_1 (cost=0.42..70.78 rows=69 width=16) (actual rows= loops=)

  • Index Cond: (userid = 3,136)
16. 0.000 0.000 ↓ 0.0

Hash (cost=838,313.13..838,313.13 rows=297,079 width=267) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=358,255.06..838,313.13 rows=297,079 width=267) (actual rows= loops=)

  • Hash Cond: (esheetapprovalhistorykeyvalue5.timesheetapprovalhistoryid = tah.id)
18. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue5 (cost=0.69..439,570.84 rows=928,144 width=87) (actual rows= loops=)

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

Hash (cost=345,836.88..345,836.88 rows=297,079 width=212) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,490.22..345,836.88 rows=297,079 width=212) (actual rows= loops=)

  • Hash Cond: (f.owneruserid = login.userid)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,258.73..344,827.38 rows=296,137 width=205) (actual rows= loops=)

  • Hash Cond: (f.owneruserid = ui.id)
22. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,007.60..343,798.23 rows=296,137 width=171) (actual rows= loops=)

  • Merge Cond: (ts.id = tslist.timesheetid)
23. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,007.18..296,141.51 rows=296,137 width=151) (actual rows= loops=)

  • Merge Cond: (ts.id = f.timesheetid)
24. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,006.76..248,484.78 rows=296,137 width=115) (actual rows= loops=)

  • Merge Cond: (ts.id = tah.timesheetid)
25. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..8,902.48 rows=296,137 width=24) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Materialize (cost=1,006.34..237,282.20 rows=124,781 width=107) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,006.34..236,970.25 rows=124,781 width=107) (actual rows= loops=)

  • Workers Planned: 2
28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.31..221,567.40 rows=51,992 width=107) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5.75..43,192.55 rows=51,992 width=36) (actual rows= loops=)

  • Merge Cond: (tah.timesheetid = tahls.timesheetid)
  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
30. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.42..33,030.66 rows=155,976 width=40) (actual rows= loops=)

  • 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[])))
31. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.42..7,112.13 rows=284,047 width=20) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue4 (cost=0.56..3.41 rows=2 width=87) (actual rows= loops=)

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

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts f (cost=0.42..43,214.64 rows=296,148 width=36) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..43,214.64 rows=296,148 width=20) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=188.28..188.28 rows=5,028 width=34) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..188.28 rows=5,028 width=34) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=168.44..168.44 rows=5,044 width=11) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..168.44 rows=5,044 width=11) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=489.96..489.96 rows=5,023 width=19) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=251.13..489.96 rows=5,023 width=19) (actual rows= loops=)

  • Hash Cond: (userhierarchy8.supervisorid = userinfo7.id)
41. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy8 (cost=0.00..225.63 rows=5,023 width=8) (actual rows= loops=)

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

Hash (cost=188.28..188.28 rows=5,028 width=19) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo7 (cost=0.00..188.28 rows=5,028 width=19) (actual rows= loops=)