explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8j9L

Settings
# exclusive inclusive rows x rows loops node
1. 0.340 13,016.139 ↓ 24.5 49 1

Sort (cost=1,023,027.50..1,023,027.51 rows=2 width=3,391) (actual time=13,016.136..13,016.139 rows=49 loops=1)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts39.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts39.entrydate3, dmv_timesheetday_facts39.timesheetenddate6, dmv_timesheetday_facts39.timesheetstatus7, dmv_timesheetday_facts39.comments4 COLLATE "en_US", ((userinfo42.displayname)::text) COLLATE "en_US", ((br.name)::character varying(50)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US
  • Sort Method: quicksort Memory: 50kB
  • Buffers: shared hit=10,877 read=56,723
2.          

CTE dmv_timesheetday_facts0cte

3. 0.049 12,891.797 ↓ 24.5 49 1

GroupAggregate (cost=1,022,932.07..1,022,932.16 rows=2 width=137) (actual time=12,891.758..12,891.797 rows=49 loops=1)

  • Group Key: ts.userid, at.entrydate, (upper(at.comments)), ui_1.duplicatename, ts_1.enddate, tslist.timesheetstatus, at.projectid, at.taskid, at.id
  • Buffers: shared hit=8,945 read=56,649
4. 0.140 12,891.748 ↓ 24.5 49 1

Sort (cost=1,022,932.07..1,022,932.07 rows=2 width=149) (actual time=12,891.741..12,891.748 rows=49 loops=1)

  • Sort Key: ts.userid, at.entrydate, (upper(at.comments)), ui_1.duplicatename, ts_1.enddate, tslist.timesheetstatus, at.projectid, at.taskid, at.id
  • Sort Method: quicksort Memory: 36kB
  • Buffers: shared hit=8,945 read=56,649
5. 0.190 12,891.608 ↓ 24.5 49 1

Nested Loop (cost=964.60..1,022,932.06 rows=2 width=149) (actual time=11,211.852..12,891.608 rows=49 loops=1)

  • Buffers: shared hit=8,942 read=56,649
6. 2,039.192 12,783.079 ↓ 24.5 49 1

Nested Loop (cost=964.17..1,022,814.83 rows=2 width=117) (actual time=11,208.771..12,783.079 rows=49 loops=1)

  • Join Filter: (at.timesheetid = ts.id)
  • Rows Removed by Join Filter: 25,294,059
  • Buffers: shared hit=8,808 read=56,562
7. 224.240 7,885.491 ↑ 2.4 53,932 1

Hash Join (cost=962.91..1,017,057.46 rows=129,403 width=120) (actual time=14.520..7,885.491 rows=53,932 loops=1)

  • Hash Cond: (at.projectid = project4.id)
  • Buffers: shared hit=1,989 read=55,625
8. 5,302.274 7,657.120 ↓ 1.0 1,686,714 1

Hash Left Join (cost=786.11..995,593.87 rows=1,685,609 width=628) (actual time=4.946..7,657.120 rows=1,686,714 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Rows Removed by Join Filter: 272
  • Buffers: shared hit=1,977 read=55,622
9. 339.893 2,351.725 ↓ 1.0 1,686,714 1

Hash Left Join (cost=317.51..86,860.68 rows=1,685,609 width=125) (actual time=1.569..2,351.725 rows=1,686,714 loops=1)

  • 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))
  • Buffers: shared hit=1,663 read=55,622
10. 2,011.343 2,011.343 ↓ 1.0 1,686,714 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..73,901.09 rows=1,685,609 width=129) (actual time=1.069..2,011.343 rows=1,686,714 loops=1)

  • Buffers: shared hit=1,387 read=55,622
11. 0.001 0.489 ↓ 0.0 0 1

