explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AWY1

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 92,840.879 ↑ 1.0 10 1

Limit (cost=19,208,888.61..19,208,913.23 rows=10 width=276) (actual time=92,840.820..92,840.879 rows=10 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetid, (("*SELECT* 1".timesheetslug)::text), (("*SELECT* 1".timesheetslug)::text), "*SELECT* 1".owneruserid, (("*SELECT* 1".owneruserdisplayname)::text), (("*SELECT* 1".owneruserslug)::text), "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, (CASE WHEN (hashed SubPlan 8) THEN "*SELECT* 1".totalregularhours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 16) THEN "*SELECT* 1".totalovertimehours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 24) THEN "*SELECT* 1".totaltimeoffhours ELSE NULL::interval END), "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, (CASE WHEN (hashed SubPlan 30) THEN (SubPlan 31) ELSE NULL::interval END), "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus
  • Buffers: shared hit=213,957
2. 7.998 92,840.876 ↑ 4,138.4 10 1

Result (cost=19,208,888.61..19,310,796.71 rows=41,384 width=276) (actual time=92,840.818..92,840.876 rows=10 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetid, (("*SELECT* 1".timesheetslug)::text), (("*SELECT* 1".timesheetslug)::text), "*SELECT* 1".owneruserid, (("*SELECT* 1".owneruserdisplayname)::text), (("*SELECT* 1".owneruserslug)::text), "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, (CASE WHEN (hashed SubPlan 8) THEN "*SELECT* 1".totalregularhours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 16) THEN "*SELECT* 1".totalovertimehours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 24) THEN "*SELECT* 1".totaltimeoffhours ELSE NULL::interval END), "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, CASE WHEN (hashed SubPlan 30) THEN (SubPlan 31) ELSE NULL::interval END, "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus
  • Buffers: shared hit=213,957
3. 5.302 92,623.861 ↑ 4,138.4 10 1

Sort (cost=9,349,438.33..9,349,541.79 rows=41,384 width=260) (actual time=92,623.859..92,623.861 rows=10 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetid, (("*SELECT* 1".timesheetslug)::text), (("*SELECT* 1".timesheetslug)::text), "*SELECT* 1".owneruserid, (("*SELECT* 1".owneruserdisplayname)::text), (("*SELECT* 1".owneruserslug)::text), "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, (CASE WHEN (hashed SubPlan 8) THEN "*SELECT* 1".totalregularhours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 16) THEN "*SELECT* 1".totalovertimehours ELSE NULL::interval END), (CASE WHEN (hashed SubPlan 24) THEN "*SELECT* 1".totaltimeoffhours ELSE NULL::interval END), "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus
  • Sort Key: "*SELECT* 1".timesheetstartdate DESC, (("*SELECT* 1".owneruserdisplayname)::text) COLLATE "en_US", "*SELECT* 1".timesheetid
  • Sort Method: top-N heapsort Memory: 29kB
  • Buffers: shared hit=173,136
4. 43.705 92,618.559 ↑ 11.6 3,568 1

Nested Loop Semi Join (cost=7,687,654.18..9,348,544.03 rows=41,384 width=260) (actual time=717.714..92,618.559 rows=3,568 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetslug, "*SELECT* 1".timesheetslug, "*SELECT* 1".owneruserid, "*SELECT* 1".owneruserdisplayname, "*SELECT* 1".owneruserslug, "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, CASE WHEN (hashed SubPlan 8) THEN "*SELECT* 1".totalregularhours ELSE NULL::interval END, CASE WHEN (hashed SubPlan 16) THEN "*SELECT* 1".totalovertimehours ELSE NULL::interval END, CASE WHEN (hashed SubPlan 24) THEN "*SELECT* 1".totaltimeoffhours ELSE NULL::interval END, "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus
  • Buffers: shared hit=173,130
5. 51,153.295 91,895.436 ↑ 23.2 3,568 1

Nested Loop Semi Join (cost=4,192.47..41,017.41 rows=82,768 width=175) (actual time=72.073..91,895.436 rows=3,568 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetslug, "*SELECT* 1".owneruserid, "*SELECT* 1".owneruserdisplayname, "*SELECT* 1".owneruserslug, "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, "*SELECT* 1".totalregularhours, "*SELECT* 1".totalovertimehours, "*SELECT* 1".totaltimeoffhours, "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus, timesheetapprovalhistory36.timesheetid
  • Join Filter: ("*SELECT* 1".timesheetid = timesheetapprovalhistory36.timesheetid)
  • Rows Removed by Join Filter: 701,461,355
  • Buffers: shared hit=24,335
6. 18.389 351.113 ↑ 1.0 165,537 1

Append (cost=0.56..15,462.21 rows=165,537 width=159) (actual time=0.046..351.113 rows=165,537 loops=1)

  • Buffers: shared hit=2,640
7. 29.595 332.718 ↓ 1.0 165,537 1

Subquery Scan on *SELECT* 1 (cost=0.56..15,448.81 rows=165,367 width=159) (actual time=0.046..332.718 rows=165,537 loops=1)

  • Output: "*SELECT* 1".timesheetid, "*SELECT* 1".timesheetslug, "*SELECT* 1".owneruserid, "*SELECT* 1".owneruserdisplayname, "*SELECT* 1".owneruserslug, "*SELECT* 1".timesheetstartdate, "*SELECT* 1".timesheetenddate, "*SELECT* 1".totalregularhours, "*SELECT* 1".totalovertimehours, "*SELECT* 1".totaltimeoffhours, "*SELECT* 1".totalhours, "*SELECT* 1".totalpayablehours, "*SELECT* 1".approvalstatus, "*SELECT* 1".timesheetstatus
  • Buffers: shared hit=2,639
8. 107.400 303.123 ↓ 1.0 165,537 1

Merge Anti Join (cost=0.56..13,795.14 rows=165,367 width=1,159) (actual time=0.046..303.123 rows=165,537 loops=1)

  • Output: dm_timesheetlist_facts.timesheetid, dm_timesheetlist_facts.timesheetslug, dm_timesheetlist_facts.timesheetstartdate, dm_timesheetlist_facts.timesheetenddate, dm_timesheetlist_facts.owneruserid, dm_timesheetlist_facts.owneruserslug, dm_timesheetlist_facts.owneruserdisplayname, NULL::text, NULL::integer, NULL::text, dm_timesheetlist_facts.approvalstatus, dm_timesheetlist_facts.timesheetstatus, NULL::integer, dm_timesheetlist_facts.totalregularhours, dm_timesheetlist_facts.totalovertimehours, dm_timesheetlist_facts.totaltimeoffhours, NULL::interval, NULL::interval, dm_timesheetlist_facts.totalhours, dm_timesheetlist_facts.totalpayablehours, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::text, NULL::timestamp without time zone, NULL::date, NULL::integer, NULL::text, NULL::text, NULL::numeric(19,4), NULL::integer, NULL::text, NULL::text, NULL::date, NULL::integer, NULL::interval, NULL::numeric(19,4), NULL::timestamp without time zone, NULL::character varying(255), NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::integer, NULL::text
  • Merge Cond: (dm_timesheetlist_facts.timesheetid = dm_timesheetlist_realtime_facts.timesheetid)
  • Buffers: shared hit=2,639
9. 195.706 195.706 ↑ 1.0 165,537 1

Index Scan using ixdtslsftimesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.dm_timesheetlist_facts (cost=0.42..13,364.48 rows=165,537 width=159) (actual time=0.026..195.706 rows=165,537 loops=1)

  • Output: dm_timesheetlist_facts.timesheetslug, dm_timesheetlist_facts.timesheetstartdate, dm_timesheetlist_facts.timesheetenddate, dm_timesheetlist_facts.owneruserid, dm_timesheetlist_facts.owneruserslug, dm_timesheetlist_facts.owneruserdisplayname, dm_timesheetlist_facts.owneruseremployeeid, dm_timesheetlist_facts.owneruseremployeetypeid, dm_timesheetlist_facts.owneruseremployeetypename, dm_timesheetlist_facts.approvalstatus, dm_timesheetlist_facts.timesheetstatus, dm_timesheetlist_facts.totalregularhours, dm_timesheetlist_facts.totalovertimehours, dm_timesheetlist_facts.totaltimeoffhours, dm_timesheetlist_facts.totalbillablehours, dm_timesheetlist_facts.totalnonbillablehours, dm_timesheetlist_facts.totalhours, dm_timesheetlist_facts.totalpayablehours, dm_timesheetlist_facts.totalprojecthours, dm_timesheetlist_facts.totalbreakhours, dm_timesheetlist_facts.totalworkinghours, dm_timesheetlist_facts.ownerdepartmentid, dm_timesheetlist_facts.ownerdepartmentslug, dm_timesheetlist_facts.generatedonutc, dm_timesheetlist_facts.duedate, dm_timesheetlist_facts.historicalsupervisoruserid, dm_timesheetlist_facts.historicalsupervisoruserslug, dm_timesheetlist_facts.historicalsupervisoruserdisplayname, dm_timesheetlist_facts.totalbillableamountinbasecurrency, dm_timesheetlist_facts.waitingonapproveruserid, dm_timesheetlist_facts.waitingonapproveruserslug, dm_timesheetlist_facts.waitingonapproveruserdisplayname, dm_timesheetlist_facts.invoicestatus, dm_timesheetlist_facts.invoicedhours, dm_timesheetlist_facts.invoicedamount, dm_timesheetlist_facts.approvalduedate, dm_timesheetlist_facts.owneruserloginname, dm_timesheetlist_facts.timesheetid, dm_timesheetlist_facts.totalscheduledworkhours, dm_timesheetlist_facts.totalscheduledbreakhours, dm_timesheetlist_facts.validationmessagecounterror, dm_timesheetlist_facts.validationmessagecountwarning, dm_timesheetlist_facts.validationmessagecountinfo, dm_timesheetlist_facts.ownerdepartmentname, dm_timesheetlist_facts.savedonutc, dm_timesheetlist_facts.timesheetscriptcalculationstatustype
  • Buffers: shared hit=2,637
10. 0.017 0.017 ↓ 0.0 0 1

Index Only Scan using dm_timesheetlist_realtime_facts_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.dm_timesheetlist_realtime_facts (cost=0.14..14.69 rows=170 width=16) (actual time=0.017..0.017 rows=0 loops=1)

  • Output: dm_timesheetlist_realtime_facts.timesheetid
  • Heap Fetches: 1
  • Buffers: shared hit=2
11. 0.000 0.006 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..13.40 rows=170 width=159) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: "*SELECT* 2".timesheetid, "*SELECT* 2".timesheetslug, "*SELECT* 2".owneruserid, "*SELECT* 2".owneruserdisplayname, "*SELECT* 2".owneruserslug, "*SELECT* 2".timesheetstartdate, "*SELECT* 2".timesheetenddate, "*SELECT* 2".totalregularhours, "*SELECT* 2".totalovertimehours, "*SELECT* 2".totaltimeoffhours, "*SELECT* 2".totalhours, "*SELECT* 2".totalpayablehours, "*SELECT* 2".approvalstatus, "*SELECT* 2".timesheetstatus
  • Buffers: shared hit=1
12. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on e42fa0cdf18e4ceba7ea5ab36f013e92.dm_timesheetlist_realtime_facts dm_timesheetlist_realtime_facts_1 (cost=0.00..11.70 rows=170 width=1,159) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: dm_timesheetlist_realtime_facts_1.timesheetid, dm_timesheetlist_realtime_facts_1.timesheetslug, dm_timesheetlist_realtime_facts_1.timesheetstartdate, dm_timesheetlist_realtime_facts_1.timesheetenddate, dm_timesheetlist_realtime_facts_1.owneruserid, dm_timesheetlist_realtime_facts_1.owneruserslug, dm_timesheetlist_realtime_facts_1.owneruserdisplayname, NULL::text, NULL::integer, NULL::text, dm_timesheetlist_realtime_facts_1.approvalstatus, dm_timesheetlist_realtime_facts_1.timesheetstatus, NULL::integer, dm_timesheetlist_realtime_facts_1.totalregularhours, dm_timesheetlist_realtime_facts_1.totalovertimehours, dm_timesheetlist_realtime_facts_1.totaltimeoffhours, NULL::interval, NULL::interval, dm_timesheetlist_realtime_facts_1.totalhours, dm_timesheetlist_realtime_facts_1.totalpayablehours, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::text, NULL::timestamp without time zone, NULL::date, NULL::integer, NULL::text, NULL::text, NULL::numeric(19,4), NULL::integer, NULL::text, NULL::text, NULL::date, NULL::integer, NULL::interval, NULL::numeric(19,4), NULL::timestamp without time zone, NULL::character varying(255), NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::integer, NULL::text
  • Filter: (NOT dm_timesheetlist_realtime_facts_1.deleted)
  • Buffers: shared hit=1
13. 40,327.678 40,391.028 ↓ 2,119.0 4,238 165,537

Materialize (cost=4,191.91..20,589.09 rows=2 width=16) (actual time=0.000..0.244 rows=4,238 loops=165,537)

  • Output: timesheetapprovalhistory36.timesheetid
  • Buffers: shared hit=21,695
14. 2.671 63.350 ↓ 2,141.5 4,283 1

Nested Loop (cost=4,191.91..20,589.08 rows=2 width=16) (actual time=7.037..63.350 rows=4,283 loops=1)

  • Output: timesheetapprovalhistory36.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=21,695
15. 1.709 34.981 ↓ 63.0 4,283 1

Hash Join (cost=4,191.35..20,421.46 rows=68 width=32) (actual time=7.016..34.981 rows=4,283 loops=1)

  • Output: timesheetapprovalhistory36.timesheetid, sheetapprovalhistorykeyvalue37.parentid
  • Inner Unique: true
  • Hash Cond: (sheetapprovalhistorykeyvalue37.timesheetapprovalhistoryid = timesheetapprovalhistory36.id)
  • Buffers: shared hit=246
16. 28.662 28.662 ↑ 2.8 4,677 1

Index Scan using ixtahkvuri on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue37 (cost=0.56..16,196.20 rows=13,130 width=32) (actual time=2.365..28.662 rows=4,677 loops=1)

  • Output: sheetapprovalhistorykeyvalue37.id, sheetapprovalhistorykeyvalue37.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue37.parentid, sheetapprovalhistorykeyvalue37.index, sheetapprovalhistorykeyvalue37.key, sheetapprovalhistorykeyvalue37.uri, sheetapprovalhistorykeyvalue37.slug, sheetapprovalhistorykeyvalue37."boolean", sheetapprovalhistorykeyvalue37.date, sheetapprovalhistorykeyvalue37.number, sheetapprovalhistorykeyvalue37.text, sheetapprovalhistorykeyvalue37."time", sheetapprovalhistorykeyvalue37.timespan, sheetapprovalhistorykeyvalue37.daterange_startdate, sheetapprovalhistorykeyvalue37.daterange_enddate, sheetapprovalhistorykeyvalue37.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue37.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue37.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue37.workdayduration_workdays, sheetapprovalhistorykeyvalue37.workdayduration_hours, sheetapprovalhistorykeyvalue37.workdayduration_minutes
  • Index Cond: (upper(sheetapprovalhistorykeyvalue37.uri) = 'URN:REPLICON-TENANT:E42FA0CDF18E4CEBA7EA5AB36F013E92:USER:1,578'::text)
  • Filter: (upper(sheetapprovalhistorykeyvalue37.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
  • Rows Removed by Filter: 15,213
  • Buffers: shared hit=220
17. 0.876 4.610 ↓ 1.0 4,935 1

Hash (cost=4,131.38..4,131.38 rows=4,753 width=32) (actual time=4.610..4.610 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory36.id, timesheetapprovalhistory36.timesheetid
  • Buckets: 8,192 Batches: 1 Memory Usage: 373kB
  • Buffers: shared hit=26
18. 3.734 3.734 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory36 (cost=0.42..4,131.38 rows=4,753 width=32) (actual time=0.020..3.734 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory36.id, timesheetapprovalhistory36.timesheetid
  • Index Cond: (timesheetapprovalhistory36.userid = 1,578)
  • Buffers: shared hit=26
19. 25.698 25.698 ↑ 1.0 1 4,283

Index Scan using timesheetapprovalhistorykeyvalue_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue38 (cost=0.56..2.47 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=4,283)

  • Output: sheetapprovalhistorykeyvalue38.id, sheetapprovalhistorykeyvalue38.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue38.parentid, sheetapprovalhistorykeyvalue38.index, sheetapprovalhistorykeyvalue38.key, sheetapprovalhistorykeyvalue38.uri, sheetapprovalhistorykeyvalue38.slug, sheetapprovalhistorykeyvalue38."boolean", sheetapprovalhistorykeyvalue38.date, sheetapprovalhistorykeyvalue38.number, sheetapprovalhistorykeyvalue38.text, sheetapprovalhistorykeyvalue38."time", sheetapprovalhistorykeyvalue38.timespan, sheetapprovalhistorykeyvalue38.daterange_startdate, sheetapprovalhistorykeyvalue38.daterange_enddate, sheetapprovalhistorykeyvalue38.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue38.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue38.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue38.workdayduration_workdays, sheetapprovalhistorykeyvalue38.workdayduration_hours, sheetapprovalhistorykeyvalue38.workdayduration_minutes
  • Index Cond: (sheetapprovalhistorykeyvalue38.id = sheetapprovalhistorykeyvalue37.parentid)
  • Filter: (upper(sheetapprovalhistorykeyvalue38.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
  • Buffers: shared hit=21,449
20. 18.064 57.088 ↑ 1.0 1 3,568

Nested Loop Left Join (cost=6.56..21.95 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=3,568)

  • Output: timesheet29.id
  • Filter: ((hashed SubPlan 32) OR (timesheet29.userid = 1,578) OR (alternatives: SubPlan 33 or hashed SubPlan 34) OR (alternatives: SubPlan 35 or hashed SubPlan 36) OR (alternatives: SubPlan 37 or hashed SubPlan 38))
  • Buffers: shared hit=26,434
21. 21.408 21.408 ↑ 1.0 1 3,568

Index Scan using timesheet_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheet timesheet29 (cost=0.42..0.45 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=3,568)

  • Output: timesheet29.id, timesheet29.userid, timesheet29.startdate, timesheet29.enddate, timesheet29.approvalstatus, timesheet29.duedate, timesheet29.autosubmitdatetimeutc, timesheet29.lastautosubmitattemptdatetimeutc, timesheet29.createdonutc
  • Index Cond: (timesheet29.id = "*SELECT* 1".timesheetid)
  • Buffers: shared hit=14,296
22. 14.272 14.272 ↑ 1.0 1 3,568

Index Only Scan using ix4ul_userlocationstartend on e42fa0cdf18e4ceba7ea5ab36f013e92.userlocation userlocation30 (cost=0.28..0.30 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=3,568)

  • Output: userlocation30.userid, userlocation30.locationid, userlocation30.startdate, userlocation30.enddate
  • Index Cond: ((userlocation30.userid = timesheet29.userid) AND (userlocation30.startdate <= timesheet29.enddate) AND (userlocation30.enddate >= timesheet29.startdate))
  • Heap Fetches: 3,568
  • Buffers: shared hit=10,704
23.          

SubPlan (for Nested Loop Left Join)

24. 0.050 0.050 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.locationflathierarchy locationflathierarchy31 (cost=0.27..5.86 rows=3 width=16) (actual time=0.045..0.050 rows=3 loops=1)

  • Output: locationflathierarchy31.childid
  • Index Cond: (locationflathierarchy31.parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 3
  • Buffers: shared hit=10
25. 0.965 0.965 ↓ 0.0 0 193

Index Scan using ix3uh_usersuperstart on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy32 (cost=0.29..2.31 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=193)

  • Index Cond: ((userhierarchy32.userid = timesheet29.userid) AND (userhierarchy32.supervisorid = 1,578) AND (userhierarchy32.startdate <= '2020-08-31'::date))
  • Filter: (userhierarchy32.enddate >= '2020-08-31'::date)
  • Buffers: shared hit=386
26. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuhsupervisorid on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy32_1 (cost=0.29..16.64 rows=3 width=4) (never executed)

  • Output: userhierarchy32_1.userid
  • Index Cond: (userhierarchy32_1.supervisorid = 1,578)
  • Filter: ((userhierarchy32_1.startdate <= '2020-08-31'::date) AND (userhierarchy32_1.enddate >= '2020-08-31'::date))
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..5.47 rows=1 width=0) (never executed)

  • Inner Unique: true
28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest33 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Output: tsapprovalrequest33.userid, tsapprovalrequest33.nodeid
  • Index Cond: (tsapprovalrequest33.userid = 1,578)
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes34 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Output: timesheetapprovalnodes34.id, timesheetapprovalnodes34.isapproved, timesheetapprovalnodes34.approverrole, timesheetapprovalnodes34.userid, timesheetapprovalnodes34.timesheetid, timesheetapprovalnodes34.systemprocessidentifier, timesheetapprovalnodes34.scriptid, timesheetapprovalnodes34.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes34.id = tsapprovalrequest33.nodeid)
  • Filter: (timesheetapprovalnodes34.timesheetid = timesheet29.id)
30. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.46 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: timesheetapprovalnodes34_1.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=1
31. 0.013 0.013 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest33_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Output: tsapprovalrequest33_1.userid, tsapprovalrequest33_1.nodeid
  • Index Cond: (tsapprovalrequest33_1.userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=1
32. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes34_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Output: timesheetapprovalnodes34_1.id, timesheetapprovalnodes34_1.isapproved, timesheetapprovalnodes34_1.approverrole, timesheetapprovalnodes34_1.userid, timesheetapprovalnodes34_1.timesheetid, timesheetapprovalnodes34_1.systemprocessidentifier, timesheetapprovalnodes34_1.scriptid, timesheetapprovalnodes34_1.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes34_1.id = tsapprovalrequest33_1.nodeid)
33. 2.316 2.316 ↑ 1.0 1 193

Index Scan using ixtah2timesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory35 (cost=0.42..7.54 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=193)

  • Index Cond: (timesheetapprovalhistory35.timesheetid = timesheet29.id)
  • Filter: (timesheetapprovalhistory35.userid = 1,578)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1,037
34. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory35_1 (cost=0.42..4,131.38 rows=4,753 width=16) (never executed)

  • Output: timesheetapprovalhistory35_1.timesheetid
  • Index Cond: (timesheetapprovalhistory35_1.userid = 1,578)
35.          

SubPlan (for Nested Loop Semi Join)

36. 102.840 212.875 ↑ 9.7 16,037 1

Merge Left Join (cost=7.79..2,560,763.71 rows=155,203 width=16) (actual time=7.525..212.875 rows=16,037 loops=1)

  • Output: timesheet1.id
  • Merge Cond: (timesheet1.userid = userlocation2.userid)
  • Join Filter: ((userlocation2.startdate <= timesheet1.enddate) AND (userlocation2.enddate >= timesheet1.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 1) OR (timesheet1.userid = 1,578) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5) OR (alternatives: SubPlan 6 or hashed SubPlan 7))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=40,787
37. 94.134 94.134 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheet timesheet1 (cost=0.42..4,682.48 rows=165,537 width=28) (actual time=0.038..94.134 rows=165,537 loops=1)

  • Output: timesheet1.id, timesheet1.userid, timesheet1.startdate, timesheet1.enddate, timesheet1.approvalstatus, timesheet1.duedate, timesheet1.autosubmitdatetimeutc, timesheet1.lastautosubmitattemptdatetimeutc, timesheet1.createdonutc
  • Buffers: shared hit=39,456
38. 10.017 11.193 ↓ 85.3 166,790 1

Materialize (cost=0.28..67.49 rows=1,955 width=28) (actual time=0.027..11.193 rows=166,790 loops=1)

  • Output: userlocation2.userid, userlocation2.locationid, userlocation2.startdate, userlocation2.enddate
  • Buffers: shared hit=1,291
39. 1.176 1.176 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on e42fa0cdf18e4ceba7ea5ab36f013e92.userlocation userlocation2 (cost=0.28..62.60 rows=1,955 width=28) (actual time=0.024..1.176 rows=1,955 loops=1)

  • Output: userlocation2.userid, userlocation2.locationid, userlocation2.startdate, userlocation2.enddate
  • Heap Fetches: 1,955
  • Buffers: shared hit=1,291
40.          

SubPlan (for Merge Left Join)

41. 0.013 0.013 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.locationflathierarchy locationflathierarchy3 (cost=0.27..5.86 rows=3 width=16) (actual time=0.010..0.013 rows=3 loops=1)

  • Output: locationflathierarchy3.childid
  • Index Cond: (locationflathierarchy3.parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 3
  • Buffers: shared hit=7
42. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy4 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userhierarchy4.userid = timesheet1.userid) AND (userhierarchy4.supervisorid = 1,578) AND (userhierarchy4.startdate <= '2020-08-31'::date))
  • Filter: (userhierarchy4.enddate >= '2020-08-31'::date)
43. 0.025 0.025 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy4_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.020..0.025 rows=4 loops=1)

  • Output: userhierarchy4_1.userid
  • Index Cond: (userhierarchy4_1.supervisorid = 1,578)
  • Filter: ((userhierarchy4_1.startdate <= '2020-08-31'::date) AND (userhierarchy4_1.enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
44. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..5.47 rows=1 width=0) (never executed)

  • Inner Unique: true
45. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest5 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Output: tsapprovalrequest5.userid, tsapprovalrequest5.nodeid
  • Index Cond: (tsapprovalrequest5.userid = 1,578)
  • Heap Fetches: 0
46. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes6 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Output: timesheetapprovalnodes6.id, timesheetapprovalnodes6.isapproved, timesheetapprovalnodes6.approverrole, timesheetapprovalnodes6.userid, timesheetapprovalnodes6.timesheetid, timesheetapprovalnodes6.systemprocessidentifier, timesheetapprovalnodes6.scriptid, timesheetapprovalnodes6.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes6.id = tsapprovalrequest5.nodeid)
  • Filter: (timesheetapprovalnodes6.timesheetid = timesheet1.id)
47. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.46 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: timesheetapprovalnodes6_1.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=1
48. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest5_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: tsapprovalrequest5_1.userid, tsapprovalrequest5_1.nodeid
  • Index Cond: (tsapprovalrequest5_1.userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=1
49. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes6_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Output: timesheetapprovalnodes6_1.id, timesheetapprovalnodes6_1.isapproved, timesheetapprovalnodes6_1.approverrole, timesheetapprovalnodes6_1.userid, timesheetapprovalnodes6_1.timesheetid, timesheetapprovalnodes6_1.systemprocessidentifier, timesheetapprovalnodes6_1.scriptid, timesheetapprovalnodes6_1.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes6_1.id = tsapprovalrequest5_1.nodeid)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory7 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetapprovalhistory7.timesheetid = timesheet1.id)
  • Filter: (timesheetapprovalhistory7.userid = 1,578)
51. 4.659 4.659 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory7_1 (cost=0.42..4,131.38 rows=4,753 width=16) (actual time=0.023..4.659 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory7_1.timesheetid
  • Index Cond: (timesheetapprovalhistory7_1.userid = 1,578)
  • Buffers: shared hit=26
52. 98.353 205.207 ↑ 9.7 16,037 1

Merge Left Join (cost=7.79..2,560,763.71 rows=155,203 width=16) (actual time=6.026..205.207 rows=16,037 loops=1)

  • Output: timesheet8.id
  • Merge Cond: (timesheet8.userid = userlocation9.userid)
  • Join Filter: ((userlocation9.startdate <= timesheet8.enddate) AND (userlocation9.enddate >= timesheet8.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 9) OR (timesheet8.userid = 1,578) OR (alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13) OR (alternatives: SubPlan 14 or hashed SubPlan 15))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=40,787
53. 88.578 88.578 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheet timesheet8 (cost=0.42..4,682.48 rows=165,537 width=28) (actual time=0.046..88.578 rows=165,537 loops=1)

  • Output: timesheet8.id, timesheet8.userid, timesheet8.startdate, timesheet8.enddate, timesheet8.approvalstatus, timesheet8.duedate, timesheet8.autosubmitdatetimeutc, timesheet8.lastautosubmitattemptdatetimeutc, timesheet8.createdonutc
  • Buffers: shared hit=39,456
54. 13.203 14.321 ↓ 85.3 166,790 1

Materialize (cost=0.28..67.49 rows=1,955 width=28) (actual time=0.033..14.321 rows=166,790 loops=1)

  • Output: userlocation9.userid, userlocation9.locationid, userlocation9.startdate, userlocation9.enddate
  • Buffers: shared hit=1,291
55. 1.118 1.118 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on e42fa0cdf18e4ceba7ea5ab36f013e92.userlocation userlocation9 (cost=0.28..62.60 rows=1,955 width=28) (actual time=0.030..1.118 rows=1,955 loops=1)

  • Output: userlocation9.userid, userlocation9.locationid, userlocation9.startdate, userlocation9.enddate
  • Heap Fetches: 1,955
  • Buffers: shared hit=1,291
56.          

SubPlan (for Merge Left Join)

57. 0.028 0.028 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.locationflathierarchy locationflathierarchy10 (cost=0.27..5.86 rows=3 width=16) (actual time=0.024..0.028 rows=3 loops=1)

  • Output: locationflathierarchy10.childid
  • Index Cond: (locationflathierarchy10.parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 3
  • Buffers: shared hit=7
58. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy11 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userhierarchy11.userid = timesheet8.userid) AND (userhierarchy11.supervisorid = 1,578) AND (userhierarchy11.startdate <= '2020-08-31'::date))
  • Filter: (userhierarchy11.enddate >= '2020-08-31'::date)
59. 0.033 0.033 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy11_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.027..0.033 rows=4 loops=1)

  • Output: userhierarchy11_1.userid
  • Index Cond: (userhierarchy11_1.supervisorid = 1,578)
  • Filter: ((userhierarchy11_1.startdate <= '2020-08-31'::date) AND (userhierarchy11_1.enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..5.47 rows=1 width=0) (never executed)

  • Inner Unique: true
61. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest12 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Output: tsapprovalrequest12.userid, tsapprovalrequest12.nodeid
  • Index Cond: (tsapprovalrequest12.userid = 1,578)
  • Heap Fetches: 0
62. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes13 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Output: timesheetapprovalnodes13.id, timesheetapprovalnodes13.isapproved, timesheetapprovalnodes13.approverrole, timesheetapprovalnodes13.userid, timesheetapprovalnodes13.timesheetid, timesheetapprovalnodes13.systemprocessidentifier, timesheetapprovalnodes13.scriptid, timesheetapprovalnodes13.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes13.id = tsapprovalrequest12.nodeid)
  • Filter: (timesheetapprovalnodes13.timesheetid = timesheet8.id)
63. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.46 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: timesheetapprovalnodes13_1.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=1
64. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest12_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: tsapprovalrequest12_1.userid, tsapprovalrequest12_1.nodeid
  • Index Cond: (tsapprovalrequest12_1.userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=1
65. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes13_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Output: timesheetapprovalnodes13_1.id, timesheetapprovalnodes13_1.isapproved, timesheetapprovalnodes13_1.approverrole, timesheetapprovalnodes13_1.userid, timesheetapprovalnodes13_1.timesheetid, timesheetapprovalnodes13_1.systemprocessidentifier, timesheetapprovalnodes13_1.scriptid, timesheetapprovalnodes13_1.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes13_1.id = tsapprovalrequest12_1.nodeid)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory14 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetapprovalhistory14.timesheetid = timesheet8.id)
  • Filter: (timesheetapprovalhistory14.userid = 1,578)
67. 3.882 3.882 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory14_1 (cost=0.42..4,131.38 rows=4,753 width=16) (actual time=0.029..3.882 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory14_1.timesheetid
  • Index Cond: (timesheetapprovalhistory14_1.userid = 1,578)
  • Buffers: shared hit=26
68. 97.981 204.248 ↑ 9.7 16,037 1

Merge Left Join (cost=7.79..2,560,763.71 rows=155,203 width=16) (actual time=5.551..204.248 rows=16,037 loops=1)

  • Output: timesheet15.id
  • Merge Cond: (timesheet15.userid = userlocation16.userid)
  • Join Filter: ((userlocation16.startdate <= timesheet15.enddate) AND (userlocation16.enddate >= timesheet15.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 17) OR (timesheet15.userid = 1,578) OR (alternatives: SubPlan 18 or hashed SubPlan 19) OR (alternatives: SubPlan 20 or hashed SubPlan 21) OR (alternatives: SubPlan 22 or hashed SubPlan 23))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=40,787
69. 90.488 90.488 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheet timesheet15 (cost=0.42..4,682.48 rows=165,537 width=28) (actual time=0.042..90.488 rows=165,537 loops=1)

  • Output: timesheet15.id, timesheet15.userid, timesheet15.startdate, timesheet15.enddate, timesheet15.approvalstatus, timesheet15.duedate, timesheet15.autosubmitdatetimeutc, timesheet15.lastautosubmitattemptdatetimeutc, timesheet15.createdonutc
  • Buffers: shared hit=39,456
70. 10.994 12.135 ↓ 85.3 166,790 1

Materialize (cost=0.28..67.49 rows=1,955 width=28) (actual time=0.035..12.135 rows=166,790 loops=1)

  • Output: userlocation16.userid, userlocation16.locationid, userlocation16.startdate, userlocation16.enddate
  • Buffers: shared hit=1,291
71. 1.141 1.141 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on e42fa0cdf18e4ceba7ea5ab36f013e92.userlocation userlocation16 (cost=0.28..62.60 rows=1,955 width=28) (actual time=0.026..1.141 rows=1,955 loops=1)

  • Output: userlocation16.userid, userlocation16.locationid, userlocation16.startdate, userlocation16.enddate
  • Heap Fetches: 1,955
  • Buffers: shared hit=1,291
72.          

SubPlan (for Merge Left Join)

73. 0.028 0.028 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.locationflathierarchy locationflathierarchy17 (cost=0.27..5.86 rows=3 width=16) (actual time=0.023..0.028 rows=3 loops=1)

  • Output: locationflathierarchy17.childid
  • Index Cond: (locationflathierarchy17.parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 3
  • Buffers: shared hit=7
74. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy18 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userhierarchy18.userid = timesheet15.userid) AND (userhierarchy18.supervisorid = 1,578) AND (userhierarchy18.startdate <= '2020-08-31'::date))
  • Filter: (userhierarchy18.enddate >= '2020-08-31'::date)
75. 0.028 0.028 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on e42fa0cdf18e4ceba7ea5ab36f013e92.userhierarchy userhierarchy18_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.022..0.028 rows=4 loops=1)

  • Output: userhierarchy18_1.userid
  • Index Cond: (userhierarchy18_1.supervisorid = 1,578)
  • Filter: ((userhierarchy18_1.startdate <= '2020-08-31'::date) AND (userhierarchy18_1.enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..5.47 rows=1 width=0) (never executed)

  • Inner Unique: true
77. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest19 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Output: tsapprovalrequest19.userid, tsapprovalrequest19.nodeid
  • Index Cond: (tsapprovalrequest19.userid = 1,578)
  • Heap Fetches: 0
78. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes20 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Output: timesheetapprovalnodes20.id, timesheetapprovalnodes20.isapproved, timesheetapprovalnodes20.approverrole, timesheetapprovalnodes20.userid, timesheetapprovalnodes20.timesheetid, timesheetapprovalnodes20.systemprocessidentifier, timesheetapprovalnodes20.scriptid, timesheetapprovalnodes20.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes20.id = tsapprovalrequest19.nodeid)
  • Filter: (timesheetapprovalnodes20.timesheetid = timesheet15.id)
79. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.46 rows=1 width=16) (actual time=0.012..0.013 rows=0 loops=1)

  • Output: timesheetapprovalnodes20_1.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=1
80. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest19_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: tsapprovalrequest19_1.userid, tsapprovalrequest19_1.nodeid
  • Index Cond: (tsapprovalrequest19_1.userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=1
81. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes20_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Output: timesheetapprovalnodes20_1.id, timesheetapprovalnodes20_1.isapproved, timesheetapprovalnodes20_1.approverrole, timesheetapprovalnodes20_1.userid, timesheetapprovalnodes20_1.timesheetid, timesheetapprovalnodes20_1.systemprocessidentifier, timesheetapprovalnodes20_1.scriptid, timesheetapprovalnodes20_1.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes20_1.id = tsapprovalrequest19_1.nodeid)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory21 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetapprovalhistory21.timesheetid = timesheet15.id)
  • Filter: (timesheetapprovalhistory21.userid = 1,578)
