explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GcYX : logicinfo_after

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 13,512.011 ↓ 5.0 5 1

Sort (cost=1,011,491.17..1,011,491.18 rows=1 width=3,121) (actual time=13,512.011..13,512.011 rows=5 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: 26kB
  • Buffers: shared hit=138,305 read=685
2.          

CTE dmv_timesheetday_facts0cte

3. 0.125 13,510.188 ↓ 56.0 56 1

GroupAggregate (cost=1,011,474.85..1,011,474.88 rows=1 width=81) (actual time=13,510.044..13,510.188 rows=56 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
  • Buffers: shared hit=136,849 read=685
4. 0.352 13,510.063 ↓ 216.0 216 1

Sort (cost=1,011,474.85..1,011,474.85 rows=1 width=81) (actual time=13,510.024..13,510.063 rows=216 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: 55kB
  • Buffers: shared hit=136,849 read=685
5. 1,009.766 13,509.711 ↓ 216.0 216 1

Nested Loop (cost=930.58..1,011,474.84 rows=1 width=81) (actual time=1,646.821..13,509.711 rows=216 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
  • Join Filter: (at.timesheetid = ts_1.id)
  • Rows Removed by Join Filter: 11,257,272
  • Buffers: shared hit=136,843 read=685
6. 220.208 10,942.585 ↓ 4.1 14,832 1

Hash Semi Join (cost=929.32..1,007,610.55 rows=3,646 width=72) (actual time=13.952..10,942.585 rows=14,832 loops=1)

  • 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)
  • Buffers: shared hit=122,543
7. 9,912.981 10,721.626 ↑ 1.0 1,695,747 1

Hash Left Join (cost=651.98..985,883.84 rows=1,695,747 width=616) (actual time=5.726..10,721.626 rows=1,695,747 loops=1)

  • 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))
  • Buffers: shared hit=122,402
8. 297.389 804.373 ↑ 1.0 1,695,747 1

Hash Left Join (cost=235.48..90,925.07 rows=1,695,747 width=65) (actual time=1.433..804.373 rows=1,695,747 loops=1)

  • 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))
  • Buffers: shared hit=122,140
9. 505.680 505.680 ↑ 1.0 1,695,747 1

Seq Scan on logicinforef.dm_attendancetimeallocation_facts at (cost=0.00..77,971.47 rows=1,695,747 width=69) (actual time=0.119..505.680 rows=1,695,747 loops=1)

  • Output: at.projectid, at.taskid, at.timeoffcodeid, at.duration, at.isbillable, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid
  • Buffers: shared hit=121,942
10. 0.004 1.304 ↑ 1.0 2 1

Hash (cost=235.45..235.45 rows=2 width=16) (actual time=1.304..1.304 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
  • Buffers: shared hit=198
11. 0.003 1.300 ↑ 1.0 2 1

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

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=198
12. 1.275 1.275 ↑ 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.275 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
  • Buffers: shared hit=192
13. 0.022 0.022 ↑ 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.010..0.011 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
  • Buffers: shared hit=6
14. 0.535 4.272 ↓ 1.0 3,098 1

Hash (cost=377.82..377.82 rows=3,094 width=44) (actual time=4.272..4.272 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
  • Buffers: shared hit=219
15. 0.871 3.737 ↓ 1.0 3,098 1

Hash Join (cost=104.40..377.82 rows=3,094 width=44) (actual time=1.714..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)
  • Buffers: shared hit=219
16. 1.181 1.181 ↓ 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.006..1.181 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
  • Buffers: shared hit=192
17. 0.543 1.685 ↓ 1.0 3,100 1

Hash (cost=65.70..65.70 rows=3,096 width=44) (actual time=1.685..1.685 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
  • Buffers: shared hit=27
18. 1.142 1.142 ↓ 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.142 rows=3,100 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)
  • Buffers: shared hit=27
19. 0.011 0.751 ↑ 1.0 24 1

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

  • Output: task4.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=141
20. 0.740 0.740 ↑ 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.127..0.740 rows=24 loops=1)

  • Output: task4.id
  • Index Cond: (upper((task4.name)::text) = 'BENCH'::text)
  • Buffers: shared hit=141
21. 509.188 1,557.360 ↓ 379.5 759 14,832

Materialize (cost=1.27..3,754.91 rows=2 width=57) (actual time=0.000..0.105 rows=759 loops=14,832)

  • Output: ts_1.userid, ts_1.id, ts_2.enddate, ts_2.id, tslist_1.timesheetid, ui_1.duplicatename
  • Buffers: shared hit=14,300 read=685
22. 0.438 1,048.172 ↓ 379.5 759 1

Nested Loop (cost=1.27..3,754.90 rows=2 width=57) (actual time=2.134..1,048.172 rows=759 loops=1)

  • Output: ts_1.userid, ts_1.id, ts_2.enddate, ts_2.id, tslist_1.timesheetid, ui_1.duplicatename
  • Join Filter: (ts_1.id = ts_2.id)
  • Buffers: shared hit=14,300 read=685
23. 0.283 1,044.698 ↓ 379.5 759 1

Nested Loop (cost=0.84..3,753.97 rows=2 width=37) (actual time=2.123..1,044.698 rows=759 loops=1)

  • Output: ts_1.userid, ts_1.id, tslist_1.timesheetid, ui_1.duplicatename
  • Buffers: shared hit=11,263 read=685
24. 1.532 938.155 ↓ 379.5 759 1

Nested Loop (cost=0.42..3,750.07 rows=2 width=21) (actual time=2.096..938.155 rows=759 loops=1)

  • Output: ts_1.userid, ts_1.id, ui_1.duplicatename
  • Buffers: shared hit=7,678 read=613
25. 0.532 0.532 ↓ 1.0 2,483 1

Seq Scan on logicinforef.userinfo ui_1 (cost=0.00..95.81 rows=2,481 width=5) (actual time=0.007..0.532 rows=2,483 loops=1)

  • 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
  • Buffers: shared hit=71
26. 936.091 936.091 ↓ 0.0 0 2,483

Index Scan using uix2tsuseridstartdate on logicinforef.timesheet ts_1 (cost=0.42..1.46 rows=1 width=20) (actual time=0.377..0.377 rows=0 loops=2,483)

  • 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.userid = ui_1.id) AND (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))
  • Buffers: shared hit=7,607 read=613
