explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MvrE : nscglobalnewinstance

Settings
# exclusive inclusive rows x rows loops node
1. 305.582 17,961,635.553 ↓ 1.3 48,958 1

Sort (cost=1,846,029,661.77..1,846,029,757.63 rows=38,344 width=3,055) (actual time=17,961,613.030..17,961,635.553 rows=48,958 loops=1)

  • Output: ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), ui.duplicatename, ((login.loginname)::character varying(255)), ((ui.displayname)::text), ui.id, ((pj.name)::character varying(255)), pj.id, ((tk.name)::character varying(255)), tk.id, "*SELECT* 1".entrydate, "*SELECT* 1".duration, ((billingrate5.name)::character varying(50)), ((timeoffcode6.name)::character varying(255)), "*SELECT* 1".timeoffduration, ((cl.name)::character varying(255)), cl.id, ((userinfo8.displayname)::text), "*SELECT* 1".userid
  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ((billingrate5.name)::character varying(50)) COLLATE "en_US", ((timeoffcode6.name)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((userinfo8.displayname)::text) COLLATE "en_US
  • Sort Method: external merge Disk: 10,200kB
  • Buffers: shared hit=1,691,785 read=69,400, temp read=339,980,781 written=12,473
2.          

Initplan (for Sort)

3. 0.004 0.004 ↑ 1.0 1 1

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

  • Output: systeminformation.basecurrencyid
  • Buffers: shared hit=1
4. 54.498 17,961,329.967 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,007.65..1,845,969,073.49 rows=38,344 width=3,055) (actual time=17,896,065.953..17,961,329.967 rows=48,958 loops=1)

  • Output: ui.lastname, ui.firstname, ui.duplicatename, login.loginname, ui.displayname, ui.id, pj.name, pj.id, tk.name, tk.id, "*SELECT* 1".entrydate, "*SELECT* 1".duration, billingrate5.name, timeoffcode6.name, "*SELECT* 1".timeoffduration, cl.name, cl.id, userinfo8.displayname, "*SELECT* 1".userid
  • Inner Unique: true
  • Buffers: shared hit=1,691,770 read=69,400, temp read=339,979,506 written=11,195
