explain.depesz.com

PostgreSQL's explain analyze made readable

Result: voRX

Settings
# exclusive inclusive rows x rows loops node
1. 2.513 2,147.213 ↓ 37.1 667 1

Sort (cost=200,605.18..200,605.22 rows=18 width=2,381) (actual time=2,147.158..2,147.213 rows=667 loops=1)

  • Output: ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), dmv_timesheetday_facts4.userduplicatename5, ((login.loginname)::character varying(255)), ((dep.name)::character varying(255)), dmv_timesheetday_facts4.entrydate3, ((pj.name)::character varying(255)), pj.id, ((tk.name)::character varying(255)), tk.id, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2
  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts4.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts4.entrydate3, ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts4.comments4 COLLATE "en_US
  • Sort Method: quicksort Memory: 202kB
  • Buffers: shared hit=135327
2.          

CTE dmv_timesheetday_facts0cte

3. 0.671 2,131.493 ↓ 30.3 667 1

GroupAggregate (cost=200,396.36..200,397.19 rows=22 width=129) (actual time=2,130.662..2,131.493 rows=667 loops=1)

  • Output: sum((CASE WHEN ((at.timeoffcodeid IS NULL) AND mechdynerefrehsed.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.breaktypeid IS NULL) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END)), ts_1.userid, at.entrydate, min(at.comments), ui_1.duplicatename, at.projectid, at.taskid, ts_1.id, (upper(at.comments))
  • Group Key: at.entrydate, (upper(at.comments)), ui_1.duplicatename, at.projectid, at.taskid, ts_1.id
  • Buffers: shared hit=117064
4. 0.688 2,130.822 ↓ 30.3 667 1

Sort (cost=200,396.36..200,396.42 rows=22 width=145) (actual time=2,130.652..2,130.822 rows=667 loops=1)

  • Output: at.entrydate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id, (upper(at.comments)), (CASE WHEN ((at.timeoffcodeid IS NULL) AND mechdynerefrehsed.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.breaktypeid IS NULL) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), ts_1.userid, at.comments
  • Sort Key: at.entrydate, (upper(at.comments)), ui_1.duplicatename, at.projectid, at.taskid, ts_1.id
  • Sort Method: quicksort Memory: 139kB
  • Buffers: shared hit=117064
5. 40.837 2,130.134 ↓ 30.3 667 1

Hash Join (cost=304.64..200,395.87 rows=22 width=145) (actual time=1,868.552..2,130.134 rows=667 loops=1)

  • Output: at.entrydate, ui_1.duplicatename, at.projectid, at.taskid, ts_1.id, upper(at.comments), (CASE WHEN ((at.timeoffcodeid IS NULL) AND mechdynerefrehsed.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.breaktypeid IS NULL) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END), ts_1.userid, at.comments
  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=117064
6. 1,894.678 2,089.213 ↑ 1.0 259,036 1

Hash Left Join (cost=290.77..196,817.56 rows=259,212 width=632) (actual time=5.118..2,089.213 rows=259,036 loops=1)

  • Output: NULL::uuid, NULL::integer, at.entrydate, NULL::uuid, CASE WHEN (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, at.comments, NULL::integer, NULL::date, at.projectid, at.taskid, NULL::integer, NULL::integer, NULL::uuid, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, CASE WHEN ((at.timeoffcodeid IS NULL) AND mechdynerefrehsed.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 mechdynerefrehsed.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=117005
7. 44.692 190.889 ↑ 1.0 259,036 1

Merge Left Join (cost=0.99..58,409.59 rows=259,212 width=113) (actual time=1.405..190.889 rows=259,036 loops=1)

  • Output: at.entrydate, at.breaktypeid, at.duration, at.comments, at.projectid, at.taskid, at.timeoffcodeid, at.isbillable, at.timesheetid
  • Merge Cond: (at.projectid = pj_2.id)
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate) AND (pc_1.clientid = at.userspecifiedclientid))
  • Buffers: shared hit=116898