Hash (cost=317.49..317.49 rows=1 width=16) (actual time=0.488..0.489 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=276
12. 0.001 0.488 ↓ 0.0 0 1

Nested Loop (cost=0.28..317.49 rows=1 width=16) (actual time=0.488..0.488 rows=0 loops=1)

  • Buffers: shared hit=276
13. 0.487 0.487 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..315.19 rows=1 width=4) (actual time=0.487..0.487 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 3,165
  • Buffers: shared hit=276
14. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.28..2.30 rows=1 width=16) (never executed)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
15. 0.509 3.121 ↓ 1.0 3,162 1

Hash (cost=429.14..429.14 rows=3,157 width=44) (actual time=3.121..3.121 rows=3,162 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 184kB
  • Buffers: shared hit=303
16. 0.660 2.612 ↓ 1.0 3,162 1

Hash Join (cost=354.38..429.14 rows=3,157 width=44) (actual time=0.950..2.612 rows=3,162 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=303
17. 1.031 1.031 ↓ 1.0 3,162 1

Seq Scan on projectclient pc (cost=0.00..66.46 rows=3,157 width=44) (actual time=0.009..1.031 rows=3,162 loops=1)

  • Buffers: shared hit=27
18. 0.399 0.921 ↓ 1.0 3,165 1

Hash (cost=315.19..315.19 rows=3,135 width=4) (actual time=0.921..0.921 rows=3,165 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=276
19. 0.522 0.522 ↓ 1.0 3,165 1

Seq Scan on project pj_1 (cost=0.00..315.19 rows=3,135 width=4) (actual time=0.002..0.522 rows=3,165 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=276
20. 0.039 4.131 ↑ 1.0 241 1

Hash (cost=173.79..173.79 rows=241 width=4) (actual time=4.131..4.131 rows=241 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=12 read=3
21. 4.092 4.092 ↑ 1.0 241 1

Index Scan using ixprjprojectleaderapproverid on project project4 (cost=0.28..173.79 rows=241 width=4) (actual time=1.568..4.092 rows=241 loops=1)

  • Index Cond: (projectleaderapproverid = 507)
  • Buffers: shared hit=12 read=3
22. 1,618.244 2,858.396 ↓ 234.5 469 53,932

Materialize (cost=1.26..1,875.29 rows=2 width=61) (actual time=0.000..0.053 rows=469 loops=53,932)

  • Buffers: shared hit=6,819 read=937
23. 0.727 1,240.152 ↓ 234.5 469 1

Nested Loop (cost=1.26..1,875.28 rows=2 width=61) (actual time=9.827..1,240.152 rows=469 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=6,819 read=937
24. 0.838 1,138.121 ↓ 234.5 469 1

Nested Loop (cost=0.84..1,874.35 rows=2 width=41) (actual time=8.234..1,138.121 rows=469 loops=1)

  • Buffers: shared hit=5,022 read=858
25. 0.893 557.130 ↓ 234.5 469 1

Nested Loop (cost=0.42..1,869.47 rows=2 width=21) (actual time=5.576..557.130 rows=469 loops=1)

  • Buffers: shared hit=3,568 read=436
26. 0.280 0.280 ↑ 1.0 1,047 1

Seq Scan on userinfo ui_1 (cost=0.00..54.47 rows=1,047 width=5) (actual time=0.004..0.280 rows=1,047 loops=1)

  • Buffers: shared hit=44
27. 555.957 555.957 ↓ 0.0 0 1,047

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..1.72 rows=1 width=20) (actual time=0.528..0.531 rows=0 loops=1,047)

  • Index Cond: ((userid = ui_1.id) AND (startdate >= '2020-06-01'::date) AND (startdate <= '2020-06-19'::date))
  • Filter: ((enddate >= '2020-06-01'::date) AND (enddate <= '2020-06-19'::date))
  • Buffers: shared hit=3,524 read=436
28. 580.153 580.153 ↑ 1.0 1 469

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..2.44 rows=1 width=20) (actual time=1.237..1.237 rows=1 loops=469)

  • Index Cond: (timesheetid = ts.id)
  • Buffers: shared hit=1,454 read=422
29. 101.304 101.304 ↑ 1.0 1 469

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.45 rows=1 width=20) (actual time=0.216..0.216 rows=1 loops=469)

  • Index Cond: (id = tslist.timesheetid)
  • Buffers: shared hit=1,797 read=79
30. 107.534 108.339 ↑ 1.0 1 49

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts ttendancetimeallocation_facts5 (cost=0.43..58.61 rows=1 width=16) (actual time=2.211..2.211 rows=1 loops=49)

  • Index Cond: (id = at.id)
  • Filter: ((userid = 507) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (SubPlan 5) OR (SubPlan 6) OR (SubPlan 7) OR (SubPlan 8))
  • Buffers: shared hit=134 read=87
31.          

SubPlan (for Index Scan)

32. 0.775 0.775 ↓ 0.0 0 5

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy6 (cost=0.28..2.31 rows=1 width=0) (actual time=0.155..0.155 rows=0 loops=5)

  • Index Cond: (supervisorid = 507)
  • Filter: ((startdate <= '2020-06-25'::date) AND (enddate >= '2020-06-25'::date) AND (userid = ttendancetimeallocation_facts5.userid))
  • Buffers: shared hit=9 read=1
33. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy6_1 (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Index Cond: (supervisorid = 507)
  • Filter: ((startdate <= '2020-06-25'::date) AND (enddate >= '2020-06-25'::date))
34. 0.030 0.030 ↑ 1.0 1 5

Index Scan using project_pkey on project project7 (cost=0.28..2.30 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=5)

  • Index Cond: (id = ttendancetimeallocation_facts5.projectid)
  • Filter: (projectleaderapproverid = 507)
  • Buffers: shared hit=15
35. 0.000 0.000 ↓ 0.0 0

Index Scan using ixprjprojectleaderapproverid on project project7_1 (cost=0.28..173.79 rows=241 width=4) (never executed)

  • Index Cond: (projectleaderapproverid = 507)
36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..11.68 rows=1 width=32) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..10.98 rows=1 width=84) (never executed)

  • Join Filter: (tsapprovalrequest11.id = tsapprovalrequestkeyvalue8.tsapprovalrequestid)
38. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..5.67 rows=1 width=4) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..3.22 rows=1 width=20) (never executed)

  • Join Filter: (timesheetapprovalnodes10.id = tsapprovalrequest11.nodeid)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest11 (cost=0.15..2.17 rows=1 width=20) (never executed)

  • Index Cond: (userid = 507)
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on timesheetapprovalnodes timesheetapprovalnodes10 (cost=0.00..1.02 rows=2 width=32) (never executed)

  • Filter: (timesheetid = ttendancetimeallocation_facts5.timesheetid)
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet9 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts5.timesheetid)
  • Heap Fetches: 0
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue8 (cost=0.00..4.26 rows=84 width=88) (never executed)

  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using tsapprovalrequestkeyvalue_pkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue12 (cost=0.14..0.59 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequestkeyvalue8.parentid)
  • Filter: (parentid IS NULL)
