explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NS5F

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 5,236,342.465 ↓ 0.0 0 1

Sort (cost=716,838.59..716,838.59 rows=1 width=2,856) (actual time=5,236,342.465..5,236,342.465 rows=0 loops=1)

  • Output: ((cl.name)::character varying(255)), cl.id, ((pj.code)::character varying(50)), pj.id, ((pj.name)::character varying(255)), ((tk.name)::character varying(255)), tk.id, ((ui.info5)::character varying(255)), ui.id, ((ui.externalid)::character varying(255)), "*SELECT* 1".entrydate, ((billingrate5.name)::character varying(50)), "*SELECT* 1".workduration, "*SELECT* 1".userid
  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((ui.info5)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ((billingrate5.name)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,511,350,297 read=9
2.          

Initplan (for Sort)

3. 0.007 0.007 ↑ 1.0 1 1

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

  • Output: systeminformation.basecurrencyid
  • Buffers: shared hit=1
4. 0.001 5,236,342.438 ↓ 0.0 0 1

Nested Loop Semi Join (cost=256,926.00..716,837.57 rows=1 width=2,856) (actual time=5,236,342.437..5,236,342.438 rows=0 loops=1)

  • Output: cl.name, cl.id, pj.code, pj.id, pj.name, tk.name, tk.id, ui.info5, ui.id, ui.externalid, "*SELECT* 1".entrydate, billingrate5.name, "*SELECT* 1".workduration, "*SELECT* 1".userid
  • Buffers: shared hit=2,511,350,291 read=9
5. 0.001 5,236,342.437 ↓ 0.0 0 1

Nested Loop Semi Join (cost=256,925.85..716,836.40 rows=1 width=186) (actual time=5,236,342.437..5,236,342.437 rows=0 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, pj.code, pj.id, pj.name, cl.name, cl.id, tk.name, tk.id, ui.info5, ui.id, ui.externalid, billingrate5.name
  • Buffers: shared hit=2,511,350,291 read=9
6. 0.021 5,236,342.436 ↓ 0.0 0 1

Nested Loop Semi Join (cost=256,925.57..716,834.09 rows=1 width=186) (actual time=5,236,342.436..5,236,342.436 rows=0 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, pj.code, pj.id, pj.name, cl.name, cl.id, tk.name, tk.id, ui.info5, ui.id, ui.externalid, billingrate5.name
  • Buffers: shared hit=2,511,350,291 read=9
7. 0.039 5,236,342.280 ↓ 15.0 15 1

Nested Loop Semi Join (cost=256,925.01..716,831.46 rows=1 width=186) (actual time=206,726.273..5,236,342.280 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, pj.code, pj.id, pj.name, cl.name, cl.id, tk.name, tk.id, ui.info5, ui.id, ui.externalid, billingrate5.name
  • Buffers: shared hit=2,511,350,230 read=9
8. 0.034 5,236,342.181 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,924.73..716,830.15 rows=1 width=186) (actual time=206,726.255..5,236,342.181 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, pj.code, pj.id, pj.name, cl.name, cl.id, tk.name, tk.id, ui.info5, ui.id, ui.externalid, billingrate5.name
  • Inner Unique: true
  • Buffers: shared hit=2,511,350,199 read=9
9. 0.046 5,236,342.072 ↓ 15.0 15 1

Nested Loop (cost=256,924.46..716,829.86 rows=1 width=177) (actual time=206,726.242..5,236,342.072 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, pj.code, pj.id, pj.name, cl.name, cl.id, tk.name, tk.id, ui.info5, ui.id, ui.externalid
  • Inner Unique: true
  • Buffers: shared hit=2,511,350,154 read=9
10. 0.112 5,236,341.951 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,924.18..716,829.57 rows=1 width=156) (actual time=206,726.228..5,236,341.951 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, pj.code, pj.id, pj.name, ts.userid, cl.name, cl.id, tk.name, tk.id
  • Buffers: shared hit=2,511,350,109 read=9
11. 0.048 5,236,341.749 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,924.04..716,829.38 rows=1 width=160) (actual time=206,726.194..5,236,341.749 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, pj.code, pj.id, pj.name, ts.userid, cl.name, cl.id, tk.name, tk.id, tk.estimatedcostcurrencyid
  • Inner Unique: true
  • Buffers: shared hit=2,511,350,078 read=9
12. 0.048 5,236,341.671 ↓ 15.0 15 1

Nested Loop (cost=256,923.62..716,828.89 rows=1 width=134) (actual time=206,726.185..5,236,341.671 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, task9.id, pj.code, pj.id, pj.name, ts.userid, cl.name, cl.id
  • Inner Unique: true
  • Join Filter: (cl.id = COALESCE(clients12.id, pj.clientid))
  • Buffers: shared hit=2,511,350,018 read=9
13. 0.023 5,236,341.578 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,923.48..716,826.72 rows=1 width=118) (actual time=206,726.171..5,236,341.578 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, task9.id, pj.code, pj.id, pj.name, pj.clientid, ts.userid, clients12.id
  • Inner Unique: true
  • Buffers: shared hit=2,511,349,988 read=9
14. 0.038 5,236,341.540 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,923.32..716,826.53 rows=1 width=177) (actual time=206,726.165..5,236,341.540 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".billingrateid, task9.id, pj.code, pj.id, pj.name, pj.clientid, ts.userid, timeentrymetadata11.uri
  • Buffers: shared hit=2,511,349,988 read=9
15. 0.038 5,236,341.412 ↓ 15.0 15 1

Nested Loop (cost=256,922.89..716,825.54 rows=1 width=130) (actual time=206,726.151..5,236,341.412 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, task9.id, pj.code, pj.id, pj.name, pj.clientid, ts.userid
  • Inner Unique: true
  • Buffers: shared hit=2,511,349,928 read=9
16. 251.995 5,236,340.009 ↓ 15.0 15 1

Nested Loop Left Join (cost=256,922.47..716,823.10 rows=1 width=146) (actual time=206,726.126..5,236,340.009 rows=15 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, task9.id, pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid
  • Inner Unique: true
  • Filter: (pj.id = COALESCE(project7.id, task9.projectid))
  • Rows Removed by Filter: 2,758
  • Buffers: shared hit=2,511,349,869 read=8
17. 4.006 5,236,074.149 ↓ 49.5 2,773 1

Nested Loop Left Join (cost=256,922.04..716,797.16 rows=56 width=209) (actual time=15,430.446..5,236,074.149 rows=2,773 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, project7.id, timeentrymetadata8.uri, pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid
  • Buffers: shared hit=2,511,339,613 read=8
18. 15.453 5,236,053.505 ↓ 50.4 2,773 1

Nested Loop Left Join (cost=256,921.60..716,742.84 rows=55 width=146) (actual time=15,430.426..5,236,053.505 rows=2,773 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, project7.id, pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid
  • Inner Unique: true
  • Buffers: shared hit=2,511,328,492 read=8
19. 5.234 5,236,035.279 ↓ 50.4 2,773 1

Nested Loop Left Join (cost=256,921.31..716,725.79 rows=55 width=205) (actual time=15,430.419..5,236,035.279 rows=2,773 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, timeentrymetadata6.uri, pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid
  • Buffers: shared hit=2,511,328,279 read=8
20. 32,446.337 5,235,988.450 ↓ 50.4 2,773 1

Nested Loop (cost=256,920.88..716,671.48 rows=55 width=142) (actual time=15,430.384..5,235,988.450 rows=2,773 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid
  • Join Filter: (("*SELECT* 1".entrydate >= ts.startdate) AND ("*SELECT* 1".entrydate <= ts.enddate) AND (ts.userid = "*SELECT* 1".userid))
  • Rows Removed by Join Filter: 397,606,913
  • Buffers: shared hit=2,511,317,158 read=8
21. 0.627 12.773 ↓ 237.0 237 1

Nested Loop (cost=0.70..2,347.79 rows=1 width=94) (actual time=1.852..12.773 rows=237 loops=1)

  • Output: pj.code, pj.id, pj.name, pj.clientid, ts.id, ts.userid, ts.startdate, ts.enddate
  • Buffers: shared hit=1,521 read=7
22. 10.013 10.013 ↓ 237.0 237 1

Index Scan using uix2tsuseridstartdate on d39c603ba5ee46da9a42069eec872cf2.timesheet ts (cost=0.42..2,345.48 rows=1 width=28) (actual time=1.837..10.013 rows=237 loops=1)

  • Output: ts.id, ts.userid, ts.startdate, ts.enddate, ts.approvalstatus, ts.duedate, ts.autosubmitdatetimeutc, ts.lastautosubmitattemptdatetimeutc, ts.createdonutc
  • Index Cond: ((ts.startdate >= '2020-08-15'::date) AND (ts.startdate <= '2020-08-21'::date))
  • Filter: ((ts.enddate >= '2020-08-15'::date) AND (ts.enddate <= '2020-08-21'::date))
  • Buffers: shared hit=810 read=7
23. 2.133 2.133 ↑ 1.0 1 237

Index Scan using project_pkey on d39c603ba5ee46da9a42069eec872cf2.project pj (cost=0.28..2.30 rows=1 width=66) (actual time=0.007..0.009 rows=1 loops=237)

  • Output: pj.code, pj.id, pj.name, pj.clientid
  • Index Cond: (pj.id = 2,805)
  • Buffers: shared hit=711
24. 43,549.935 5,203,529.340 ↓ 3.4 1,677,678 237

Append (cost=256,920.18..705,579.11 rows=499,690 width=56) (actual time=6.222..21,955.820 rows=1,677,678 loops=237)

  • Buffers: shared hit=2,511,315,637 read=1
25. 269,581.597 5,158,460.235 ↓ 3.4 1,674,623 237

Subquery Scan on *SELECT* 1 (cost=256,920.18..660,616.60 rows=497,398 width=56) (actual time=6.222..21,765.655 rows=1,674,623 loops=237)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".workduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid
  • Filter: ((hashed SubPlan 2) OR (hashed SubPlan 3))
  • Buffers: shared hit=2,511,297,094
26. 3,702,087.888 4,888,334.271 ↓ 2.5 1,674,623 237

Nested Loop (cost=212,181.59..605,930.06 rows=663,197 width=140) (actual time=2.126..20,625.883 rows=1,674,623 loops=237)

  • Output: te.id, NULL::integer, te.userid, te.entrydate, NULL::time without time zone, NULL::time without time zone, NULL::interval, CASE WHEN (tembillingrate.uri IS NULL) THEN NULL::uuid WHEN (tembillingrate.uri = 'urn:replicon:project-specific-billing-rate'::text) THEN psi.projectcustombillingrateid WHEN (tembillingrate.uri = 'urn:replicon:user-specific-billing-rate'::text) THEN psi.usercustombillingrateid ELSE ("substring"(tembillingrate.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':company-billing-rate:(.*)$')))::uuid END, NULL::integer, NULL::interval, NULL::integer, CASE WHEN (temdbreaktype.uri IS NULL) THEN CASE WHEN (te.hours IS NOT NULL) THEN te.hours WHEN ((te.intime IS NULL) OR (te.outtime IS NULL)) THEN NULL::interval WHEN ((te.outtime - te.intime) >= '00:00:00'::interval) THEN (te.outtime - te.intime) ELSE (('24:00:00'::interval + (te.outtime)::interval) - (te.intime)::interval) END ELSE NULL::interval END, NULL::interval, NULL::integer, NULL::integer
  • Buffers: shared hit=2,511,287,141
27. 0.474 0.474 ↑ 1.0 1 237

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.projectsysteminformation psi (cost=0.00..1.01 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=237)

  • Output: psi.usercustombillingrateid, psi.projectcustombillingrateid, psi.defaultprojectstatuslabelid, psi.defaultinvoicecompanyaddressid, psi.defaultbillingratesfornewclients, psi.billingprocesstype, psi.projectworkflowmode, psi.defaultinvoicetemplateid, psi.resourcingdatasource, psi.projectnameunique, psi.projectcoderequiredandunique, psi.clientnameunique, psi.clientcoderequiredandunique
  • Buffers: shared hit=237
28. 65,464.140 1,186,245.909 ↓ 2.5 1,674,623 237

Nested Loop (cost=212,181.59..577,743.18 rows=663,197 width=215) (actual time=2.123..5,005.257 rows=1,674,623 loops=237)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, tembillingrate.uri, temdbreaktype.uri, si.tenantslug
  • Buffers: shared hit=2,511,286,904
29. 0.474 0.474 ↑ 1.0 1 237

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.systeminformation si (cost=0.00..1.01 rows=1 width=33) (actual time=0.001..0.002 rows=1 loops=237)

  • Output: si.rootuserid, si.basecurrencyid, si.basecurrencysymbol, si.defaulttimesheetapprovalpathid, si.defaultexpenseapprovalpathid, si.defaulttimeoffapprovalpathid, si.defaultlanguageid, si.buildnumber, si.isreleaseversion, si.systemguid, si.systemtimezoneid, si.databaseguid, si.uncategorizedskillcategoryid, si.newuserdefaulttimezoneid, si.defaultholidaycalendarid, si.defaultofficescheduleid, si.customlogouri, si.tenantslug, si.databaseupdatescriptstage, si.defaulttimesheetperiodid, si.defaulttimeentryapprovalpathid
  • Buffers: shared hit=237
30. 0.000 1,120,781.295 ↓ 2.5 1,674,623 237

Gather (cost=212,181.59..571,110.20 rows=663,197 width=182) (actual time=2.120..4,729.035 rows=1,674,623 loops=237)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, tembillingrate.uri, temdbreaktype.uri
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2,511,286,667
31. 31,519.980 1,332,965.973 ↓ 2.0 558,208 711 / 3

Hash Left Join (cost=211,181.59..503,790.50 rows=276,332 width=182) (actual time=316.444..5,624.329 rows=558,208 loops=711)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, tembillingrate.uri, temdbreaktype.uri
  • Hash Cond: (te.id = temdbreaktype.timeentryid)
  • Buffers: shared hit=2,511,286,667
  • Worker 0: actual time=473.643..6608.455 rows=650,140 loops=237
  • Buffers: shared hit=972,157,247
  • Worker 1: actual time=473.602..6672.071 rows=657,205 loops=237
  • Buffers: shared hit=979,965,664
32. 808.881 1,226,474.526 ↓ 2.0 558,208 711 / 3

Nested Loop Left Join (cost=0.43..291,573.08 rows=276,332 width=119) (actual time=0.066..5,174.998 rows=558,208 loops=711)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, tembillingrate.uri
  • Buffers: shared hit=2,442,231,675
  • Worker 0: actual time=0.090..5980.060 rows=650,140 loops=237
  • Buffers: shared hit=937,702,424
  • Worker 1: actual time=0.093..6042.045 rows=657,205 loops=237
  • Buffers: shared hit=945,510,841
33. 35,008.692 35,008.692 ↓ 2.0 558,208 711 / 3

Parallel Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeentry te (cost=0.00..49,520.07 rows=276,332 width=56) (actual time=0.005..147.716 rows=558,208 loops=711)

  • Output: te.id, te.userid, te.entrydate, te.timeallocationtype, te.hours, te.intime, te.outtime, te.approvalstatus
  • Filter: (((te.timeallocationtype <> 2) OR (te.timeallocationtype IS NULL)) AND (CASE WHEN (te.hours IS NOT NULL) THEN te.hours WHEN ((te.intime IS NULL) OR (te.outtime IS NULL)) THEN NULL::interval WHEN ((te.outtime - te.intime) >= '00:00:00'::interval) THEN (te.outtime - te.intime) ELSE (('24:00:00'::interval + (te.outtime)::interval) - (te.intime)::interval) END >= '00:00:00.036'::interval))
  • Rows Removed by Filter: 105,056
  • Buffers: shared hit=5,350,275
  • Worker 0: actual time=0.005..175.795 rows=650,140 loops=237
  • Buffers: shared hit=2,224,573
  • Worker 1: actual time=0.005..178.767 rows=657,205 loops=237
  • Buffers: shared hit=2,282,078
34. 1,190,656.953 1,190,656.953 ↑ 1.0 1 396,885,651 / 3

Index Scan using ixtemtimeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata tembillingrate (cost=0.43..0.87 rows=1 width=79) (actual time=0.005..0.009 rows=1 loops=396,885,651)

  • Output: tembillingrate.id, tembillingrate.timeentryid, tembillingrate.parentid, tembillingrate.index, tembillingrate.key, tembillingrate.uri, tembillingrate.slug, tembillingrate."boolean", tembillingrate.date, tembillingrate.number, tembillingrate.text, tembillingrate."time", tembillingrate.timespan, tembillingrate.daterange_startdate, tembillingrate.daterange_enddate, tembillingrate.daterange_relativedaterangeuri, tembillingrate.daterange_relativedaterangeasofdate, tembillingrate.workdayduration_decimalworkdays, tembillingrate.workdayduration_workdays, tembillingrate.workdayduration_hours, tembillingrate.workdayduration_minutes
  • Index Cond: (tembillingrate.timeentryid = te.id)
  • Filter: (upper(tembillingrate.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=2,436,881,400
  • Worker 0: actual time=0.006..0.008 rows=1 loops=154,083,112
  • Buffers: shared hit=935,477,851
  • Worker 1: actual time=0.006..0.008 rows=1 loops=155,757,669
  • Buffers: shared hit=943,228,763
35. 0.158 74,971.467 ↓ 0.0 0 475 / 3

Hash (cost=211,181.15..211,181.15 rows=1 width=79) (actual time=473.504..473.504 rows=0 loops=475)

  • Output: temdbreaktype.uri, temdbreaktype.timeentryid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=69,039,824
  • Worker 0: actual time=473.490..473.490 rows=0 loops=237
  • Buffers: shared hit=34,447,239
  • Worker 1: actual time=473.445..473.445 rows=0 loops=237
  • Buffers: shared hit=34,447,239
36. 74,971.308 74,971.308 ↓ 0.0 0 475 / 3

Index Scan using ixtem2timeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata temdbreaktype (cost=0.56..211,181.15 rows=1 width=79) (actual time=473.503..473.503 rows=0 loops=475)

  • Output: temdbreaktype.uri, temdbreaktype.timeentryid
  • Index Cond: (upper(temdbreaktype.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
  • Buffers: shared hit=69,039,824
  • Worker 0: actual time=473.489..473.489 rows=0 loops=237
  • Buffers: shared hit=34,447,239
  • Worker 1: actual time=473.444..473.444 rows=0 loops=237
  • Buffers: shared hit=34,447,239
37.          

SubPlan (for Subquery Scan)

38. 543.254 543.254 ↑ 1.0 1,989,790 2

Index Only Scan using timeentry_pkey on d39c603ba5ee46da9a42069eec872cf2.timeentry timeentry18 (cost=0.43..39,713.28 rows=1,989,790 width=16) (actual time=0.044..271.627 rows=1,989,790 loops=2)

  • Output: timeentry18.id
  • Heap Fetches: 0
  • Buffers: shared hit=19,906
39. 1.113 1.113 ↑ 1.0 2,260 1

Index Only Scan using timeoffs_pkey on d39c603ba5ee46da9a42069eec872cf2.timeoffs timeoffs19 (cost=0.28..45.18 rows=2,260 width=4) (actual time=0.824..1.113 rows=2,260 loops=1)

  • Output: timeoffs19.id
  • Heap Fetches: 0
  • Buffers: shared hit=13 read=1
40. 1,216.521 1,519.170 ↓ 1.3 3,055 237

Subquery Scan on *SELECT* 2 (cost=44,834.43..44,962.52 rows=2,292 width=56) (actual time=4.179..6.410 rows=3,055 loops=237)

  • Output: "*SELECT* 2".entrydate, "*SELECT* 2".workduration, "*SELECT* 2".userid, "*SELECT* 2".timeentryid, "*SELECT* 2".billingrateid
  • Filter: ((hashed SubPlan 2) OR (hashed SubPlan 3))
  • Buffers: shared hit=18,543 read=1
41. 190.313 302.649 ↑ 1.0 3,055 237

Hash Join (cost=95.85..178.10 rows=3,056 width=140) (actual time=0.011..1.277 rows=3,055 loops=237)

  • Output: NULL::uuid, toff.id, toff.userid, toe.entrydate, NULL::time without time zone, NULL::time without time zone, NULL::interval, NULL::uuid, NULL::integer, NULL::interval, NULL::integer, NULL::interval, NULL::interval, NULL::integer, NULL::integer
  • Inner Unique: true
  • Hash Cond: (toe.timeoffid = toff.id)
  • Buffers: shared hit=8,577
42. 111.627 111.627 ↑ 1.0 3,055 237

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeoffentries toe (cost=0.00..74.21 rows=3,056 width=8) (actual time=0.005..0.471 rows=3,055 loops=237)

  • Output: toe.id, toe.timeoffid, toe.entrydate, toe.duration, toe.timein, toe.timeout, toe.isreportable, toe.workdayduration, toe.entryid
  • Filter: (toe.duration >= '00:00:00.036'::interval)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=8,532
43. 0.283 0.709 ↑ 1.0 2,260 1

Hash (cost=67.60..67.60 rows=2,260 width=8) (actual time=0.709..0.709 rows=2,260 loops=1)

  • Output: toff.id, toff.userid
  • Buckets: 4,096 Batches: 1 Memory Usage: 121kB
  • Buffers: shared hit=45
44. 0.426 0.426 ↑ 1.0 2,260 1

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeoffs toff (cost=0.00..67.60 rows=2,260 width=8) (actual time=0.005..0.426 rows=2,260 loops=1)

  • Output: toff.id, toff.userid
  • Buffers: shared hit=45
45. 41.595 41.595 ↓ 0.0 0 2,773

Index Scan using ixtemtimeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata timeentrymetadata6 (cost=0.43..0.98 rows=1 width=79) (actual time=0.015..0.015 rows=0 loops=2,773)

  • Output: timeentrymetadata6.id, timeentrymetadata6.timeentryid, timeentrymetadata6.parentid, timeentrymetadata6.index, timeentrymetadata6.key, timeentrymetadata6.uri, timeentrymetadata6.slug, timeentrymetadata6."boolean", timeentrymetadata6.date, timeentrymetadata6.number, timeentrymetadata6.text, timeentrymetadata6."time", timeentrymetadata6.timespan, timeentrymetadata6.daterange_startdate, timeentrymetadata6.daterange_enddate, timeentrymetadata6.daterange_relativedaterangeuri, timeentrymetadata6.daterange_relativedaterangeasofdate, timeentrymetadata6.workdayduration_decimalworkdays, timeentrymetadata6.workdayduration_workdays, timeentrymetadata6.workdayduration_hours, timeentrymetadata6.workdayduration_minutes
  • Index Cond: ("*SELECT* 1".timeentryid = timeentrymetadata6.timeentryid)
  • Filter: (upper(timeentrymetadata6.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=11,121
46. 2.773 2.773 ↓ 0.0 0 2,773

Index Only Scan using project_pkey on d39c603ba5ee46da9a42069eec872cf2.project project7 (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,773)

  • Output: project7.id
  • Index Cond: (project7.id = CASE WHEN ("substring"(timeentrymetadata6.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata6.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:(.*)$'::text))::integer ELSE NULL::integer END)
  • Heap Fetches: 0
  • Buffers: shared hit=213
47. 16.638 16.638 ↑ 1.0 1 2,773

Index Scan using ixtemtimeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata timeentrymetadata8 (cost=0.43..0.98 rows=1 width=79) (actual time=0.004..0.006 rows=1 loops=2,773)

  • Output: timeentrymetadata8.id, timeentrymetadata8.timeentryid, timeentrymetadata8.parentid, timeentrymetadata8.index, timeentrymetadata8.key, timeentrymetadata8.uri, timeentrymetadata8.slug, timeentrymetadata8."boolean", timeentrymetadata8.date, timeentrymetadata8.number, timeentrymetadata8.text, timeentrymetadata8."time", timeentrymetadata8.timespan, timeentrymetadata8.daterange_startdate, timeentrymetadata8.daterange_enddate, timeentrymetadata8.daterange_relativedaterangeuri, timeentrymetadata8.daterange_relativedaterangeasofdate, timeentrymetadata8.workdayduration_decimalworkdays, timeentrymetadata8.workdayduration_workdays, timeentrymetadata8.workdayduration_hours, timeentrymetadata8.workdayduration_minutes
  • Index Cond: ("*SELECT* 1".timeentryid = timeentrymetadata8.timeentryid)
  • Filter: (upper(timeentrymetadata8.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=11,121
48. 13.865 13.865 ↑ 1.0 1 2,773

Index Scan using task_pkey on d39c603ba5ee46da9a42069eec872cf2.task task9 (cost=0.43..0.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2,773)

  • Output: task9.id, task9.projectid, task9.parentid, task9.name, task9.description, task9.estimatedcost, task9.estimatedcostcurrencyid, task9.estimatedhours, task9.estimatedexpenses, task9.estimatedexpensescurrencyid, task9.expenseentrystartdate, task9.expenseentryenddate, task9.info1, task9.info2, task9.info3, task9.info4, task9.info5, task9.info6, task9.info7, task9.info8, task9.info9, task9.info10, task9.info11, task9.info12, task9.info13, task9.info14, task9.info15, task9.info16, task9.info17, task9.info18, task9.info19, task9.info20, task9.code, task9.isclosed, task9.percentcomplete, task9.istimeentryallowed, task9.timeentrystartdate, task9.timeentryenddate, task9.orderindex, task9.timeandexpenseentrytype, task9.costtype, task9.closeddate, task9.guidid, task9.uri
  • Index Cond: (CASE WHEN ("substring"(timeentrymetadata8.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata8.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:(.*)$'::text))::integer ELSE NULL::integer END = task9.id)
  • Buffers: shared hit=10,256
49. 1.365 1.365 ↑ 1.0 1 15

Index Scan using dm_timesheetlist_facts_pkey on d39c603ba5ee46da9a42069eec872cf2.dm_timesheetlist_facts tslist (cost=0.42..2.44 rows=1 width=16) (actual time=0.091..0.091 rows=1 loops=15)

  • Output: tslist.timesheetslug, tslist.timesheetstartdate, tslist.timesheetenddate, tslist.owneruserid, tslist.owneruserslug, tslist.owneruserdisplayname, tslist.owneruseremployeeid, tslist.owneruseremployeetypeid, tslist.owneruseremployeetypename, tslist.approvalstatus, tslist.timesheetstatus, tslist.totalregularhours, tslist.totalovertimehours, tslist.totaltimeoffhours, tslist.totalbillablehours, tslist.totalnonbillablehours, tslist.totalhours, tslist.totalpayablehours, tslist.totalprojecthours, tslist.totalbreakhours, tslist.totalworkinghours, tslist.ownerdepartmentid, tslist.ownerdepartmentslug, tslist.generatedonutc, tslist.duedate, tslist.historicalsupervisoruserid, tslist.historicalsupervisoruserslug, tslist.historicalsupervisoruserdisplayname, tslist.totalbillableamountinbasecurrency, tslist.waitingonapproveruserid, tslist.waitingonapproveruserslug, tslist.waitingonapproveruserdisplayname, tslist.invoicestatus, tslist.invoicedhours, tslist.invoicedamount, tslist.approvalduedate, tslist.owneruserloginname, tslist.timesheetid, tslist.totalscheduledworkhours, tslist.totalscheduledbreakhours, tslist.validationmessagecounterror, tslist.validationmessagecountwarning, tslist.validationmessagecountinfo, tslist.ownerdepartmentname, tslist.savedonutc, tslist.timesheetscriptcalculationstatustype
  • Index Cond: (tslist.timesheetid = ts.id)
  • Filter: (tslist.timesheetstatus = 2)
  • Buffers: shared hit=59 read=1
50. 0.090 0.090 ↓ 0.0 0 15

Index Scan using ixtemtimeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata timeentrymetadata11 (cost=0.43..0.98 rows=1 width=79) (actual time=0.006..0.006 rows=0 loops=15)

  • Output: timeentrymetadata11.id, timeentrymetadata11.timeentryid, timeentrymetadata11.parentid, timeentrymetadata11.index, timeentrymetadata11.key, timeentrymetadata11.uri, timeentrymetadata11.slug, timeentrymetadata11."boolean", timeentrymetadata11.date, timeentrymetadata11.number, timeentrymetadata11.text, timeentrymetadata11."time", timeentrymetadata11.timespan, timeentrymetadata11.daterange_startdate, timeentrymetadata11.daterange_enddate, timeentrymetadata11.daterange_relativedaterangeuri, timeentrymetadata11.daterange_relativedaterangeasofdate, timeentrymetadata11.workdayduration_decimalworkdays, timeentrymetadata11.workdayduration_workdays, timeentrymetadata11.workdayduration_hours, timeentrymetadata11.workdayduration_minutes
  • Index Cond: ("*SELECT* 1".timeentryid = timeentrymetadata11.timeentryid)
  • Filter: (upper(timeentrymetadata11.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=60
51. 0.015 0.015 ↓ 0.0 0 15

Index Only Scan using clients_pkey on d39c603ba5ee46da9a42069eec872cf2.clients clients12 (cost=0.15..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=15)

  • Output: clients12.id
  • Index Cond: (clients12.id = CASE WHEN ("substring"(timeentrymetadata11.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata11.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text))::integer ELSE NULL::integer END)
  • Heap Fetches: 0
52. 0.045 0.045 ↑ 1.0 1 15

Index Scan using clients_pkey on d39c603ba5ee46da9a42069eec872cf2.clients cl (cost=0.14..2.16 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=15)

  • Output: cl.id, cl.name, cl.code, cl.comments, cl.disabled, cl.city, cl.stateprovince, cl.zippostalcode, cl.telephone, cl.fax, cl.website, cl.info1, cl.info2, cl.info3, cl.info4, cl.info5, cl.info6, cl.info7, cl.info8, cl.info9, cl.info10, cl.info11, cl.info12, cl.slug, cl.clientmanageruserid, cl.address, cl.country, cl.email, cl.billingcontact, cl.billingaddress, cl.billingcity, cl.billingstateprovince, cl.billingzippostalcode, cl.billingcountry, cl.billingemail, cl.billingtelephone, cl.billingfax, cl.billingwebsite, cl.defaultbillingcurrencyid, cl.guidid, cl.uniquename, cl.uniquecode, cl.taxprofileid, cl.defaultinvoicepaymentterm
  • Index Cond: (cl.id = 60)
  • Buffers: shared hit=30
53. 0.030 0.030 ↑ 1.0 1 15

Index Scan using task_pkey on d39c603ba5ee46da9a42069eec872cf2.task tk (cost=0.42..0.49 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=15)

  • Output: tk.id, tk.projectid, tk.parentid, tk.name, tk.description, tk.estimatedcost, tk.estimatedcostcurrencyid, tk.estimatedhours, tk.estimatedexpenses, tk.estimatedexpensescurrencyid, tk.expenseentrystartdate, tk.expenseentryenddate, tk.info1, tk.info2, tk.info3, tk.info4, tk.info5, tk.info6, tk.info7, tk.info8, tk.info9, tk.info10, tk.info11, tk.info12, tk.info13, tk.info14, tk.info15, tk.info16, tk.info17, tk.info18, tk.info19, tk.info20, tk.code, tk.isclosed, tk.percentcomplete, tk.istimeentryallowed, tk.timeentrystartdate, tk.timeentryenddate, tk.orderindex, tk.timeandexpenseentrytype, tk.costtype, tk.closeddate, tk.guidid, tk.uri
  • Index Cond: (task9.id = tk.id)
  • Buffers: shared hit=60
54. 0.090 0.090 ↑ 1.0 1 15

Index Scan using uix3er_currencyideffectivedate on d39c603ba5ee46da9a42069eec872cf2.exchangerate (cost=0.14..0.18 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=15)

  • Output: exchangerate.variablecurrencyid, exchangerate.exchangevalue, exchangerate.effectivedate, exchangerate.enddate, exchangerate.id, exchangerate.fixedcurrencyid
  • Index Cond: ((exchangerate.variablecurrencyid = $0) AND (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid) AND (('now'::cstring)::date >= exchangerate.effectivedate))
  • Filter: (('now'::cstring)::date <= exchangerate.enddate)
  • Buffers: shared hit=30
55. 0.075 0.075 ↑ 1.0 1 15

Index Scan using userinfo_pkey on d39c603ba5ee46da9a42069eec872cf2.userinfo ui (cost=0.28..0.30 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=15)

  • Output: ui.id, ui.firstname, ui.lastname, ui.email, ui.startdate, ui.enddate, ui.externalid, ui.disabled, ui.info1, ui.info2, ui.info3, ui.info4, ui.info5, ui.info6, ui.info7, ui.info8, ui.info9, ui.info10, ui.info11, ui.info12, ui.info13, ui.info14, ui.info15, ui.info16, ui.info17, ui.info18, ui.info19, ui.info20, ui.info21, ui.info22, ui.info23, ui.info24, ui.info25, ui.info26, ui.info27, ui.info28, ui.info29, ui.info30, ui.info31, ui.info32, ui.info33, ui.info34, ui.info35, ui.info36, ui.info37, ui.info38, ui.info39, ui.info40, ui.info41, ui.info42, ui.info43, ui.info44, ui.info45, ui.info46, ui.info47, ui.info48, ui.info49, ui.info50, ui.languageid, ui.employeetypeid, ui.timezoneid, ui.duplicatename, ui.issampleuser, ui.slug, ui.defaultactivityid, ui.displayname, ui.guidid, ui.customdisplayname
  • Index Cond: (ui.id = "*SELECT* 1".userid)
  • Buffers: shared hit=45
56. 0.075 0.075 ↑ 1.0 1 15

Index Scan using billingrate_pkey on d39c603ba5ee46da9a42069eec872cf2.billingrate billingrate5 (cost=0.27..0.29 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=15)

  • Output: billingrate5.id, billingrate5.name, billingrate5.description, billingrate5.isenabled
  • Index Cond: ("*SELECT* 1".billingrateid = billingrate5.id)
  • Buffers: shared hit=45
57. 0.060 0.060 ↑ 1.0 1 15

Index Only Scan using project_pkey on d39c603ba5ee46da9a42069eec872cf2.project project13 (cost=0.28..1.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=15)

  • Output: project13.id
  • Index Cond: (project13.id = 2,805)
  • Heap Fetches: 0
  • Buffers: shared hit=31
58. 0.015 0.135 ↓ 0.0 0 15

Unique (cost=0.56..2.61 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=15)

  • Output: project14.id
  • Buffers: shared hit=61
59. 0.030 0.120 ↓ 0.0 0 15

Nested Loop (cost=0.56..2.61 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=15)

  • Output: project14.id
  • Buffers: shared hit=61
60. 0.030 0.030 ↑ 1.0 1 15

Index Only Scan using project_pkey on d39c603ba5ee46da9a42069eec872cf2.project project14 (cost=0.28..1.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=15)

  • Output: project14.id
  • Index Cond: (project14.id = 2,805)
  • Heap Fetches: 0
  • Buffers: shared hit=31
61. 0.060 0.060 ↓ 0.0 0 15

Index Only Scan using uix4pc_projectclienteffectiveend on d39c603ba5ee46da9a42069eec872cf2.projectclient projectclient15 (cost=0.28..1.30 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=15)

  • Output: projectclient15.projectid, projectclient15.clientid, projectclient15.effectivedate, projectclient15.enddate
  • Index Cond: ((projectclient15.projectid = 2,805) AND (projectclient15.clientid = 70))
  • Heap Fetches: 0
  • Buffers: shared hit=30
62. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on d39c603ba5ee46da9a42069eec872cf2.project project16 (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Output: project16.id
  • Index Cond: (project16.id = 2,805)
  • Filter: (project16.projectleaderapproverid = 503)
63. 0.000 0.000 ↓ 0.0 0

Index Only Scan using clients_pkey on d39c603ba5ee46da9a42069eec872cf2.clients clients17 (cost=0.14..1.16 rows=1 width=4) (never executed)

  • Output: clients17.id
  • Index Cond: (clients17.id = 60)
  • Heap Fetches: 0
Planning time : 49.906 ms
Execution time : 5,236,366.502 ms