83. 3.575 3.575 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory21_1 (cost=0.42..4,131.38 rows=4,753 width=16) (actual time=0.025..3.575 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory21_1.timesheetid
  • Index Cond: (timesheetapprovalhistory21_1.userid = 1,578)
  • Buffers: shared hit=26
84.          

SubPlan (for Result)

85. 89.122 208.947 ↑ 9.0 16,037 1

Merge Left Join (cost=7.79..2,175,633.02 rows=144,845 width=16) (actual time=6.045..208.947 rows=16,037 loops=1)

  • Output: timesheet22.id
  • Merge Cond: (timesheet22.userid = userlocation23.userid)
  • Join Filter: ((userlocation23.startdate <= timesheet22.enddate) AND (userlocation23.enddate >= timesheet22.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27) OR (alternatives: SubPlan 28 or hashed SubPlan 29))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=40,781
86. 101.430 101.430 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheet timesheet22 (cost=0.42..4,682.48 rows=165,537 width=28) (actual time=0.049..101.430 rows=165,537 loops=1)

  • Output: timesheet22.id, timesheet22.userid, timesheet22.startdate, timesheet22.enddate, timesheet22.approvalstatus, timesheet22.duedate, timesheet22.autosubmitdatetimeutc, timesheet22.lastautosubmitattemptdatetimeutc, timesheet22.createdonutc
  • Buffers: shared hit=39,456
87. 13.243 14.474 ↓ 85.3 166,790 1

Materialize (cost=0.28..67.49 rows=1,955 width=28) (actual time=0.038..14.474 rows=166,790 loops=1)

  • Output: userlocation23.userid, userlocation23.locationid, userlocation23.startdate, userlocation23.enddate
  • Buffers: shared hit=1,291
88. 1.231 1.231 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on e42fa0cdf18e4ceba7ea5ab36f013e92.userlocation userlocation23 (cost=0.28..62.60 rows=1,955 width=28) (actual time=0.034..1.231 rows=1,955 loops=1)

  • Output: userlocation23.userid, userlocation23.locationid, userlocation23.startdate, userlocation23.enddate
  • Heap Fetches: 1,955
  • Buffers: shared hit=1,291
89.          

SubPlan (for Merge Left Join)

90. 0.028 0.028 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.locationflathierarchy locationflathierarchy24 (cost=0.27..5.86 rows=3 width=16) (actual time=0.022..0.028 rows=3 loops=1)

  • Output: locationflathierarchy24.childid
  • Index Cond: (locationflathierarchy24.parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 3
  • Buffers: shared hit=7
91. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..5.47 rows=1 width=0) (never executed)

  • Inner Unique: true
92. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest25 (cost=0.14..2.16 rows=1 width=16) (never executed)

  • Output: tsapprovalrequest25.userid, tsapprovalrequest25.nodeid
  • Index Cond: (tsapprovalrequest25.userid = 1,578)
  • Heap Fetches: 0
93. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes26 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Output: timesheetapprovalnodes26.id, timesheetapprovalnodes26.isapproved, timesheetapprovalnodes26.approverrole, timesheetapprovalnodes26.userid, timesheetapprovalnodes26.timesheetid, timesheetapprovalnodes26.systemprocessidentifier, timesheetapprovalnodes26.scriptid, timesheetapprovalnodes26.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes26.id = tsapprovalrequest25.nodeid)
  • Filter: (timesheetapprovalnodes26.timesheetid = timesheet22.id)
94. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.46 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: timesheetapprovalnodes26_1.timesheetid
  • Inner Unique: true
  • Buffers: shared hit=1
95. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on e42fa0cdf18e4ceba7ea5ab36f013e92.tsapprovalrequest tsapprovalrequest25_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: tsapprovalrequest25_1.userid, tsapprovalrequest25_1.nodeid
  • Index Cond: (tsapprovalrequest25_1.userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=1
96. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalnodes timesheetapprovalnodes26_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Output: timesheetapprovalnodes26_1.id, timesheetapprovalnodes26_1.isapproved, timesheetapprovalnodes26_1.approverrole, timesheetapprovalnodes26_1.userid, timesheetapprovalnodes26_1.timesheetid, timesheetapprovalnodes26_1.systemprocessidentifier, timesheetapprovalnodes26_1.scriptid, timesheetapprovalnodes26_1.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes26_1.id = tsapprovalrequest25_1.nodeid)
97. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory27 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetapprovalhistory27.timesheetid = timesheet22.id)
  • Filter: (timesheetapprovalhistory27.userid = 1,578)
98. 3.881 3.881 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on e42fa0cdf18e4ceba7ea5ab36f013e92.timesheetapprovalhistory timesheetapprovalhistory27_1 (cost=0.42..4,131.38 rows=4,753 width=16) (actual time=0.027..3.881 rows=4,935 loops=1)

  • Output: timesheetapprovalhistory27_1.timesheetid
  • Index Cond: (timesheetapprovalhistory27_1.userid = 1,578)
  • Buffers: shared hit=26
99. 0.070 0.070 ↑ 1.0 1 10

Index Scan using uix2dtslpl_tsid_plid on e42fa0cdf18e4ceba7ea5ab36f013e92.dm_timesheetlist_projectleadertime_facts list_projectleadertime_facts28 (cost=0.42..2.44 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=10)

  • Output: list_projectleadertime_facts28.projectleadertotalhours
  • Index Cond: ((list_projectleadertime_facts28.timesheetid = "*SELECT* 1".timesheetid) AND (list_projectleadertime_facts28.projectleaderid = 1,578))
  • Buffers: shared hit=40
Planning time : 8.039 ms
Execution time : 92,843.899 ms