45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.89..37.31 rows=1 width=32) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.46..36.79 rows=1 width=16) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.02..33.87 rows=1 width=16) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.58..33.35 rows=1 width=16) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.15..32.28 rows=1 width=16) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.72..31.16 rows=1 width=16) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..28.91 rows=1 width=16) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..8.97 rows=1 width=16) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet14 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts5.timesheetid)
  • Heap Fetches: 0
54. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory15 (cost=0.42..6.52 rows=1 width=32) (never executed)

  • Index Cond: (timesheetid = ttendancetimeallocation_facts5.timesheetid)
  • Filter: (userid = 507)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue16 (cost=0.43..19.93 rows=1 width=32) (never executed)

  • Index Cond: (timesheetapprovalhistoryid = timesheetapprovalhistory15.id)
  • Filter: ((upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text) AND (upper(uri) = 'URN:REPLICON-TENANT:D39C603BA5EE46DA9A42069EEC872CF2:USER:507'::text))
56. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue17 (cost=0.43..2.25 rows=1 width=32) (never executed)

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

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue18 (cost=0.43..1.11 rows=1 width=32) (never executed)

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

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue19 (cost=0.43..1.04 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue18.id)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue20 (cost=0.43..0.51 rows=1 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue19.id)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue21 (cost=0.43..2.90 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue20.id)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..0.50 rows=2 width=92) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue21.id)
62. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..10.63 rows=1 width=32) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..7.96 rows=1 width=84) (never executed)

  • Join Filter: (tsapprovalrequest25.id = tsapprovalrequestkeyvalue22.tsapprovalrequestid)
