explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x8sJ

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

Unique (cost=3,528.31..3,530.91 rows=260 width=37) (actual rows= loops=)

2.          

CTE timesheet0cte

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..36.70 rows=1 width=76) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..34.65 rows=1 width=178) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..32.77 rows=1 width=182) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..30.48 rows=1 width=182) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest3 (cost=0.28..3.31 rows=2 width=20) (actual rows= loops=)

  • Index Cond: (userid = 2,484)
8. 0.000 0.000 ↓ 0.0

Index Scan using ixtsarkvtsapprovalrequestid on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue1 (cost=0.28..13.57 rows=1 width=170) (actual rows= loops=)

  • Index Cond: (tsapprovalrequestid = tsapprovalrequest3.id)
  • Filter: ((upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR ((upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text) AND (upper(uri) = 'URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text)))
9. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes2 (cost=0.28..2.29 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = tsapprovalrequest3.nodeid)
10. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet timesheet0 (cost=0.42..1.87 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetapprovalnodes2.timesheetid)
11. 0.000 0.000 ↓ 0.0

Index Scan using tsapprovalrequestkeyvalue_pkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue4 (cost=0.28..2.05 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = tsapprovalrequestkeyvalue1.parentid)
  • Filter: (parentid IS NULL)
12.          

CTE timesheet5cte

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=701.11..1,578.65 rows=1 width=76) (actual rows= loops=)

  • Join Filter: ((upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR ((upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text) AND (upper(esheetapprovalhistorykeyvalue7.uri) = 'URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text)))
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=700.55..1,577.76 rows=1 width=99) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=699.98..1,575.98 rows=1 width=99) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=699.42..1,575.19 rows=1 width=28) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=698.86..1,573.49 rows=1 width=28) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=698.30..1,570.78 rows=1 width=28) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=697.73..1,568.20 rows=1 width=28) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=697.31..1,566.11 rows=1 width=32) (actual rows= loops=)

  • Hash Cond: (esheetapprovalhistorykeyvalue9.timesheetapprovalhistoryid = timesheetapprovalhistory8.id)
21. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvuri on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue9 (cost=0.56..867.97 rows=533 width=32) (actual rows= loops=)

  • Index Cond: (upper(uri) = 'URN:REPLICON-TENANT:471,700D8E3A5480F97E7FEF37D80DFA9:USER:2,484'::text)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
22. 0.000 0.000 ↓ 0.0

Hash (cost=687.80..687.80 rows=716 width=32) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory8 (cost=0.42..687.80 rows=716 width=32) (actual rows= loops=)

  • Index Cond: (userid = 2,484)
24. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet timesheet5 (cost=0.42..2.08 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetapprovalhistory8.timesheetid)
25. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue10 (cost=0.56..2.58 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (id = esheetapprovalhistorykeyvalue9.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
26. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue11 (cost=0.56..2.70 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue10.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-METADATA'::text)
27. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue12 (cost=0.56..1.63 rows=7 width=32) (actual rows= loops=)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue11.id)
28. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue6 (cost=0.56..0.79 rows=1 width=103) (actual rows= loops=)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue12.id)
  • Filter: ((upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR (upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text))
29. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.56..1.71 rows=7 width=32) (actual rows= loops=)

  • Index Cond: (parentid = esheetapprovalhistorykeyvalue6.id)
30. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue7 (cost=0.56..0.72 rows=7 width=87) (actual rows= loops=)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue13.id)
31. 0.000 0.000 ↓ 0.0

Sort (cost=1,912.96..1,913.61 rows=260 width=37) (actual rows= loops=)

  • Sort Key: ((CASE WHEN (NOT userinfo14.duplicatename) THEN (((userinfo14.lastname)::text || ', '::text) || (userinfo14.firstname)::text) ELSE ((((((userinfo14.lastname)::text || ', '::text) || (userinfo14.firstname)::text) || ' ('::text) || (login15.loginname)::text) || ')'::text) END)::text) COLLATE "en_US", userinfo14.id, userinfo14.disabled
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,620.11..1,902.53 rows=260 width=37) (actual rows= loops=)

  • Join Filter: (userinfo14.id <> systeminformation16.rootuserid)
33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,620.11..1,893.72 rows=260 width=27) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,619.83..1,806.90 rows=270 width=27) (actual rows= loops=)

  • Join Filter: (timeoffs17.userid = userinfo14.id)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,619.55..1,715.46 rows=270 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,619.27..1,622.30 rows=303 width=4) (actual rows= loops=)

  • Group Key: timeoffs17.userid
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,323.05..1,603.21 rows=6,424 width=4) (actual rows= loops=)

  • Hash Cond: (timeoffs17.id = timeoffs18.id)
39. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs timeoffs17 (cost=0.00..257.66 rows=8,566 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash (cost=1,242.75..1,242.75 rows=6,424 width=4) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs timeoffs18 (cost=0.00..1,242.75 rows=6,424 width=4) (actual rows= loops=)

  • Filter: ((SubPlan 3) OR (SubPlan 4))
42.          

SubPlan (for Seq Scan)

43. 0.000 0.000 ↓ 0.0

CTE Scan on timesheet0cte timesheet19 (cost=0.00..0.06 rows=1 width=0) (actual rows= loops=)

  • Filter: ((timeoffs18.startdate <= enddate2) AND (timeoffs18.enddate >= startdate1) AND (userid0 = timeoffs18.userid) AND ((('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text = upper(key3)) AND ('URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text = upper(uri4))) OR (('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text = upper(key3)) AND (upper(('urn:replicon-tenant:471,700d8e3a5480f97e7fef37d80dfa9:time-off:'::text || COALESCE((timeoffs18.id)::text, ''::text))) = upper(uri4)))))
44. 0.000 0.000 ↓ 0.0

CTE Scan on timesheet5cte timesheet20 (cost=0.00..0.06 rows=1 width=0) (actual rows= loops=)

  • Filter: ((timeoffs18.startdate <= enddate7) AND (timeoffs18.enddate >= startdate6) AND (userid5 = timeoffs18.userid) AND ((('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text = upper(uri8)) AND ('URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text = upper(uri9))) OR (('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text = upper(uri8)) AND (upper(('urn:replicon-tenant:471,700d8e3a5480f97e7fef37d80dfa9:time-off:'::text || COALESCE((timeoffs18.id)::text, ''::text))) = upper(uri9)))))
45. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo21 (cost=0.28..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = timeoffs17.userid)
  • Filter: (employeetypeid = 7)
46. 0.000 0.000 ↓ 0.0

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

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

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

  • Index Cond: (userid = userinfo14.id)