explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JY49

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 5,483,301.934 ↓ 0.0 0 1

Sort (cost=720,060.93..720,060.94 rows=1 width=2,856) (actual time=5,483,301.934..5,483,301.934 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,964,986 read=351,827
2.          

Initplan (for Sort)

3. 0.005 0.005 ↑ 1.0 1 1

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

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

Nested Loop Semi Join (cost=258,575.76..720,059.91 rows=1 width=2,856) (actual time=5,483,301.843..5,483,301.843 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,964,980 read=351,827
5. 0.000 5,483,301.842 ↓ 0.0 0 1

Nested Loop Semi Join (cost=258,575.62..720,058.74 rows=1 width=186) (actual time=5,483,301.842..5,483,301.842 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,964,980 read=351,827
6. 0.035 5,483,301.842 ↓ 0.0 0 1

Nested Loop Semi Join (cost=258,575.34..720,056.43 rows=1 width=186) (actual time=5,483,301.841..5,483,301.842 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,964,980 read=351,827
7. 0.039 5,483,301.687 ↓ 15.0 15 1

Nested Loop Semi Join (cost=258,574.78..720,052.81 rows=1 width=186) (actual time=413,451.303..5,483,301.687 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,964,919 read=351,827
8. 0.046 5,483,301.588 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,574.50..720,051.50 rows=1 width=186) (actual time=413,451.290..5,483,301.588 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,964,888 read=351,827
9. 0.052 5,483,301.467 ↓ 15.0 15 1

Nested Loop (cost=258,574.23..720,051.21 rows=1 width=177) (actual time=413,451.277..5,483,301.467 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,964,843 read=351,827
10. 0.114 5,483,301.340 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,573.95..720,050.91 rows=1 width=156) (actual time=413,451.263..5,483,301.340 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,964,798 read=351,827
11. 0.047 5,483,301.136 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,573.80..720,050.73 rows=1 width=160) (actual time=413,451.234..5,483,301.136 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,964,767 read=351,827
12. 0.039 5,483,301.044 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,573.39..720,050.24 rows=1 width=134) (actual time=413,451.225..5,483,301.044 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
  • Filter: (cl.id = COALESCE(clients12.id, pj.clientid))
  • Buffers: shared hit=2,511,964,707 read=351,827
13. 0.040 5,483,301.005 ↓ 15.0 15 1

Nested Loop (cost=258,573.23..720,049.05 rows=1 width=201) (actual time=413,451.219..5,483,301.005 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, cl.name, cl.id
  • Buffers: shared hit=2,511,964,707 read=351,827
14. 0.046 5,483,300.905 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,573.09..720,046.88 rows=1 width=177) (actual time=413,451.208..5,483,300.905 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,964,677 read=351,827
15. 0.057 5,483,300.739 ↓ 15.0 15 1

Nested Loop (cost=258,572.65..720,045.90 rows=1 width=130) (actual time=413,451.183..5,483,300.739 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,964,617 read=351,827
16. 251.374 5,483,291.817 ↓ 15.0 15 1

Nested Loop Left Join (cost=258,572.23..720,043.46 rows=1 width=146) (actual time=413,449.610..5,483,291.817 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,964,563 read=351,821
17. 4.474 5,483,026.578 ↓ 50.4 2,773 1

Nested Loop Left Join (cost=258,571.80..720,017.98 rows=55 width=209) (actual time=233,179.160..5,483,026.578 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,954,307 read=351,821
18. 14.890 5,483,005.466 ↓ 50.4 2,773 1

Nested Loop Left Join (cost=258,571.37..719,963.65 rows=55 width=146) (actual time=233,179.148..5,483,005.466 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,943,186 read=351,821
19. 6.393 5,482,987.803 ↓ 50.4 2,773 1

Nested Loop Left Join (cost=258,571.08..719,946.60 rows=55 width=205) (actual time=233,179.138..5,482,987.803 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,942,973 read=351,821
20. 33,783.932 5,482,939.815 ↓ 50.4 2,773 1

Nested Loop (cost=258,570.64..719,892.27 rows=55 width=142) (actual time=233,179.109..5,482,939.815 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,599,803
  • Buffers: shared hit=2,511,931,852 read=351,821
21. 0.621 29.605 ↓ 237.0 237 1

Nested Loop (cost=0.70..2,347.82 rows=1 width=94) (actual time=16.915..29.605 rows=237 loops=1)

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

Index Scan using uix2tsuseridstartdate on d39c603ba5ee46da9a42069eec872cf2.timesheet ts (cost=0.42..2,345.51 rows=1 width=28) (actual time=16.897..26.851 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=244 read=573
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. 42,733.233 5,449,126.278 ↓ 3.4 1,677,648 237

Append (cost=258,569.94..708,805.21 rows=499,385 width=56) (actual time=597.285..22,992.094 rows=1,677,648 loops=237)

  • Buffers: shared hit=2,511,930,897 read=351,248
25. 272,452.961 5,404,692.570 ↓ 3.4 1,674,595 237

Subquery Scan on *SELECT* 1 (cost=258,569.94..662,103.83 rows=497,115 width=56) (actual time=597.285..22,804.610 rows=1,674,595 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,910,890 read=351,248
26. 3,691,164.084 5,131,254.057 ↓ 2.5 1,674,595 237

Nested Loop (cost=212,092.23..605,683.83 rows=662,820 width=140) (actual time=592.283..21,650.861 rows=1,674,595 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,899,505 read=351,248
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,625.537 1,440,089.499 ↓ 2.5 1,674,595 237

Nested Loop (cost=212,092.23..577,512.97 rows=662,820 width=215) (actual time=592.280..6,076.327 rows=1,674,595 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,899,268 read=351,248
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,374,463.488 ↓ 2.5 1,674,595 237

Gather (cost=212,092.23..570,883.76 rows=662,820 width=182) (actual time=592.277..5,799.424 rows=1,674,595 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,899,031 read=351,248
31. 31,366.315 1,592,542.802 ↓ 2.0 559,773 709 / 3

Hash Left Join (cost=211,092.23..503,601.76 rows=276,175 width=182) (actual time=913.045..6,738.545 rows=559,773 loops=709)

  • 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,899,031 read=351,248
  • Worker 0: actual time=1,074.021..7802.407 rows=658,156 loops=236
  • Buffers: shared hit=978,757,184 read=115,410
  • Worker 1: actual time=1,074.229..7747.503 rows=653,266 loops=236
  • Buffers: shared hit=974,440,249 read=129,075
32. 119,144.031 1,345,426.996 ↓ 2.0 559,773 709 / 3

Nested Loop Left Join (cost=0.43..291,474.29 rows=276,175 width=119) (actual time=0.098..5,692.921 rows=559,773 loops=709)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, tembillingrate.uri
  • Buffers: shared hit=2,443,255,297 read=231,694
  • Worker 0: actual time=0.121..6574.263 rows=658,156 loops=236
  • Buffers: shared hit=944,481,318 read=82,304
  • Worker 1: actual time=0.127..6520.970 rows=653,266 loops=236
  • Buffers: shared hit=940,179,072 read=81,280
33. 35,645.920 35,645.920 ↓ 2.0 559,773 709 / 3

Parallel Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeentry te (cost=0.00..49,505.66 rows=276,175 width=56) (actual time=0.010..150.829 rows=559,773 loops=709)

  • 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,319
  • Buffers: shared hit=5,331,244 read=17,478
  • Worker 0: actual time=0.011..182.504 rows=658,156 loops=236
  • Buffers: shared hit=2,264,621 read=6,973
  • Worker 1: actual time=0.011..179.841 rows=653,266 loops=236
  • Buffers: shared hit=2,210,859 read=5,500
34. 1,190,637.045 1,190,637.045 ↑ 1.0 1 396,879,015 / 3

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

  • 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,437,924,053 read=214,216
  • Worker 0: actual time=0.006..0.009 rows=1 loops=155,324,750
  • Buffers: shared hit=942,216,697 read=75,331
  • Worker 1: actual time=0.006..0.009 rows=1 loops=154,170,891
  • Buffers: shared hit=937,968,213 read=75,780
35. 0.000 215,749.490 ↓ 0.0 0 473 / 3

Hash (cost=211,091.79..211,091.79 rows=1 width=79) (actual time=1,368.390..1,368.390 rows=0 loops=473)

  • Output: temdbreaktype.uri, temdbreaktype.timeentryid
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=68,628,630 read=119,554
  • Worker 0: actual time=1,073.836..1073.836 rows=0 loops=236
  • Buffers: shared hit=34,268,314 read=33,106
  • Worker 1: actual time=1,074.035..1074.035 rows=0 loops=236
  • Buffers: shared hit=34,253,625 read=47,795
36. 215,749.490 215,749.490 ↓ 0.0 0 473 / 3

Index Scan using ixtem2timeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata temdbreaktype (cost=0.56..211,091.79 rows=1 width=79) (actual time=1,368.389..1,368.390 rows=0 loops=473)

  • Output: temdbreaktype.uri, temdbreaktype.timeentryid
  • Index Cond: (upper(temdbreaktype.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
  • Buffers: shared hit=68,628,630 read=119,554
  • Worker 0: actual time=1,073.835..1073.835 rows=0 loops=236
  • Buffers: shared hit=34,268,314 read=33,106
  • Worker 1: actual time=1,074.034..1074.034 rows=0 loops=236
  • Buffers: shared hit=34,253,625 read=47,795
37.          

SubPlan (for Subquery Scan)

38. 985.184 985.184 ↓ 1.0 1,989,663 2

Index Only Scan using timeentry_pkey on d39c603ba5ee46da9a42069eec872cf2.timeentry timeentry18 (cost=0.43..41,432.32 rows=1,988,726 width=16) (actual time=0.088..492.592 rows=1,989,663 loops=2)

  • Output: timeentry18.id
  • Heap Fetches: 564,514
  • Buffers: shared hit=22,770
39. 0.368 0.368 ↑ 1.0 2,258 1

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeoffs timeoffs19 (cost=0.00..67.86 rows=2,286 width=4) (actual time=0.007..0.368 rows=2,258 loops=1)

  • Output: timeoffs19.id
  • Buffers: shared hit=45
40. 1,393.086 1,700.475 ↓ 1.3 3,053 237

Subquery Scan on *SELECT* 2 (cost=46,574.14..46,701.38 rows=2,270 width=56) (actual time=4.894..7.175 rows=3,053 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=20,007
41. 192.973 307.389 ↓ 1.0 3,053 237

Hash Join (cost=96.44..178.27 rows=3,027 width=140) (actual time=0.011..1.297 rows=3,053 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. 113.760 113.760 ↓ 1.0 3,053 237

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeoffentries toe (cost=0.00..73.88 rows=3,027 width=8) (actual time=0.005..0.480 rows=3,053 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.268 0.656 ↑ 1.0 2,258 1

Hash (cost=67.86..67.86 rows=2,286 width=8) (actual time=0.656..0.656 rows=2,258 loops=1)

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

Seq Scan on d39c603ba5ee46da9a42069eec872cf2.timeoffs toff (cost=0.00..67.86 rows=2,286 width=8) (actual time=0.007..0.388 rows=2,258 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.005..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. 8.865 8.865 ↑ 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.591..0.591 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=54 read=6
50. 0.120 0.120 ↓ 0.0 0 15

Index Scan using ixtemtimeentryid on d39c603ba5ee46da9a42069eec872cf2.timeentrymetadata timeentrymetadata11 (cost=0.43..0.98 rows=1 width=79) (actual time=0.008..0.008 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.060 0.060 ↑ 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.004..0.004 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
52. 0.000 0.000 ↓ 0.0 0 15

Index Only Scan using clients_pkey on d39c603ba5ee46da9a42069eec872cf2.clients clients12 (cost=0.15..1.17 rows=1 width=4) (actual time=0.000..0.000 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
53. 0.045 0.045 ↑ 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.003..0.003 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.006..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.000 0.120 ↓ 0.0 0 15

Unique (cost=0.56..3.61 rows=1 width=4) (actual time=0.008..0.008 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..3.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..2.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 : 8.451 ms
Execution time : 5,483,324.851 ms