64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..5.67 rows=1 width=4) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..3.22 rows=1 width=20) (never executed)

  • Join Filter: (timesheetapprovalnodes24.id = tsapprovalrequest25.nodeid)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest25 (cost=0.15..2.17 rows=1 width=20) (never executed)

  • Index Cond: (userid = 507)
67. 0.000 0.000 ↓ 0.0 0

Seq Scan on timesheetapprovalnodes timesheetapprovalnodes24 (cost=0.00..1.02 rows=2 width=32) (never executed)

  • Filter: (timesheetid = ttendancetimeallocation_facts5.timesheetid)
68. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet23 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts5.timesheetid)
  • Heap Fetches: 0
69. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsarkvkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue22 (cost=0.27..2.28 rows=1 width=88) (never executed)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using tsapprovalrequestkeyvalue_pkey on tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue26 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequestkeyvalue22.parentid)
  • Filter: (parentid IS NULL)
71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.89..37.87 rows=1 width=32) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.46..37.35 rows=1 width=16) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.02..33.87 rows=1 width=16) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.58..33.35 rows=1 width=16) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.15..32.28 rows=1 width=16) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.72..31.16 rows=1 width=16) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..28.91 rows=1 width=16) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..8.97 rows=1 width=16) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet timesheet28 (cost=0.42..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ttendancetimeallocation_facts5.timesheetid)
  • Heap Fetches: 0
80. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory29 (cost=0.42..6.52 rows=1 width=32) (never executed)

  • Index Cond: (timesheetid = ttendancetimeallocation_facts5.timesheetid)
  • Filter: (userid = 507)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue30 (cost=0.43..19.93 rows=1 width=32) (never executed)

  • Index Cond: (timesheetapprovalhistoryid = timesheetapprovalhistory29.id)
  • Filter: ((upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text) AND (upper(uri) = 'URN:REPLICON-TENANT:D39C603BA5EE46DA9A42069EEC872CF2:USER:507'::text))
82. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue31 (cost=0.43..2.25 rows=1 width=32) (never executed)

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

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue32 (cost=0.43..1.11 rows=1 width=32) (never executed)

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

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue33 (cost=0.43..1.04 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue32.id)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue34 (cost=0.43..0.51 rows=1 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue33.id)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue35 (cost=0.43..3.46 rows=2 width=32) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue34.id)
87. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahkvparentid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue27 (cost=0.43..0.50 rows=2 width=92) (never executed)

  • Index Cond: (parentid = sheetapprovalhistorykeyvalue35.id)
88.          

Initplan (for Sort)

89. 0.004 0.004 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
90. 0.005 0.005 ↑ 650.0 1 1

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

  • Buffers: shared hit=1
91. 0.001 0.001 ↑ 650.0 1 1

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

  • Buffers: shared hit=1
92. 0.089 13,015.789 ↓ 24.5 49 1

Nested Loop Left Join (cost=19.75..61.33 rows=2 width=3,391) (actual time=12,927.601..13,015.789 rows=49 loops=1)

  • Buffers: shared hit=10,863 read=56,723
93. 0.049 12,990.318 ↓ 24.5 49 1

Nested Loop Left Join (cost=19.46..60.63 rows=2 width=775) (actual time=12,926.470..12,990.318 rows=49 loops=1)

  • Buffers: shared hit=10,735 read=56,704