27. 106.260 106.260 ↑ 1.0 1 759

Index Only Scan using ixdtslsftimesheetid on logicinforef.dm_timesheetlist_facts tslist_1 (cost=0.42..1.94 rows=1 width=16) (actual time=0.139..0.140 rows=1 loops=759)

  • Output: tslist_1.timesheetid
  • Index Cond: (tslist_1.timesheetid = ts_1.id)
  • Heap Fetches: 1,214
  • Buffers: shared hit=3,585 read=72
28. 3.036 3.036 ↑ 1.0 1 759

Index Scan using timesheet_pkey on logicinforef.timesheet ts_2 (cost=0.42..0.45 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=759)

  • 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 = tslist_1.timesheetid)
  • Buffers: shared hit=3,037
29.          

Initplan (for Sort)

30. 0.005 0.005 ↑ 1.0 1 1

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

  • Output: systeminformation.basecurrencyid
  • Buffers: shared hit=1
31. 0.058 13,511.949 ↓ 5.0 5 1

Nested Loop (cost=3.08..15.27 rows=1 width=3,121) (actual time=13,510.536..13,511.949 rows=5 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)
  • Buffers: shared hit=138,294 read=685
32. 0.047 13,511.781 ↓ 55.0 55 1

Nested Loop (cost=2.80..14.94 rows=1 width=217) (actual time=13,510.216..13,511.781 rows=55 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
  • Buffers: shared hit=138,183 read=685
33. 0.012 13,511.459 ↓ 55.0 55 1

Nested Loop (cost=2.38..14.35 rows=1 width=249) (actual time=13,510.200..13,511.459 rows=55 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, ts.id
  • Buffers: shared hit=137,932 read=685
34. 0.030 13,511.227 ↓ 55.0 55 1

Nested Loop Left Join (cost=1.96..12.90 rows=1 width=233) (actual time=13,510.176..13,511.227 rows=55 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
  • Buffers: shared hit=137,766 read=685
35. 0.032 13,510.977 ↓ 55.0 55 1

Nested Loop Left Join (cost=1.25..4.53 rows=1 width=209) (actual time=13,510.135..13,510.977 rows=55 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
  • Buffers: shared hit=137,576 read=685
36. 0.035 13,510.835 ↓ 55.0 55 1

Nested Loop Left Join (cost=0.97..2.23 rows=1 width=144) (actual time=13,510.127..13,510.835 rows=55 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
  • Buffers: shared hit=137,411 read=685
37. 0.050 13,510.745 ↓ 55.0 55 1

Nested Loop (cost=0.84..2.06 rows=1 width=144) (actual time=13,510.121..13,510.745 rows=55 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)
  • Buffers: shared hit=137,301 read=685
38. 0.061 13,510.585 ↓ 55.0 55 1

Nested Loop (cost=0.56..1.72 rows=1 width=118) (actual time=13,510.110..13,510.585 rows=55 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)
  • Buffers: shared hit=137,136 read=685
39. 0.032 13,510.414 ↓ 55.0 55 1

Nested Loop (cost=0.28..1.34 rows=1 width=101) (actual time=13,510.096..13,510.414 rows=55 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
  • Buffers: shared hit=136,970 read=685
40. 13,510.214 13,510.214 ↓ 56.0 56 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..0.02 rows=1 width=81) (actual time=13,510.047..13,510.214 rows=56 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
  • Buffers: shared hit=136,849 read=685
41. 0.168 0.168 ↑ 1.0 1 56

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

  • 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: 8
  • Buffers: shared hit=121
42. 0.110 0.110 ↑ 1.0 1 55

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

  • Output: login.loginname, login.userid
  • Index Cond: (login.userid = userservicecenter11.userid)
  • Buffers: shared hit=166
43. 0.110 0.110 ↑ 1.0 1 55

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

  • 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)
  • Buffers: shared hit=165
44. 0.055 0.055 ↑ 1.0 1 55

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

  • 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)
  • Buffers: shared hit=110
45. 0.110 0.110 ↑ 1.0 1 55

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=55)

  • 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)
  • Buffers: shared hit=165
46. 0.055 0.220 ↑ 1.0 1 55

Nested Loop Left Join (cost=0.71..8.35 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=55)

  • Output: tk.name, tk.id, tk.code
  • Buffers: shared hit=190
47. 0.110 0.110 ↑ 1.0 1 55

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=55)

  • 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)
  • Buffers: shared hit=165
48. 0.055 0.055 ↓ 0.0 0 55

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

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

Index Only Scan using timesheet_pkey on logicinforef.timesheet ts (cost=0.42..1.44 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=55)

  • Output: ts.id
  • Index Cond: (ts.id = dmv_timesheetday_facts5.timesheetid8)
  • Heap Fetches: 0
  • Buffers: shared hit=166
50. 0.275 0.275 ↑ 1.0 1 55

Index Scan using dm_timesheetlist_facts_pkey on logicinforef.dm_timesheetlist_facts tslist (cost=0.42..0.58 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=55)

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

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

  • 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
  • Buffers: shared hit=111
Planning time : 34.592 ms
Execution time : 13,512.627 ms