explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eUX1

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 22,127.430 ↓ 5.0 5 1

Sort (cost=1,011,495.42..1,011,495.42 rows=1 width=3,121) (actual time=22,127.429..22,127.430 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=138971
2.          

CTE dmv_timesheetday_facts0cte

3. 0.130 22,125.537 ↓ 56.0 56 1

GroupAggregate (cost=1,011,479.09..1,011,479.13 rows=1 width=81) (actual time=22,125.400..22,125.537 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=137511
4. 0.321 22,125.407 ↓ 216.0 216 1

Sort (cost=1,011,479.09..1,011,479.10 rows=1 width=81) (actual time=22,125.378..22,125.407 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=137511
5. 1,003.754 22,125.086 ↓ 216.0 216 1

Nested Loop (cost=930.58..1,011,479.08 rows=1 width=81) (actual time=1,141.758..22,125.086 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: 11257272
  • Buffers: shared hit=137505
6. 226.506 20,602.212 ↓ 4.1 14,832 1

Hash Semi Join (cost=929.32..1,007,614.55 rows=3,654 width=72) (actual time=20.689..20,602.212 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=122520
7. 19,491.061 20,374.971 ↑ 1.0 1,695,747 1

Hash Left Join (cost=651.98..985,887.76 rows=1,695,747 width=616) (actual time=6.881..20,374.971 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=122379
8. 310.062 879.513 ↑ 1.0 1,695,747 1

Hash Left Join (cost=235.48..90,925.07 rows=1,695,747 width=65) (actual time=1.514..879.513 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=122140
9. 568.087 568.087 ↑ 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.138..568.087 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=121942
10. 0.004 1.364 ↑ 1.0 2 1

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

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=198
11. 0.005 1.360 ↑ 1.0 2 1

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

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=198
12. 1.311 1.311 ↑ 1.0 2 1

Seq Scan on logicinforef.project pj_2 (cost=0.00..230.84 rows=2 width=4) (actual time=0.397..1.311 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: 3116
  • Buffers: shared hit=192
13. 0.044 0.044 ↑ 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.022..0.022 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.598 4.397 ↓ 1.0 3,098 1

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

  • Output: pj_1.id, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4096 Batches: 1 Memory Usage: 181kB
  • Buffers: shared hit=219
15. 0.874 3.799 ↓ 1.0 3,098 1

Hash Join (cost=104.40..377.82 rows=3,094 width=44) (actual time=1.750..3.799 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.208 1.208 ↓ 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.208 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.507 1.717 ↓ 1.0 3,100 1

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

  • Output: pc.projectid, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4096 Batches: 1 Memory Usage: 181kB
  • Buffers: shared hit=27
18. 1.210 1.210 ↓ 1.0 3,100 1

Seq Scan on logicinforef.projectclient pc (cost=0.00..65.70 rows=3,096 width=44) (actual time=0.012..1.210 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.008 0.735 ↑ 1.0 24 1

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

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

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

Materialize (cost=1.27..3,754.91 rows=2 width=57) (actual time=0.000..0.035 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=14985
22. 0.209 12.341 ↓ 379.5 759 1

Nested Loop (cost=1.27..3,754.90 rows=2 width=57) (actual time=0.075..12.341 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=14985
23. 0.346 9.855 ↓ 379.5 759 1

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

  • Output: ts_1.userid, ts_1.id, tslist_1.timesheetid, ui_1.duplicatename
  • Buffers: shared hit=11948
24. 1.196 6.473 ↓ 379.5 759 1

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

  • Output: ts_1.userid, ts_1.id, ui_1.duplicatename
  • Buffers: shared hit=8291
25. 0.311 0.311 ↓ 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.311 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. 4.966 4.966 ↓ 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.002..0.002 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=8220
27. 3.036 3.036 ↑ 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.004..0.004 rows=1 loops=759)

  • Output: tslist_1.timesheetid
  • Index Cond: (tslist_1.timesheetid = ts_1.id)
  • Heap Fetches: 1214
  • Buffers: shared hit=3657
28. 2.277 2.277 ↑ 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.003..0.003 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=3037
29.          

Initplan (forSort)

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.004..0.005 rows=1 loops=1)

  • Output: systeminformation.basecurrencyid
  • Buffers: shared hit=1
31. 0.062 22,127.301 ↓ 5.0 5 1

Nested Loop (cost=3.08..15.27 rows=1 width=3,121) (actual time=22,125.913..22,127.301 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=138956
32. 0.052 22,127.129 ↓ 55.0 55 1

Nested Loop (cost=2.80..14.94 rows=1 width=217) (actual time=22,125.574..22,127.129 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=138845
33. 0.029 22,126.802 ↓ 55.0 55 1

Nested Loop (cost=2.38..14.35 rows=1 width=249) (actual time=22,125.558..22,126.802 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=138594
34. 0.026 22,126.553 ↓ 55.0 55 1

Nested Loop Left Join (cost=1.96..12.90 rows=1 width=233) (actual time=22,125.533..22,126.553 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=138428
35. 0.013 22,126.307 ↓ 55.0 55 1

Nested Loop Left Join (cost=1.25..4.53 rows=1 width=209) (actual time=22,125.493..22,126.307 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=138238
36. 0.041 22,126.184 ↓ 55.0 55 1

Nested Loop Left Join (cost=0.97..2.23 rows=1 width=144) (actual time=22,125.485..22,126.184 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=138073
37. 0.053 22,126.088 ↓ 55.0 55 1

Nested Loop (cost=0.84..2.06 rows=1 width=144) (actual time=22,125.479..22,126.088 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=137963
38. 0.053 22,125.925 ↓ 55.0 55 1

Nested Loop (cost=0.56..1.72 rows=1 width=118) (actual time=22,125.467..22,125.925 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=137798
39. 0.015 22,125.762 ↓ 55.0 55 1

Nested Loop (cost=0.28..1.34 rows=1 width=101) (actual time=22,125.452..22,125.762 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=137632
40. 22,125.579 22,125.579 ↓ 56.0 56 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..0.02 rows=1 width=81) (actual time=22,125.404..22,125.579 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=137511
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.003..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-02'::date) AND (userservicecenter11.enddate >= '2019-10-02'::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-02'::date) AND (userlocation13.enddate >= '2019-10-02'::date))
  • Heap Fetches: 0
  • Buffers: shared hit=111
Planning time : 26.506 ms
Execution time : 22,128.118 ms