explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vIHo : logicinfo_before

Settings
# exclusive inclusive rows x rows loops node
1. 0.129 4,924,615.858 ↓ 9.0 9 1

Sort (cost=1,010,858.52..1,010,858.53 rows=1 width=3,121) (actual time=4,924,615.857..4,924,615.858 rows=9 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, ((servicecenter6.name)::character varying(50)), ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), dmv_timesheetday_facts5.userduplicatename5, ((login.loginname)::character varying(255)), ui.id, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, ((pj.name)::character varying(255)), pj.id, ((tk.name)::character varying(255)), tk.id, ((pj.info20)::character varying(255)), ((pj.info7)::character varying(255)), ((tk.code)::character varying(50)), dmv_timesheetday_facts5.userid3
  • Sort Key: dmv_timesheetday_facts5.timesheetenddate4, ((servicecenter6.name)::character varying(50)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((pj.info20)::character varying(255)) COLLATE "en_US", ((pj.info7)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 27kB
2.          

CTE dmv_timesheetday_facts0cte

3. 0.201 4,924,529.370 ↓ 60.0 60 1

GroupAggregate (cost=1,010,841.36..1,010,841.39 rows=1 width=81) (actual time=4,924,529.133..4,924,529.370 rows=60 loops=1)

  • Output: sum((CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END)), sum((CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END)), sum((CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END)), ts_1.userid, ts_2.enddate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id
  • Group Key: ts_2.enddate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id
4. 0.461 4,924,529.169 ↓ 232.0 232 1

Sort (cost=1,010,841.36..1,010,841.36 rows=1 width=81) (actual time=4,924,529.110..4,924,529.169 rows=232 loops=1)

  • Output: ts_2.enddate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id, (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), ts_1.userid
  • Sort Key: ts_2.enddate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id
  • Sort Method: quicksort Memory: 57kB
5. 0.315 4,924,528.708 ↓ 232.0 232 1

Nested Loop (cost=930.86..1,010,841.35 rows=1 width=81) (actual time=76,763.299..4,924,528.708 rows=232 loops=1)

  • Output: ts_2.enddate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id, (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), ts_1.userid
6. 0.286 4,924,527.697 ↓ 232.0 232 1

Nested Loop (cost=930.58..1,010,839.04 rows=1 width=80) (actual time=76,763.290..4,924,527.697 rows=232 loops=1)

  • Output: ts_1.userid, ts_1.id, (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), at.projectid, at.taskid, ts_2.enddate
  • Join Filter: (ts_1.id = tslist_1.timesheetid)
7. 0.336 4,924,526.019 ↓ 232.0 232 1

Nested Loop (cost=930.16..1,010,838.58 rows=1 width=112) (actual time=76,763.247..4,924,526.019 rows=232 loops=1)

  • Output: ts_1.userid, ts_1.id, (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), at.projectid, at.taskid, at.timesheetid, ts_2.enddate, ts_2.id
  • Join Filter: (ts_1.id = ts_2.id)
8. 1,629.251 4,924,524.523 ↓ 232.0 232 1

Nested Loop (cost=929.74..1,010,838.13 rows=1 width=92) (actual time=76,763.226..4,924,524.523 rows=232 loops=1)

  • Output: ts_1.userid, ts_1.id, (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), at.projectid, at.taskid, at.timesheetid
  • Join Filter: (at.timesheetid = ts_1.id)
  • Rows Removed by Join Filter: 11,264,087
9. 9.128 9.128 ↓ 759.0 759 1

Index Scan using uix2tsuseridstartdate on logicinforef.timesheet ts_1 (cost=0.42..3,436.52 rows=1 width=20) (actual time=0.101..9.128 rows=759 loops=1)

  • Output: ts_1.id, ts_1.userid, ts_1.startdate, ts_1.enddate, ts_1.approvalstatus, ts_1.duedate, ts_1.autosubmitdatetimeutc, ts_1.lastautosubmitattemptdatetimeutc, ts_1.createdonutc
  • Index Cond: ((ts_1.startdate >= '2019-09-22'::date) AND (ts_1.startdate <= '2019-09-28'::date))
  • Filter: ((ts_1.enddate >= '2019-09-22'::date) AND (ts_1.enddate <= '2019-09-28'::date))
10. 140,145.556 4,922,886.144 ↓ 4.1 14,841 759

Hash Semi Join (cost=929.32..1,007,355.93 rows=3,655 width=72) (actual time=4.319..6,486.016 rows=14,841 loops=759)

  • Output: (CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), (CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), at.projectid, at.taskid, at.timesheetid
  • Hash Cond: (at.taskid = task4.id)
11. 4,214,146.670 4,782,739.830 ↓ 1.0 1,695,546 759

Hash Left Join (cost=651.98..985,635.55 rows=1,695,238 width=616) (actual time=0.113..6,301.370 rows=1,695,546 loops=759)

  • Output: NULL::uuid, NULL::integer, NULL::date, NULL::uuid, NULL::interval, NULL::text, NULL::integer, NULL::date, at.projectid, at.taskid, NULL::integer, NULL::integer, NULL::uuid, NULL::interval, NULL::interval, NULL::interval, CASE WHEN (at.timeoffcodeid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END, NULL::interval, NULL::interval, CASE WHEN ((at.timeoffcodeid IS NULL) AND logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END, CASE WHEN ((at.timeoffcodeid IS NULL) AND (NOT logicinforef.timeallocationisbillableordefault(at.isbillable, at.projectid)) AND (at.breaktypeid IS NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::timestamp without time zone, at.timesheetid, NULL::uuid
  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
12. 200,964.390 568,588.911 ↓ 1.0 1,695,546 759

Hash Left Join (cost=235.48..90,916.16 rows=1,695,238 width=65) (actual time=0.106..749.129 rows=1,695,546 loops=759)

  • Output: at.projectid, at.taskid, at.timeoffcodeid, at.duration, at.isbillable, at.breaktypeid, at.timesheetid, at.entrydate
  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
13. 367,623.168 367,623.168 ↓ 1.0 1,695,546 759

Seq Scan on logicinforef.dm_attendancetimeallocation_facts at (cost=0.00..77,966.38 rows=1,695,238 width=69) (actual time=0.104..484.352 rows=1,695,546 loops=759)

  • Output: at.projectid, at.taskid, at.timeoffcodeid, at.duration, at.isbillable, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid
14. 0.004 1.353 ↑ 1.0 2 1

Hash (cost=235.45..235.45 rows=2 width=16) (actual time=1.353..1.353 rows=2 loops=1)

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.006 1.349 ↑ 1.0 2 1

Nested Loop (cost=0.28..235.45 rows=2 width=16) (actual time=0.432..1.349 rows=2 loops=1)

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
16. 1.305 1.305 ↑ 1.0 2 1

Seq Scan on logicinforef.project pj_2 (cost=0.00..230.84 rows=2 width=4) (actual time=0.394..1.305 rows=2 loops=1)

  • Output: pj_2.id, pj_2.timeentrycappercent, pj_2.info1, pj_2.info2, pj_2.info3, pj_2.info4, pj_2.info5, pj_2.info6, pj_2.info7, pj_2.info8, pj_2.info9, pj_2.info10, pj_2.info11, pj_2.info12, pj_2.info13, pj_2.info14, pj_2.info15, pj_2.info16, pj_2.info17, pj_2.info18, pj_2.info19, pj_2.info20, pj_2.name, pj_2.slug, pj_2.code, pj_2.clientid, pj_2.projectleaderapproverid, pj_2.isprojectleaderapprovalrequired, pj_2.startdate, pj_2.enddate, pj_2.programid, pj_2.billingtype, pj_2.fixedbidamount, pj_2.fixedbidcurrencyid, pj_2.fixedbidfrequency, pj_2.costtype, pj_2.description, pj_2.percentcomplete, pj_2.istimeentryallowed, pj_2.estimatedhours, pj_2.estimatedexpenses, pj_2.estimatedexpensescurrencyid, pj_2.estimatedcost, pj_2.estimatedcostcurrencyid, pj_2.timeandexpenseentrytype, pj_2.estimationmode, pj_2.projectstatuslabelid, pj_2.billingratefrequencyid, pj_2.billingratefrequencyduration, pj_2.defaultbillingcurrencyid, pj_2.clientbillingallocationmethod, pj_2.billingcontractid, pj_2.guidid, pj_2.totalestimatedcontractamount, pj_2.totalestimatedcontractcurrencyid, pj_2.budgetedhours, pj_2.budgetedcost, pj_2.budgetedcostcurrencyid, pj_2.projecthealthstateid, pj_2.projecthealthstatedescription
  • Filter: (pj_2.clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 3,116
17. 0.038 0.038 ↑ 1.0 1 2

Index Only Scan using uix4pc_projectclienteffectiveend on logicinforef.projectclient pc_1 (cost=0.28..2.30 rows=1 width=16) (actual time=0.019..0.019 rows=1 loops=2)

  • Output: pc_1.projectid, pc_1.clientid, pc_1.effectivedate, pc_1.enddate
  • Index Cond: (pc_1.projectid = pj_2.id)
  • Heap Fetches: 1
18. 0.512 4.249 ↓ 1.0 3,098 1

Hash (cost=377.82..377.82 rows=3,094 width=44) (actual time=4.249..4.249 rows=3,098 loops=1)

  • Output: pj_1.id, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4,096 Batches: 1 Memory Usage: 181kB
19. 0.820 3.737 ↓ 1.0 3,098 1

Hash Join (cost=104.40..377.82 rows=3,094 width=44) (actual time=1.787..3.737 rows=3,098 loops=1)

  • Output: pj_1.id, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Hash Cond: (pj_1.id = pc.projectid)
20. 1.162 1.162 ↓ 1.0 3,116 1

Seq Scan on logicinforef.project pj_1 (cost=0.00..230.84 rows=3,105 width=4) (actual time=0.005..1.162 rows=3,116 loops=1)

  • Output: pj_1.id, pj_1.timeentrycappercent, pj_1.info1, pj_1.info2, pj_1.info3, pj_1.info4, pj_1.info5, pj_1.info6, pj_1.info7, pj_1.info8, pj_1.info9, pj_1.info10, pj_1.info11, pj_1.info12, pj_1.info13, pj_1.info14, pj_1.info15, pj_1.info16, pj_1.info17, pj_1.info18, pj_1.info19, pj_1.info20, pj_1.name, pj_1.slug, pj_1.code, pj_1.clientid, pj_1.projectleaderapproverid, pj_1.isprojectleaderapprovalrequired, pj_1.startdate, pj_1.enddate, pj_1.programid, pj_1.billingtype, pj_1.fixedbidamount, pj_1.fixedbidcurrencyid, pj_1.fixedbidfrequency, pj_1.costtype, pj_1.description, pj_1.percentcomplete, pj_1.istimeentryallowed, pj_1.estimatedhours, pj_1.estimatedexpenses, pj_1.estimatedexpensescurrencyid, pj_1.estimatedcost, pj_1.estimatedcostcurrencyid, pj_1.timeandexpenseentrytype, pj_1.estimationmode, pj_1.projectstatuslabelid, pj_1.billingratefrequencyid, pj_1.billingratefrequencyduration, pj_1.defaultbillingcurrencyid, pj_1.clientbillingallocationmethod, pj_1.billingcontractid, pj_1.guidid, pj_1.totalestimatedcontractamount, pj_1.totalestimatedcontractcurrencyid, pj_1.budgetedhours, pj_1.budgetedcost, pj_1.budgetedcostcurrencyid, pj_1.projecthealthstateid, pj_1.projecthealthstatedescription
  • Filter: (pj_1.clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 2
21. 0.559 1.755 ↓ 1.0 3,100 1

Hash (cost=65.70..65.70 rows=3,096 width=44) (actual time=1.755..1.755 rows=3,100 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4,096 Batches: 1 Memory Usage: 181kB
22. 1.196 1.196 ↓ 1.0 3,100 1

Seq Scan on logicinforef.projectclient pc (cost=0.00..65.70 rows=3,096 width=44) (actual time=0.008..1.196 rows=3,100 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)
23. 0.006 0.758 ↑ 1.0 24 1

Hash (cost=277.04..277.04 rows=24 width=4) (actual time=0.758..0.758 rows=24 loops=1)

  • Output: task4.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.752 0.752 ↑ 1.0 24 1

Index Scan using uix3tk_projectparentname on logicinforef.task task4 (cost=0.29..277.04 rows=24 width=4) (actual time=0.122..0.752 rows=24 loops=1)

  • Output: task4.id
  • Index Cond: (upper((task4.name)::text) = 'BENCH'::text)
25. 1.160 1.160 ↑ 1.0 1 232

Index Scan using timesheet_pkey on logicinforef.timesheet ts_2 (cost=0.42..0.44 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=232)

  • Output: ts_2.id, ts_2.userid, ts_2.startdate, ts_2.enddate, ts_2.approvalstatus, ts_2.duedate, ts_2.autosubmitdatetimeutc, ts_2.lastautosubmitattemptdatetimeutc, ts_2.createdonutc
  • Index Cond: (ts_2.id = at.timesheetid)
26. 1.392 1.392 ↑ 1.0 1 232

Index Only Scan using dm_timesheetlist_facts_pkey on logicinforef.dm_timesheetlist_facts tslist_1 (cost=0.42..0.44 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=232)

  • Output: tslist_1.timesheetid
  • Index Cond: (tslist_1.timesheetid = at.timesheetid)
  • Heap Fetches: 1,142
27. 0.696 0.696 ↑ 1.0 1 232

Index Scan using userinfo_pkey on logicinforef.userinfo ui_1 (cost=0.28..2.30 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=232)

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

Initplan (for Sort)

29. 0.003 0.003 ↑ 1.0 1 1

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

  • Output: systeminformation.basecurrencyid
30. 0.078 4,924,615.726 ↓ 9.0 9 1

Nested Loop (cost=3.08..16.11 rows=1 width=3,121) (actual time=4,924,543.376..4,924,615.726 rows=9 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, servicecenter6.name, ui.lastname, ui.firstname, dmv_timesheetday_facts5.userduplicatename5, login.loginname, ui.id, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, pj.name, pj.id, tk.name, tk.id, pj.info20, pj.info7, tk.code, dmv_timesheetday_facts5.userid3
  • Join Filter: (dmv_timesheetday_facts5.userid3 = userlocation13.userid)
31. 0.091 4,924,615.471 ↓ 59.0 59 1

Nested Loop (cost=2.80..15.78 rows=1 width=217) (actual time=4,924,530.932..4,924,615.471 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, servicecenter6.name, login.loginname, login.userid, pj.name, pj.id, pj.info20, pj.info7, tk.name, tk.id, tk.code
32. 0.059 4,924,615.026 ↓ 59.0 59 1

Nested Loop (cost=2.38..15.32 rows=1 width=249) (actual time=4,924,530.907..4,924,615.026 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.timesheetid8, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, servicecenter6.name, login.loginname, login.userid, pj.name, pj.id, pj.info20, pj.info7, tk.name, tk.id, tk.code, tslist.timesheetid
33. 0.069 4,924,530.951 ↓ 59.0 59 1

Nested Loop Left Join (cost=1.96..12.87 rows=1 width=233) (actual time=4,924,529.274..4,924,530.951 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.timesheetid8, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, servicecenter6.name, login.loginname, login.userid, pj.name, pj.id, pj.info20, pj.info7, tk.name, tk.id, tk.code
34. 0.074 4,924,530.528 ↓ 59.0 59 1

Nested Loop Left Join (cost=1.25..4.53 rows=1 width=209) (actual time=4,924,529.233..4,924,530.528 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, servicecenter6.name, login.loginname, login.userid, pj.name, pj.id, pj.info20, pj.info7
35. 0.017 4,924,530.336 ↓ 59.0 59 1

Nested Loop Left Join (cost=0.97..2.23 rows=1 width=144) (actual time=4,924,529.224..4,924,530.336 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.projectid6, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, servicecenter6.name, login.loginname, login.userid
36. 0.050 4,924,530.201 ↓ 59.0 59 1

Nested Loop (cost=0.84..2.06 rows=1 width=144) (actual time=4,924,529.218..4,924,530.201 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.projectid6, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8, ui.lastname, ui.firstname, ui.id, userservicecenter11.userid, userservicecenter11.servicecenterid, login.loginname, login.userid
  • Join Filter: (dmv_timesheetday_facts5.userid3 = ui.id)
37. 0.075 4,924,529.974 ↓ 59.0 59 1

Nested Loop (cost=0.56..1.72 rows=1 width=118) (actual time=4,924,529.207..4,924,529.974 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.projectid6, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8, userservicecenter11.userid, userservicecenter11.servicecenterid, login.loginname, login.userid
  • Join Filter: (dmv_timesheetday_facts5.userid3 = login.userid)
38. 0.044 4,924,529.722 ↓ 59.0 59 1

Nested Loop (cost=0.28..1.34 rows=1 width=101) (actual time=4,924,529.193..4,924,529.722 rows=59 loops=1)

  • Output: dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.projectid6, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8, userservicecenter11.userid, userservicecenter11.servicecenterid
39. 4,924,529.438 4,924,529.438 ↓ 60.0 60 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..0.02 rows=1 width=81) (actual time=4,924,529.136..4,924,529.438 rows=60 loops=1)

  • Output: dmv_timesheetday_facts5.billableduration0, dmv_timesheetday_facts5.nonbillableduration1, dmv_timesheetday_facts5.timeoffduration2, dmv_timesheetday_facts5.userid3, dmv_timesheetday_facts5.timesheetenddate4, dmv_timesheetday_facts5.userduplicatename5, dmv_timesheetday_facts5.projectid6, dmv_timesheetday_facts5.taskid7, dmv_timesheetday_facts5.timesheetid8
40. 0.240 0.240 ↑ 1.0 1 60

Index Only Scan using ix4usc_userservicecenterstartend on logicinforef.userservicecenter userservicecenter11 (cost=0.28..1.31 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=60)

  • Output: userservicecenter11.userid, userservicecenter11.servicecenterid, userservicecenter11.startdate, userservicecenter11.enddate
  • Index Cond: ((userservicecenter11.userid = dmv_timesheetday_facts5.userid3) AND (userservicecenter11.startdate <= '2019-10-01'::date) AND (userservicecenter11.enddate >= '2019-10-01'::date))
  • Filter: (userservicecenter11.servicecenterid = ANY ('{15ebb972-f35e-42ee-a644-b30615c5f9ae,06533ef7-7f9a-4a34-828a-83e426de9cf9,a5a77c99-7b17-410a-8d62-ac7094b4888a,f89816a9-9a4a-4b6b-80ff-c11b1dd628bb,87e0ae2f-58bf-45e6-b202-0fd1ce106f5a,9d7b6421-eabb-42af-80de-3c2a4333bdec,33686946-e46f-44c0-adf9-9f6eebd35274}'::uuid[]))
  • Rows Removed by Filter: 0
  • Heap Fetches: 7
41. 0.177 0.177 ↑ 1.0 1 59

Index Scan using login_pkey on logicinforef.login (cost=0.28..0.37 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=59)

  • Output: login.loginname, login.userid
  • Index Cond: (login.userid = userservicecenter11.userid)
42. 0.177 0.177 ↑ 1.0 1 59

Index Scan using userinfo_pkey on logicinforef.userinfo ui (cost=0.28..0.33 rows=1 width=26) (actual time=0.002..0.003 rows=1 loops=59)

  • 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
  • Index Cond: (ui.id = login.userid)
43. 0.118 0.118 ↑ 1.0 1 59

Index Scan using servicecenter_pkey on logicinforef.servicecenter servicecenter6 (cost=0.13..0.15 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=59)

  • Output: servicecenter6.id, servicecenter6.parentid, servicecenter6.name, servicecenter6.slug, servicecenter6.code, servicecenter6.description, servicecenter6.enabled, servicecenter6.info1, servicecenter6.info2, servicecenter6.info3, servicecenter6.info4, servicecenter6.info5, servicecenter6.info6, servicecenter6.info7, servicecenter6.info8, servicecenter6.info9, servicecenter6.info10, servicecenter6.info11, servicecenter6.info12, servicecenter6.info13, servicecenter6.info14, servicecenter6.info15, servicecenter6.info16, servicecenter6.info17, servicecenter6.info18, servicecenter6.info19, servicecenter6.info20
  • Index Cond: (userservicecenter11.servicecenterid = servicecenter6.id)
44. 0.118 0.118 ↑ 1.0 1 59

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

  • Output: pj.id, pj.timeentrycappercent, pj.info1, pj.info2, pj.info3, pj.info4, pj.info5, pj.info6, pj.info7, pj.info8, pj.info9, pj.info10, pj.info11, pj.info12, pj.info13, pj.info14, pj.info15, pj.info16, pj.info17, pj.info18, pj.info19, pj.info20, pj.name, pj.slug, pj.code, pj.clientid, pj.projectleaderapproverid, pj.isprojectleaderapprovalrequired, pj.startdate, pj.enddate, pj.programid, pj.billingtype, pj.fixedbidamount, pj.fixedbidcurrencyid, pj.fixedbidfrequency, pj.costtype, pj.description, pj.percentcomplete, pj.istimeentryallowed, pj.estimatedhours, pj.estimatedexpenses, pj.estimatedexpensescurrencyid, pj.estimatedcost, pj.estimatedcostcurrencyid, pj.timeandexpenseentrytype, pj.estimationmode, pj.projectstatuslabelid, pj.billingratefrequencyid, pj.billingratefrequencyduration, pj.defaultbillingcurrencyid, pj.clientbillingallocationmethod, pj.billingcontractid, pj.guidid, pj.totalestimatedcontractamount, pj.totalestimatedcontractcurrencyid, pj.budgetedhours, pj.budgetedcost, pj.budgetedcostcurrencyid, pj.projecthealthstateid, pj.projecthealthstatedescription
  • Index Cond: (dmv_timesheetday_facts5.projectid6 = pj.id)
45. 0.177 0.354 ↑ 1.0 1 59

Nested Loop Left Join (cost=0.71..8.32 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=59)

  • Output: tk.name, tk.id, tk.code
46. 0.118 0.118 ↑ 1.0 1 59

Index Scan using task_pkey on logicinforef.task tk (cost=0.29..2.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=59)

  • 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
  • Index Cond: (dmv_timesheetday_facts5.taskid7 = tk.id)
47. 0.059 0.059 ↓ 0.0 0 59

Index Scan using uix3er_currencyideffectivedate on logicinforef.exchangerate (cost=0.42..6.01 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=59)

  • Output: exchangerate.variablecurrencyid, exchangerate.exchangevalue, exchangerate.effectivedate, exchangerate.enddate, exchangerate.id, exchangerate.fixedcurrencyid
  • Index Cond: ((exchangerate.variablecurrencyid = $4) AND (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid) AND (('now'::cstring)::date >= exchangerate.effectivedate))
  • Filter: (('now'::cstring)::date <= exchangerate.enddate)
48. 84.016 84.016 ↑ 1.0 1 59

Index Scan using ixdtslsftimesheetid on logicinforef.dm_timesheetlist_facts tslist (cost=0.42..2.44 rows=1 width=16) (actual time=1.420..1.424 rows=1 loops=59)

  • 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 = dmv_timesheetday_facts5.timesheetid8)
  • Filter: (tslist.timesheetstatus = ANY ('{0,1,2}'::integer[]))
49. 0.354 0.354 ↑ 1.0 1 59

Index Only Scan using timesheet_pkey on logicinforef.timesheet ts (cost=0.42..0.45 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=59)

  • Output: ts.id
  • Index Cond: (ts.id = tslist.timesheetid)
  • Heap Fetches: 72
50. 0.177 0.177 ↓ 0.0 0 59

Index Only Scan using ix4ul_userlocationstartend on logicinforef.userlocation userlocation13 (cost=0.28..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=59)

  • Output: userlocation13.userid, userlocation13.locationid, userlocation13.startdate, userlocation13.enddate
  • Index Cond: ((userlocation13.userid = ui.id) AND (userlocation13.locationid = '19f8aabf-5a33-4cce-8e2a-d67aa297b34f'::uuid) AND (userlocation13.startdate <= '2019-10-01'::date) AND (userlocation13.enddate >= '2019-10-01'::date))
  • Heap Fetches: 0
Planning time : 12.591 ms
Execution time : 4,924,616.571 ms