94. 0.032 12,990.171 ↓ 24.5 49 1

Nested Loop Left Join (cost=19.18..60.01 rows=2 width=765) (actual time=12,926.457..12,990.171 rows=49 loops=1)

  • Buffers: shared hit=10,588 read=56,704
95. 0.118 12,903.850 ↓ 24.5 49 1

Hash Join (cost=17.36..49.41 rows=2 width=756) (actual time=12,902.690..12,903.850 rows=49 loops=1)

  • Hash Cond: (du.userid = dmv_timesheetday_facts39.userid2)
  • Buffers: shared hit=9,605 read=56,656
96. 0.162 8.753 ↑ 1.0 825 1

Hash Join (cost=2.45..31.32 rows=844 width=520) (actual time=6.809..8.753 rows=825 loops=1)

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=55 read=5
97. 8.569 8.569 ↑ 1.0 825 1

Index Only Scan using departmentusers_pkey on departmentusers du (cost=0.28..26.75 rows=844 width=8) (actual time=6.779..8.569 rows=825 loops=1)

  • Index Cond: (departmentid = ANY ('{15,40,39,55,18,53,51,54,47,49,11,13,50,6,10,30,35,32,25,27,29,23,34,36,41}'::integer[]))
  • Heap Fetches: 269
  • Buffers: shared hit=54 read=5
98. 0.011 0.022 ↑ 1.0 52 1