8. 144.800 144.800 ↑ 1.0 259,036 1

Index Scan using ixata2projectid on mechdynerefrehsed.dm_attendancetimeallocation_facts at (cost=0.43..57,607.03 rows=259,212 width=117) (actual time=0.006..144.800 rows=259,036 loops=1)

  • Output: at.entrydate, at.breaktypeid, at.duration, at.comments, at.projectid, at.taskid, at.timeoffcodeid, at.isbillable, at.timesheetid, at.userspecifiedclientid
  • Index Cond: (at.projectid = ANY ('{10,1176}'::integer[]))
  • Buffers: shared hit=115035
9. 0.002 1.397 ↑ 3.0 1 1

Materialize (cost=0.56..148.78 rows=3 width=16) (actual time=1.397..1.397 rows=1 loops=1)

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=1863
10. 0.002 1.395 ↑ 3.0 1 1

Nested Loop (cost=0.56..148.78 rows=3 width=16) (actual time=1.395..1.395 rows=1 loops=1)

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=1863
11. 1.388 1.388 ↑ 3.0 1 1

Index Scan using project_pkey on mechdynerefrehsed.project pj_2 (cost=0.28..143.85 rows=3 width=4) (actual time=1.388..1.388 rows=1 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: 2278
  • Buffers: shared hit=1859
12. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using uix4pc_projectclienteffectiveend on mechdynerefrehsed.projectclient pc_1 (cost=0.28..1.63 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • 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=4
13. 0.509 3.646 ↑ 1.0 2,760 1

Hash (cost=254.06..254.06 rows=2,858 width=44) (actual time=3.646..3.646 rows=2,760 loops=1)

  • Output: pj_1.id, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
  • Buffers: shared hit=107
14. 0.722 3.137 ↑ 1.0 2,760 1

Hash Join (cost=95.53..254.06 rows=2,858 width=44) (actual time=1.575..3.137 rows=2,760 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=107
15. 0.877 0.877 ↑ 1.0 2,769 1

Seq Scan on mechdynerefrehsed.project pj_1 (cost=0.00..119.12 rows=2,887 width=4) (actual time=0.007..0.877 rows=2,769 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: 3
  • Buffers: shared hit=83
16. 0.443 1.538 ↑ 1.0 2,763 1

Hash (cost=59.76..59.76 rows=2,861 width=44) (actual time=1.538..1.538 rows=2,763 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
  • Buffers: shared hit=24
17. 1.095 1.095 ↑ 1.0 2,763 1

Seq Scan on mechdynerefrehsed.projectclient pc (cost=0.00..59.76 rows=2,861 width=44) (actual time=0.009..1.095 rows=2,763 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)
  • Buffers: shared hit=24
18. 0.016 0.084 ↓ 5.8 52 1

Hash (cost=13.76..13.76 rows=9 width=21) (actual time=0.084..0.084 rows=52 loops=1)

  • Output: ts_1.userid, ts_1.id, ui_1.duplicatename
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=59
19. 0.010 0.068 ↓ 5.8 52 1

Nested Loop (cost=0.69..13.76 rows=9 width=21) (actual time=0.015..0.068 rows=52 loops=1)

  • Output: ts_1.userid, ts_1.id, ui_1.duplicatename
  • Buffers: shared hit=59
20. 0.006 0.006 ↑ 1.0 1 1

Index Scan using userinfo_pkey on mechdynerefrehsed.userinfo ui_1 (cost=0.28..2.29 rows=1 width=5) (actual time=0.005..0.006 rows=1 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
  • Index Cond: (ui_1.id = 40)
  • Buffers: shared hit=4
21. 0.052 0.052 ↓ 5.8 52 1

Index Scan using ixtsuseridstartdateenddate on mechdynerefrehsed.timesheet ts_1 (cost=0.42..11.38 rows=9 width=20) (actual time=0.008..0.052 rows=52 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.userid = 40) AND (ts_1.startdate >= '2018-10-01'::date) AND (ts_1.startdate <= '2019-09-30'::date) AND (ts_1.enddate >= '2018-10-01'::date) AND (ts_1.enddate <= '2019-09-30'::date))
  • Buffers: shared hit=55
22.          

Initplan (forSort)

23. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on mechdynerefrehsed.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
24. 0.162 2,144.695 ↓ 37.1 667 1

Nested Loop (cost=4.13..206.60 rows=18 width=2,381) (actual time=2,130.808..2,144.695 rows=667 loops=1)

  • Output: ui.lastname, ui.firstname, dmv_timesheetday_facts4.userduplicatename5, login.loginname, dep.name, dmv_timesheetday_facts4.entrydate3, pj.name, pj.id, tk.name, tk.id, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2
  • Buffers: shared hit=135327
25. 0.265 2,142.532 ↓ 37.1 667 1

Nested Loop (cost=3.71..197.17 rows=18 width=683) (actual time=2,130.796..2,142.532 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.timesheetid8, ui.lastname, ui.firstname, login.loginname, dep.name, pj.name, pj.id, tk.name, tk.id, ts.id
  • Buffers: shared hit=131951
26. 0.661 2,140.933 ↓ 37.1 667 1

Nested Loop Left Join (cost=3.30..168.71 rows=18 width=667) (actual time=2,130.786..2,140.933 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.timesheetid8, ui.lastname, ui.firstname, login.loginname, dep.name, pj.name, pj.id, tk.name, tk.id
  • Buffers: shared hit=129430
27. 0.571 2,137.604 ↓ 37.1 667 1

Nested Loop Left Join (cost=2.71..59.32 rows=18 width=649) (actual time=2,130.754..2,137.604 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8, ui.lastname, ui.firstname, login.loginname, dep.name, pj.name, pj.id
  • Buffers: shared hit=125736
28. 0.306 2,136.366 ↓ 37.1 667 1

Hash Join (cost=2.43..29.24 rows=18 width=634) (actual time=2,130.746..2,136.366 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.projectid6, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8, ui.lastname, ui.firstname, login.loginname, dep.name
  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=123735
29. 0.112 2,136.044 ↓ 37.1 667 1

Nested Loop (cost=0.83..27.38 rows=18 width=122) (actual time=2,130.718..2,136.044 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.projectid6, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8, ui.lastname, ui.firstname, login.loginname, du.departmentid
  • Join Filter: (dmv_timesheetday_facts4.userid2 = ui.id)
  • Buffers: shared hit=123734
30. 0.664 2,134.598 ↓ 37.1 667 1

Nested Loop (cost=0.55..21.42 rows=18 width=117) (actual time=2,130.692..2,134.598 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.projectid6, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8, login.loginname, login.userid, du.departmentid, du.userid
  • Join Filter: (dmv_timesheetday_facts4.userid2 = login.userid)
  • Buffers: shared hit=121066
31. 0.013 2,133.267 ↓ 37.1 667 1

Nested Loop (cost=0.28..15.56 rows=18 width=105) (actual time=2,130.681..2,133.267 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.projectid6, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8, du.departmentid, du.userid
  • Buffers: shared hit=119065
32. 2,131.920 2,131.920 ↓ 30.3 667 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts4 (cost=0.00..0.44 rows=22 width=97) (actual time=2,130.665..2,131.920 rows=667 loops=1)

  • Output: dmv_timesheetday_facts4.billableduration0, dmv_timesheetday_facts4.totalduration1, dmv_timesheetday_facts4.userid2, dmv_timesheetday_facts4.entrydate3, dmv_timesheetday_facts4.comments4, dmv_timesheetday_facts4.userduplicatename5, dmv_timesheetday_facts4.projectid6, dmv_timesheetday_facts4.taskid7, dmv_timesheetday_facts4.timesheetid8
  • Buffers: shared hit=117064
33. 1.334 1.334 ↑ 1.0 1 667

Index Scan using ixduuserid on mechdynerefrehsed.departmentusers du (cost=0.28..0.68 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=667)

  • Output: du.departmentid, du.userid, du.isprimarydepartment
  • Index Cond: (du.userid = dmv_timesheetday_facts4.userid2)
  • Filter: (du.departmentid = ANY ('{1,2,15,16,3,4,5,6,7,8,9,10,11,12,17,13,14}'::integer[]))
  • Buffers: shared hit=2001
34. 0.667 0.667 ↑ 1.0 1 667

Index Scan using login_pkey on mechdynerefrehsed.login (cost=0.28..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=667)

  • Output: login.loginname, login.userid
  • Index Cond: (login.userid = du.userid)
  • Buffers: shared hit=2001
35. 1.334 1.334 ↑ 1.0 1 667

Index Scan using userinfo_pkey on mechdynerefrehsed.userinfo ui (cost=0.28..0.32 rows=1 width=17) (actual time=0.001..0.002 rows=1 loops=667)

  • 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=2668
36. 0.007 0.016 ↑ 1.0 27 1

Hash (cost=1.27..1.27 rows=27 width=520) (actual time=0.016..0.016 rows=27 loops=1)

  • Output: dep.name, dep.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
37. 0.009 0.009 ↑ 1.0 27 1

Seq Scan on mechdynerefrehsed.departments dep (cost=0.00..1.27 rows=27 width=520) (actual time=0.004..0.009 rows=27 loops=1)

  • Output: dep.name, dep.id
  • Buffers: shared hit=1
38. 0.667 0.667 ↑ 1.0 1 667

Index Scan using project_pkey on mechdynerefrehsed.project pj (cost=0.28..1.66 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=667)

  • 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_facts4.projectid6 = pj.id)
  • Buffers: shared hit=2001
39. 0.000 2.668 ↑ 1.0 1 667

Nested Loop Left Join (cost=0.58..6.07 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=667)

  • Output: tk.name, tk.id
  • Buffers: shared hit=3694
40. 1.334 1.334 ↑ 1.0 1 667

Index Scan using task_pkey on mechdynerefrehsed.task tk (cost=0.29..2.22 rows=1 width=26) (actual time=0.001..0.002 rows=1 loops=667)

  • 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_facts4.taskid7 = tk.id)
  • Buffers: shared hit=2001
41. 1.334 1.334 ↑ 1.0 1 667

Index Scan using uix3er_currencyideffectivedate on mechdynerefrehsed.exchangerate (cost=0.29..3.84 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=667)

  • Output: exchangerate.variablecurrencyid, exchangerate.exchangevalue, exchangerate.effectivedate, exchangerate.enddate, exchangerate.id, exchangerate.fixedcurrencyid
  • Index Cond: ((exchangerate.variablecurrencyid = $2) AND (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid) AND (('now'::cstring)::date >= exchangerate.effectivedate))
  • Filter: (('now'::cstring)::date <= exchangerate.enddate)
  • Buffers: shared hit=1692
42. 1.334 1.334 ↑ 1.0 1 667

Index Only Scan using timesheet_pkey on mechdynerefrehsed.timesheet ts (cost=0.42..1.57 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=667)

  • Output: ts.id
  • Index Cond: (ts.id = dmv_timesheetday_facts4.timesheetid8)
  • Heap Fetches: 519
  • Buffers: shared hit=2521
43. 2.001 2.001 ↑ 1.0 1 667

Index Scan using ixdtslsftimesheetid on mechdynerefrehsed.dm_timesheetlist_facts tslist (cost=0.42..0.51 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=667)

  • 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,3}'::integer[]))
  • Buffers: shared hit=3376
Planning time : 5.292 ms
Execution time : 2,147.811 ms