5. 47.180 17,961,226.511 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,007.38..1,845,955,905.20 rows=38,344 width=200) (actual time=17,896,065.941..17,961,226.511 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name, timeoffcode6.name, clients15.id, userinfo8.displayname
  • Inner Unique: true
  • Buffers: shared hit=1,572,280 read=69,400, temp read=339,979,506 written=11,195
6. 23.254 17,961,179.331 ↓ 1.3 48,958 1

Merge Right Join (cost=1,007.09..1,845,943,258.34 rows=38,344 width=247) (actual time=17,896,065.933..17,961,179.331 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name, timeoffcode6.name, timeentrymetadata14.uri, userinfo8.displayname
  • Merge Cond: (userhierarchy16.userid = ui.id)
  • Buffers: shared hit=1,572,102 read=69,400, temp read=339,979,506 written=11,195
7. 7.506 39.939 ↑ 1.0 6,363 1

Nested Loop Left Join (cost=0.57..2,426.22 rows=6,365 width=20) (actual time=0.034..39.939 rows=6,363 loops=1)

  • Output: userhierarchy16.userid, userinfo8.displayname
  • Inner Unique: true
  • Buffers: shared hit=22,595 read=2
8. 13.344 13.344 ↑ 1.0 6,363 1

Index Scan using ix3uh_usersuperstart on nscglobalnewinstance.userhierarchy userhierarchy16 (cost=0.29..325.77 rows=6,365 width=8) (actual time=0.022..13.344 rows=6,363 loops=1)

  • Output: userhierarchy16.id, userhierarchy16.userid, userhierarchy16.supervisorid, userhierarchy16.startdate, userhierarchy16.enddate
  • Index Cond: ('2020-07-24'::date >= userhierarchy16.startdate)
  • Filter: ('2020-07-24'::date <= userhierarchy16.enddate)
  • Rows Removed by Filter: 3,309
  • Buffers: shared hit=3,506 read=2
9. 19.089 19.089 ↑ 1.0 1 6,363

Index Scan using userinfo_pkey on nscglobalnewinstance.userinfo userinfo8 (cost=0.28..0.33 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=6,363)

  • Output: userinfo8.id, userinfo8.firstname, userinfo8.lastname, userinfo8.slug, userinfo8.email, userinfo8.startdate, userinfo8.enddate, userinfo8.externalid, userinfo8.disabled, userinfo8.info1, userinfo8.info2, userinfo8.info3, userinfo8.info4, userinfo8.info5, userinfo8.info6, userinfo8.info7, userinfo8.info8, userinfo8.info9, userinfo8.info10, userinfo8.info11, userinfo8.info12, userinfo8.info13, userinfo8.info14, userinfo8.info15, userinfo8.info16, userinfo8.info17, userinfo8.info18, userinfo8.info19, userinfo8.info20, userinfo8.info21, userinfo8.info22, userinfo8.info23, userinfo8.info24, userinfo8.info25, userinfo8.info26, userinfo8.info27, userinfo8.info28, userinfo8.info29, userinfo8.info30, userinfo8.info31, userinfo8.info32, userinfo8.info33, userinfo8.info34, userinfo8.info35, userinfo8.info36, userinfo8.info37, userinfo8.info38, userinfo8.info39, userinfo8.info40, userinfo8.info41, userinfo8.info42, userinfo8.info43, userinfo8.info44, userinfo8.info45, userinfo8.info46, userinfo8.info47, userinfo8.info48, userinfo8.info49, userinfo8.info50, userinfo8.languageid, userinfo8.employeetypeid, userinfo8.timezoneid, userinfo8.duplicatename, userinfo8.displayname, userinfo8.issampleuser, userinfo8.defaultactivityid, userinfo8.guidid, userinfo8.customdisplayname
  • Index Cond: (userhierarchy16.supervisorid = userinfo8.id)
  • Buffers: shared hit=19,089
10. 29.159 17,961,116.138 ↓ 1.3 48,958 1

Materialize (cost=1,006.52..1,845,940,338.29 rows=38,344 width=231) (actual time=17,896,065.869..17,961,116.138 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name, timeoffcode6.name, timeentrymetadata14.uri
  • Buffers: shared hit=1,549,507 read=69,398, temp read=339,979,506 written=11,195
11. 45.786 17,961,086.979 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,006.52..1,845,940,242.43 rows=38,344 width=231) (actual time=17,896,065.866..17,961,086.979 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name, timeoffcode6.name, timeentrymetadata14.uri
  • Buffers: shared hit=1,549,507 read=69,398, temp read=339,979,506 written=11,195
12. 50.714 17,960,845.361 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,005.96..1,845,850,744.89 rows=38,344 width=196) (actual time=17,896,065.847..17,960,845.361 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name, timeoffcode6.name
  • Inner Unique: true
  • Buffers: shared hit=1,381,564 read=69,394, temp read=339,979,506 written=11,195
13. 31.818 17,960,794.647 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,005.81..1,845,844,411.04 rows=38,344 width=174) (actual time=17,896,065.839..17,960,794.647 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".timeoffcodeid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, billingrate5.name
  • Inner Unique: true
  • Buffers: shared hit=1,367,570 read=69,394, temp read=339,979,506 written=11,195
14. 80.450 17,960,713.871 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,005.67..1,845,838,371.87 rows=38,344 width=174) (actual time=17,896,065.826..17,960,713.871 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id
  • Join Filter: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Rows Removed by Join Filter: 1,145,159
  • Buffers: shared hit=1,295,848 read=69,394, temp read=339,979,506 written=11,195
15. 42.874 17,960,535.505 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,005.38..1,845,824,364.01 rows=38,344 width=178) (actual time=17,896,065.216..17,960,535.505 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname, tk.name, tk.id, tk.estimatedcostcurrencyid
  • Inner Unique: true
  • Buffers: shared hit=1,295,712 read=69,394, temp read=339,979,506 written=11,195
16. 21.775 17,960,443.673 ↓ 1.3 48,958 1

Merge Join (cost=1,005.09..1,845,811,839.75 rows=38,344 width=150) (actual time=17,896,065.208..17,960,443.673 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, task12.id, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id, login.loginname
  • Inner Unique: true
  • Merge Cond: ("*SELECT* 1".userid = login.userid)
  • Buffers: shared hit=1,176,130 read=69,394, temp read=339,979,506 written=11,195
17. 61.581 17,960,414.173 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,004.81..1,845,811,084.37 rows=38,344 width=136) (actual time=17,896,065.167..17,960,414.173 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, task12.id, pj.name, pj.id, pj.clientid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id
  • Inner Unique: true
  • Buffers: shared hit=1,172,453 read=69,394, temp read=339,979,506 written=11,195
18. 2,283.120 17,960,303.634 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,004.52..1,845,797,063.72 rows=38,344 width=123) (actual time=17,896,065.152..17,960,303.634 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, project10.id, task12.id, task12.projectid, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id
  • Inner Unique: true
  • Buffers: shared hit=1,052,729 read=69,394, temp read=339,979,506 written=11,195
19. 36.461 17,957,922.598 ↓ 1.3 48,958 1

Nested Loop Left Join (cost=1,004.22..1,845,784,777.93 rows=38,344 width=166) (actual time=17,896,064.906..17,957,922.598 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, project10.id, timeentrymetadata11.uri, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id
  • Buffers: shared hit=933,175 read=69,366, temp read=339,979,506 written=11,195
20. 24,195.301 17,950,493.479 ↓ 1.4 48,958 1

Nested Loop (cost=1,003.66..1,845,700,613.74 rows=36,059 width=115) (actual time=17,896,064.880..17,950,493.479 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, project10.id, ui.lastname, ui.firstname, ui.duplicatename, ui.displayname, ui.id
  • Join Filter: ("*SELECT* 1".userid = ui.id)
  • Rows Removed by Join Filter: 312,694,746
  • Buffers: shared hit=723,148 read=63,351, temp read=339,979,506 written=11,195
21. 11.506 11.506 ↑ 1.0 6,388 1

Index Scan using userinfo_pkey on nscglobalnewinstance.userinfo ui (cost=0.28..287.10 rows=6,388 width=35) (actual time=0.026..11.506 rows=6,388 loops=1)

  • Output: ui.id, ui.firstname, ui.lastname, ui.slug, 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.displayname, ui.issampleuser, ui.defaultactivityid, ui.guidid, ui.customdisplayname
  • Buffers: shared hit=3,642
22. 30,396.684 17,926,286.672 ↓ 1.4 48,958 6,388

Materialize (cost=1,003.38..1,842,245,243.41 rows=36,059 width=80) (actual time=0.132..2,806.244 rows=48,958 loops=6,388)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, project10.id
  • Buffers: shared hit=719,506 read=63,351, temp read=339,979,506 written=11,195
23. 268.932 17,895,889.988 ↓ 1.4 48,958 1

Nested Loop Left Join (cost=1,003.38..1,842,245,063.11 rows=36,059 width=80) (actual time=831.229..17,895,889.988 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, project10.id
  • Inner Unique: true
  • Buffers: shared hit=719,506 read=63,351, temp read=337,488,576 written=10,806
24. 185.528 17,895,572.098 ↓ 1.4 48,958 1

Nested Loop Left Join (cost=1,003.08..1,842,233,457.79 rows=36,059 width=127) (actual time=831.224..17,895,572.098 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, timeentrymetadata9.uri
  • Buffers: shared hit=716,210 read=63,351, temp read=337,488,576 written=10,806
25. 262.181 17,895,092.822 ↓ 1.4 48,958 1

Nested Loop Left Join (cost=1,002.52..1,842,149,293.60 rows=36,059 width=76) (actual time=831.213..17,895,092.822 rows=48,958 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid
  • Filter: (((timeentrymetadata13.uri IS NULL) AND ("*SELECT* 1".timeoffentryid IS NULL) AND (("*SELECT* 1".timeallocationtype = 1) OR ("*SELECT* 1".timeallocationtype IS NULL))) OR (timeentrymetadata13.uri IS NOT NULL) OR ("*SELECT* 1".timeoffentryid IS NOT NULL) OR ((timeentrymetadata13.uri IS NULL) AND ("*SELECT* 1".timeoffentryid IS NULL) AND ("*SELECT* 1".timeallocationtype = 0)))
  • Buffers: shared hit=546,990 read=63,300, temp read=337,488,576 written=10,806
26. 32.154 17,894,047.313 ↓ 1.4 48,958 1

Append (cost=1,001.96..1,842,064,759.65 rows=36,140 width=84) (actual time=831.190..17,894,047.313 rows=48,958 loops=1)

  • Buffers: shared hit=377,255 read=63,300, temp read=337,488,576 written=10,806
27. 5,215,468.526 15,323,954.121 ↓ 1.4 41,961 1

Subquery Scan on *SELECT* 1 (cost=1,001.96..1,571,230,637.84 rows=30,826 width=84) (actual time=831.189..15,323,954.121 rows=41,961 loops=1)

  • Output: "*SELECT* 1".entrydate, "*SELECT* 1".duration, "*SELECT* 1".timeoffduration, "*SELECT* 1".userid, "*SELECT* 1".timeentryid, "*SELECT* 1".billingrateid, "*SELECT* 1".timeoffcodeid, "*SELECT* 1".timeoffentryid, "*SELECT* 1".timeallocationtype
  • Filter: ((SubPlan 2) OR (SubPlan 3))
  • Buffers: shared hit=344,466 read=62,217, temp read=286,105,393 written=7,078
28. 339.417 848.092 ↓ 1.0 41,961 1

Nested Loop (cost=1,001.54..120,507.97 rows=41,102 width=140) (actual time=6.617..848.092 rows=41,961 loops=1)

  • Output: te.id, NULL::integer, te.userid, te.entrydate, NULL::time without time zone, NULL::time without time zone, 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, 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, NULL::interval, NULL::interval, te.timeallocationtype, NULL::integer
  • Buffers: shared hit=343,904 read=41,845
29. 1.102 1.102 ↑ 1.0 1 1

Seq Scan on nscglobalnewinstance.projectsysteminformation psi (cost=0.00..1.01 rows=1 width=32) (actual time=1.102..1.102 rows=1 loops=1)

  • 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 read=1
30. 36.860 507.573 ↓ 1.0 41,961 1

Nested Loop (cost=1,001.54..118,760.12 rows=41,102 width=133) (actual time=5.514..507.573 rows=41,961 loops=1)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, te.timeallocationtype, tembillingrate.uri, si.tenantslug
  • Buffers: shared hit=343,904 read=41,844
31. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on nscglobalnewinstance.systeminformation si (cost=0.00..1.01 rows=1 width=22) (actual time=0.002..0.003 rows=1 loops=1)

  • Output: si.rootuserid, si.basecurrencyid, si.basecurrencysymbol, si.defaulttimesheetapprovalpathid, si.defaultexpenseapprovalpathid, si.defaulttimeoffapprovalpathid, si.defaultlanguageid, si.defaultholidaycalendarid, si.buildnumber, si.isreleaseversion, si.systemguid, si.systemtimezoneid, si.newuserdefaulttimezoneid, si.databaseguid, si.uncategorizedskillcategoryid, si.defaultofficescheduleid, si.customlogouri, si.databaseupdatescriptstage, si.tenantslug, si.defaulttimesheetperiodid, si.defaulttimeentryapprovalpathid
  • Buffers: shared hit=1
32. 0.000 470.710 ↓ 1.0 41,961 1

Gather (cost=1,001.54..118,348.09 rows=41,102 width=111) (actual time=5.510..470.710 rows=41,961 loops=1)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, te.timeallocationtype, tembillingrate.uri
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=343,903 read=41,844
33. 97.060 27,506.691 ↑ 1.2 20,980 2 / 2

Nested Loop Left Join (cost=1.54..113,237.89 rows=24,178 width=111) (actual time=5.588..27,506.691 rows=20,980 loops=2)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, te.timeallocationtype, tembillingrate.uri
  • Buffers: shared hit=343,903 read=41,844
  • Worker 0: actual time=6.870..54807.821 rows=41,767 loops=1
  • Buffers: shared hit=342,325 read=41,673
34. 134.492 27,052.962 ↑ 1.2 20,980 2 / 2

Nested Loop Left Join (cost=0.99..57,526.24 rows=24,178 width=111) (actual time=5.573..27,052.962 rows=20,980 loops=2)

  • Output: te.id, te.userid, te.entrydate, te.hours, te.intime, te.outtime, te.timeallocationtype, tembillingrate.uri
  • Buffers: shared hit=173,553 read=41,581
  • Worker 0: actual time=6.860..53902.457 rows=41,767 loops=1
  • Buffers: shared hit=172,758 read=41,410
35. 168.333 168.333 ↑ 1.2 20,980 2 / 2

Parallel Index Scan using ixte2entrydate on nscglobalnewinstance.timeentry te (cost=0.43..1,814.59 rows=24,178 width=60) (actual time=3.332..168.333 rows=20,980 loops=2)

  • Output: te.id, te.userid, te.entrydate, te.timeallocationtype, te.hours, te.intime, te.outtime, te.approvalstatus
  • Index Cond: ((te.entrydate >= '2020-07-01'::date) AND (te.entrydate <= '2020-07-31'::date))
  • Filter: ((te.timeallocationtype <> 2) OR (te.timeallocationtype IS NULL))
  • Buffers: shared hit=3,290 read=616
  • Worker 0: actual time=4.739..333.154 rows=41,767 loops=1
  • Buffers: shared hit=3,276 read=612
36. 26,750.137 26,750.137 ↑ 1.0 1 41,961 / 2

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata tembillingrate (cost=0.56..2.29 rows=1 width=67) (actual time=1.264..1.275 rows=1 loops=41,961)

  • 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) AND (upper(tembillingrate.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text))
  • Buffers: shared hit=170,263 read=40,965
  • Worker 0: actual time=1.265..1.276 rows=1 loops=41,767
  • Buffers: shared hit=169,482 read=40,798
37. 356.669 356.669 ↓ 0.0 0 41,961 / 2

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata temdbreaktype (cost=0.56..2.29 rows=1 width=16) (actual time=0.016..0.017 rows=0 loops=41,961)

  • Output: temdbreaktype.id, temdbreaktype.timeentryid, temdbreaktype.parentid, temdbreaktype.index, temdbreaktype.key, temdbreaktype.uri, temdbreaktype.slug, temdbreaktype."boolean", temdbreaktype.date, temdbreaktype.number, temdbreaktype.text, temdbreaktype."time", temdbreaktype.timespan, temdbreaktype.daterange_startdate, temdbreaktype.daterange_enddate, temdbreaktype.daterange_relativedaterangeuri, temdbreaktype.daterange_relativedaterangeasofdate, temdbreaktype.workdayduration_decimalworkdays, temdbreaktype.workdayduration_workdays, temdbreaktype.workdayduration_hours, temdbreaktype.workdayduration_minutes
  • Index Cond: ((temdbreaktype.timeentryid = te.id) AND (upper(temdbreaktype.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
  • Buffers: shared hit=170,350 read=263
  • Worker 0: actual time=0.016..0.017 rows=0 loops=41,767
  • Buffers: shared hit=169,567 read=263
38.          

SubPlan (for Subquery Scan)

39. 10,010,326.575 10,010,638.092 ↑ 1.0 2,159,475 48,958

Materialize (cost=0.00..65,536.42 rows=2,226,828 width=16) (actual time=0.003..204.474 rows=2,159,475 loops=48,958)

  • Output: timeentry17.id
  • Buffers: shared hit=562 read=20,372, temp read=335,574,183 written=7,079
40. 311.517 311.517 ↓ 1.0 2,227,056 1

Seq Scan on nscglobalnewinstance.timeentry timeentry17 (cost=0.00..43,528.28 rows=2,226,828 width=16) (actual time=0.828..311.517 rows=2,227,056 loops=1)

  • Output: timeentry17.id
  • Buffers: shared hit=562 read=20,372
41. 96,950.511 96,999.411 ↑ 1.1 159,537 6,997

Materialize (cost=0.42..4,927.36 rows=167,547 width=4) (actual time=0.011..13.863 rows=159,537 loops=6,997)

  • Output: timeoffs18.id
  • Buffers: shared hit=4,069 read=7, temp read=1,914,393 written=3,727
42. 48.900 48.900 ↓ 1.0 167,556 1

Index Only Scan using timeoffs_pkey on nscglobalnewinstance.timeoffs timeoffs18 (cost=0.42..3,434.62 rows=167,547 width=4) (actual time=0.098..48.900 rows=167,556 loops=1)

  • Output: timeoffs18.id
  • Heap Fetches: 13,696
  • Buffers: shared hit=4,069 read=7
43. 2,569,143.558 2,570,061.038 ↓ 1.3 6,997 1

Subquery Scan on *SELECT* 2 (cost=1.26..270,834,121.81 rows=5,314 width=84) (actual time=938.190..2,570,061.038 rows=6,997 loops=1)

  • Output: "*SELECT* 2".entrydate, "*SELECT* 2".duration, "*SELECT* 2".timeoffduration, "*SELECT* 2".userid, "*SELECT* 2".timeentryid, "*SELECT* 2".billingrateid, "*SELECT* 2".timeoffcodeid, "*SELECT* 2".timeoffentryid, "*SELECT* 2".timeallocationtype
  • Filter: ((SubPlan 2) OR (SubPlan 3))
  • Buffers: shared hit=32,789 read=1,083, temp read=51,383,183 written=3,728
44. 30.305 917.480 ↑ 1.0 6,997 1

Nested Loop (cost=0.84..12,378.72 rows=7,085 width=140) (actual time=551.703..917.480 rows=6,997 loops=1)

  • Output: NULL::uuid, toff.id, toff.userid, toe.entrydate, NULL::time without time zone, NULL::time without time zone, toe.duration, NULL::uuid, toff.timeoffcodeid, toe.duration, toe.id, NULL::interval, NULL::interval, NULL::integer, NULL::integer
  • Inner Unique: true
  • Buffers: shared hit=28,720 read=1,076
45. 831.199 831.199 ↑ 1.0 6,997 1

Index Scan using uix2toe_timeoffidentrydate on nscglobalnewinstance.timeoffentries toe (cost=0.42..5,097.04 rows=7,085 width=28) (actual time=551.680..831.199 rows=6,997 loops=1)

  • Output: toe.id, toe.timeoffid, toe.entrydate, toe.duration, toe.workdayduration, toe.timein, toe.timeout, toe.isreportable, toe.entryid
  • Index Cond: ((toe.entrydate >= '2020-07-01'::date) AND (toe.entrydate <= '2020-07-31'::date))
  • Buffers: shared hit=724 read=1,075
46. 55.976 55.976 ↑ 1.0 1 6,997

Index Scan using timeoffs_pkey on nscglobalnewinstance.timeoffs toff (cost=0.42..1.03 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=6,997)

  • Output: toff.id, toff.guidid, toff.userid, toff.createdbyuserid, toff.timeoffcodeid, toff.startdate, toff.enddate, toff.startdurationtype, toff.enddurationtype, toff.startduration, toff.endduration, toff.startdatestarttime, toff.enddateendtime, toff.approvalstatus, toff.comments, toff.submittedonutc, toff.modifiedonutc, toff.entryconfigurationmethod, toff.info1, toff.info2, toff.info3, toff.info4, toff.info5
  • Index Cond: (toff.id = toe.timeoffid)
  • Buffers: shared hit=27,996 read=1
47. 783.328 783.328 ↓ 0.0 0 48,958

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata timeentrymetadata13 (cost=0.56..2.32 rows=1 width=67) (actual time=0.016..0.016 rows=0 loops=48,958)

  • Output: timeentrymetadata13.id, timeentrymetadata13.timeentryid, timeentrymetadata13.parentid, timeentrymetadata13.index, timeentrymetadata13.key, timeentrymetadata13.uri, timeentrymetadata13.slug, timeentrymetadata13."boolean", timeentrymetadata13.date, timeentrymetadata13.number, timeentrymetadata13.text, timeentrymetadata13."time", timeentrymetadata13.timespan, timeentrymetadata13.daterange_startdate, timeentrymetadata13.daterange_enddate, timeentrymetadata13.daterange_relativedaterangeuri, timeentrymetadata13.daterange_relativedaterangeasofdate, timeentrymetadata13.workdayduration_decimalworkdays, timeentrymetadata13.workdayduration_workdays, timeentrymetadata13.workdayduration_hours, timeentrymetadata13.workdayduration_minutes
  • Index Cond: (("*SELECT* 1".timeentryid = timeentrymetadata13.timeentryid) AND (upper(timeentrymetadata13.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
  • Buffers: shared hit=169,735
48. 293.748 293.748 ↓ 0.0 0 48,958

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata timeentrymetadata9 (cost=0.56..2.32 rows=1 width=67) (actual time=0.006..0.006 rows=0 loops=48,958)

  • Output: timeentrymetadata9.id, timeentrymetadata9.timeentryid, timeentrymetadata9.parentid, timeentrymetadata9.index, timeentrymetadata9.key, timeentrymetadata9.uri, timeentrymetadata9.slug, timeentrymetadata9."boolean", timeentrymetadata9.date, timeentrymetadata9.number, timeentrymetadata9.text, timeentrymetadata9."time", timeentrymetadata9.timespan, timeentrymetadata9.daterange_startdate, timeentrymetadata9.daterange_enddate, timeentrymetadata9.daterange_relativedaterangeuri, timeentrymetadata9.daterange_relativedaterangeasofdate, timeentrymetadata9.workdayduration_decimalworkdays, timeentrymetadata9.workdayduration_workdays, timeentrymetadata9.workdayduration_hours, timeentrymetadata9.workdayduration_minutes
  • Index Cond: (("*SELECT* 1".timeentryid = timeentrymetadata9.timeentryid) AND (upper(timeentrymetadata9.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text))
  • Buffers: shared hit=169,220 read=51
49. 48.958 48.958 ↓ 0.0 0 48,958

Index Only Scan using project_pkey on nscglobalnewinstance.project project10 (cost=0.30..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=48,958)

  • Output: project10.id
  • Index Cond: (project10.id = CASE WHEN ("substring"(timeentrymetadata9.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:project:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata9.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:project:(.*)$'::text))::integer ELSE NULL::integer END)
  • Heap Fetches: 965
  • Buffers: shared hit=3,296
50. 7,392.658 7,392.658 ↑ 1.0 1 48,958

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata timeentrymetadata11 (cost=0.56..2.32 rows=1 width=67) (actual time=0.148..0.151 rows=1 loops=48,958)

  • 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) AND (upper(timeentrymetadata11.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text))
  • Buffers: shared hit=210,027 read=6,015
51. 97.916 97.916 ↑ 1.0 1 48,958

Index Scan using task_pkey on nscglobalnewinstance.task task12 (cost=0.30..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=48,958)

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

Index Scan using project_pkey on nscglobalnewinstance.project pj (cost=0.29..0.37 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=48,958)

  • Output: pj.name, pj.id, pj.clientid
  • Index Cond: (pj.id = COALESCE(project10.id, task12.projectid))
  • Buffers: shared hit=119,724
53. 7.725 7.725 ↑ 1.0 6,385 1

Index Scan using login_pkey on nscglobalnewinstance.login (cost=0.28..260.10 rows=6,388 width=18) (actual time=0.032..7.725 rows=6,385 loops=1)

  • Output: login.userid, login.loginname, login.password, login.accountexpiry, login.passwordlastchanged, login.forcepasswordchange, login.disablepasswordchange, login.info1, login.info2, login.info3, login.info4, login.info5, login.passwordentropybits, login.ssoname, login.invalidloginattempts, login.lastinvalidloginattemptutc
  • Buffers: shared hit=3,677
54. 48.958 48.958 ↑ 1.0 1 48,958

Index Scan using task_pkey on nscglobalnewinstance.task tk (cost=0.29..0.33 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=48,958)

  • Output: tk.id, tk.projectid, tk.parentid, tk.name, tk.code, tk.description, tk.isclosed, tk.orderindex, tk.percentcomplete, tk.istimeentryallowed, tk.estimatedhours, tk.timeentrystartdate, tk.timeentryenddate, tk.estimatedexpenses, tk.estimatedexpensescurrencyid, tk.expenseentrystartdate, tk.expenseentryenddate, tk.costtype, tk.estimatedcost, tk.estimatedcostcurrencyid, tk.timeandexpenseentrytype, 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.closeddate
  • Index Cond: (task12.id = tk.id)
  • Buffers: shared hit=119,582
55. 97.318 97.916 ↑ 1.0 24 48,958

Materialize (cost=0.29..204.09 rows=24 width=4) (actual time=0.000..0.002 rows=24 loops=48,958)

  • Output: exchangerate.fixedcurrencyid
  • Buffers: shared hit=136
56. 0.598 0.598 ↑ 1.0 24 1

Index Scan using uix3er_currencyideffectivedate on nscglobalnewinstance.exchangerate (cost=0.29..203.97 rows=24 width=4) (actual time=0.092..0.598 rows=24 loops=1)

  • Output: exchangerate.fixedcurrencyid
  • Index Cond: ((exchangerate.variablecurrencyid = $0) AND (('now'::cstring)::date >= exchangerate.effectivedate))
  • Filter: (('now'::cstring)::date <= exchangerate.enddate)
  • Rows Removed by Filter: 612
  • Buffers: shared hit=136
57. 48.958 48.958 ↑ 1.0 1 48,958

Index Scan using billingrate_pkey on nscglobalnewinstance.billingrate billingrate5 (cost=0.14..0.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=48,958)

  • Output: billingrate5.id, billingrate5.name, billingrate5.description, billingrate5.isenabled
  • Index Cond: ("*SELECT* 1".billingrateid = billingrate5.id)
  • Buffers: shared hit=71,722
58. 0.000 0.000 ↓ 0.0 0 48,958

Index Scan using timeoffcode_pkey on nscglobalnewinstance.timeoffcode timeoffcode6 (cost=0.15..0.17 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=48,958)

  • Output: timeoffcode6.id, timeoffcode6.name, timeoffcode6.slug, timeoffcode6.description, timeoffcode6.systemrequired, timeoffcode6.bankingpermitted, timeoffcode6.disabled, timeoffcode6.displayas, timeoffcode6.paycodeid, timeoffcode6.timeoffrequestresolution, timeoffcode6.requiretimeoffstartendtime, timeoffcode6.accrualmeasurementtype, timeoffcode6.editpolicyoption, timeoffcode6.deletepolicyoption, timeoffcode6.displayformat, timeoffcode6.defaultexpirypolicysetid, timeoffcode6.originalaccrualmeasurementtype, timeoffcode6.autosyncbookingsonscheduleupdate
  • Index Cond: ("*SELECT* 1".timeoffcodeid = timeoffcode6.id)
  • Buffers: shared hit=13,994
59. 195.832 195.832 ↓ 0.0 0 48,958

Index Scan using ixtem2timeentryid on nscglobalnewinstance.timeentrymetadata timeentrymetadata14 (cost=0.56..2.32 rows=1 width=67) (actual time=0.004..0.004 rows=0 loops=48,958)

  • Output: timeentrymetadata14.id, timeentrymetadata14.timeentryid, timeentrymetadata14.parentid, timeentrymetadata14.index, timeentrymetadata14.key, timeentrymetadata14.uri, timeentrymetadata14.slug, timeentrymetadata14."boolean", timeentrymetadata14.date, timeentrymetadata14.number, timeentrymetadata14.text, timeentrymetadata14."time", timeentrymetadata14.timespan, timeentrymetadata14.daterange_startdate, timeentrymetadata14.daterange_enddate, timeentrymetadata14.daterange_relativedaterangeuri, timeentrymetadata14.daterange_relativedaterangeasofdate, timeentrymetadata14.workdayduration_decimalworkdays, timeentrymetadata14.workdayduration_workdays, timeentrymetadata14.workdayduration_hours, timeentrymetadata14.workdayduration_minutes
  • Index Cond: (("*SELECT* 1".timeentryid = timeentrymetadata14.timeentryid) AND (upper(timeentrymetadata14.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text))
  • Buffers: shared hit=167,943 read=4
60. 0.000 0.000 ↓ 0.0 0 48,958

Index Only Scan using clients_pkey on nscglobalnewinstance.clients clients15 (cost=0.29..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=48,958)

  • Output: clients15.id
  • Index Cond: (clients15.id = CASE WHEN ("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:client:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:ns-cglobalnewinstance:client:(.*)$'::text))::integer ELSE NULL::integer END)
  • Heap Fetches: 59
  • Buffers: shared hit=178
61. 48.958 48.958 ↑ 1.0 1 48,958

Index Scan using clients_pkey on nscglobalnewinstance.clients cl (cost=0.28..0.34 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=48,958)

  • Output: cl.id, cl.name, cl.slug, cl.code, cl.comments, cl.clientmanageruserid, cl.disabled, cl.address, cl.city, cl.stateprovince, cl.zippostalcode, cl.country, cl.email, cl.telephone, cl.fax, cl.website, cl.billingcontact, cl.billingaddress, cl.billingcity, cl.billingstateprovince, cl.billingzippostalcode, cl.billingcountry, cl.billingemail, cl.billingtelephone, cl.billingfax, cl.billingwebsite, 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.defaultbillingcurrencyid, cl.guidid, cl.uniquename, cl.uniquecode, cl.taxprofileid
  • Index Cond: (cl.id = COALESCE(clients15.id, pj.clientid))
  • Buffers: shared hit=119,490
Planning time : 11.220 ms
Execution time : 17,961,650.157 ms