Hash (cost=1.52..1.52 rows=52 width=520) (actual time=0.022..0.022 rows=52 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
99. 0.011 0.011 ↑ 1.0 52 1

Seq Scan on departments dep (cost=0.00..1.52 rows=52 width=520) (actual time=0.005..0.011 rows=52 loops=1)

  • Buffers: shared hit=1
100. 0.036 12,894.979 ↓ 24.5 49 1

Hash (cost=14.88..14.88 rows=2 width=244) (actual time=12,894.979..12,894.979 rows=49 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=9,550 read=56,651
101. 0.057 12,894.943 ↓ 24.5 49 1

Nested Loop (cost=1.27..14.88 rows=2 width=244) (actual time=12,891.843..12,894.943 rows=49 loops=1)

  • Join Filter: (dmv_timesheetday_facts39.userid2 = login.userid)
  • Buffers: shared hit=9,550 read=56,651
102. 0.063 12,894.837 ↓ 24.5 49 1

Nested Loop (cost=1.00..14.22 rows=2 width=231) (actual time=12,891.829..12,894.837 rows=49 loops=1)

  • Buffers: shared hit=9,403 read=56,651
103. 0.042 12,894.725 ↓ 24.5 49 1

Nested Loop Left Join (cost=0.72..9.63 rows=2 width=192) (actual time=12,891.814..12,894.725 rows=49 loops=1)

  • Buffers: shared hit=9,256 read=56,651
104. 0.033 12,894.634 ↓ 24.5 49 1

Nested Loop Left Join (cost=0.57..9.26 rows=2 width=196) (actual time=12,891.792..12,894.634 rows=49 loops=1)

  • Buffers: shared hit=9,237 read=56,651
105. 0.024 12,891.955 ↓ 24.5 49 1

Nested Loop Left Join (cost=0.28..4.64 rows=2 width=166) (actual time=12,891.777..12,891.955 rows=49 loops=1)

  • Buffers: shared hit=9,092 read=56,649
106. 12,891.833 12,891.833 ↓ 24.5 49 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts39 (cost=0.00..0.04 rows=2 width=105) (actual time=12,891.762..12,891.833 rows=49 loops=1)

  • Buffers: shared hit=8,945 read=56,649
107. 0.098 0.098 ↑ 1.0 1 49

Index Scan using project_pkey on project pj (cost=0.28..2.30 rows=1 width=65) (actual time=0.002..0.002 rows=1 loops=49)

  • Index Cond: (dmv_timesheetday_facts39.projectid8 = id)
  • Buffers: shared hit=147
108. 2.646 2.646 ↑ 1.0 1 49

Index Scan using task_pkey on task tk (cost=0.29..2.31 rows=1 width=34) (actual time=0.054..0.054 rows=1 loops=49)

  • Index Cond: (dmv_timesheetday_facts39.taskid9 = id)
  • Buffers: shared hit=145 read=2
109. 0.049 0.049 ↓ 0.0 0 49

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

  • Index Cond: ((variablecurrencyid = $34) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
  • Buffers: shared hit=18
110. 0.049 0.049 ↑ 1.0 1 49

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.29 rows=1 width=39) (actual time=0.001..0.001 rows=1 loops=49)

  • Index Cond: (id = dmv_timesheetday_facts39.userid2)
  • Buffers: shared hit=147
111. 0.049 0.049 ↑ 1.0 1 49

Index Scan using login_pkey on login (cost=0.28..0.32 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=49)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=147
112. 0.049 86.289 ↑ 37.0 1 49

Nested Loop (cost=1.82..4.93 rows=37 width=41) (actual time=1.707..1.761 rows=1 loops=49)

  • Buffers: shared hit=983 read=48
113. 0.098 80.017 ↑ 1.0 1 49

Nested Loop (cost=1.41..3.53 rows=1 width=49) (actual time=1.579..1.633 rows=1 loops=49)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=649 read=43
114. 0.049 21.658 ↑ 1.0 1 49

Nested Loop (cost=1.11..3.19 rows=1 width=65) (actual time=0.441..0.442 rows=1 loops=49)

  • Buffers: shared hit=525 read=16
115. 0.049 0.441 ↑ 1.0 1 49

Nested Loop (cost=0.70..2.74 rows=1 width=85) (actual time=0.008..0.009 rows=1 loops=49)

  • Buffers: shared hit=343
116. 0.294 0.294 ↑ 1.0 1 49

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..2.45 rows=1 width=44) (actual time=0.005..0.006 rows=1 loops=49)

  • Index Cond: (dmv_timesheetday_facts39.timeallocationid10 = id)
  • Buffers: shared hit=196
117. 0.098 0.098 ↑ 1.0 1 49

Index Scan using billingrate_pkey on billingrate br (cost=0.27..0.29 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=49)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
  • Buffers: shared hit=147
118. 21.168 21.168 ↑ 1.0 1 49

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.41..0.44 rows=1 width=40) (actual time=0.432..0.432 rows=1 loops=49)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $35) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $36) AND (userid IS NULL)))
  • Buffers: shared hit=182 read=16
119. 58.261 58.261 ↓ 2.0 2 49

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.29..0.33 rows=1 width=24) (actual time=1.137..1.189 rows=2 loops=49)

  • Index Cond: (projectbillingrateid = pbr.id)
  • Buffers: shared hit=124 read=27
120. 6.223 6.223 ↑ 17.0 1 49

Index Only Scan using ixtsuseridstartdateenddate on timesheet dmvts (cost=0.42..1.23 rows=17 width=12) (actual time=0.127..0.127 rows=1 loops=49)

  • Index Cond: ((userid = dm_attendancetimeallocation_facts.userid) AND (startdate <= dm_attendancetimeallocation_facts.entrydate) AND (enddate >= dm_attendancetimeallocation_facts.entrydate))
  • Heap Fetches: 49
  • Buffers: shared hit=334 read=5
121. 0.098 0.098 ↑ 1.0 1 49

Index Scan using userinfo_pkey on userinfo userinfo42 (cost=0.28..0.31 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=49)

  • Index Cond: (pj.projectleaderapproverid = id)
  • Buffers: shared hit=147
122. 25.382 25.382 ↑ 1.0 1 49

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..0.35 rows=1 width=63) (actual time=0.518..0.518 rows=1 loops=49)

  • Index Cond: (tk.id = taskid)
  • Buffers: shared hit=128 read=19
Planning time : 22.988 ms
Execution time : 13,017.296 ms