explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JHPb : akf

Settings
# exclusive inclusive rows x rows loops node
1. 0.119 31,862.985 ↓ 26.0 26 1

Sort (cost=98,711.81..98,711.82 rows=1 width=1,897) (actual time=31,862.984..31,862.985 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), dmv_timesheetday_facts4.userduplicatename6, ((login.loginname)::character varying(255)), ((cl.name)::character varying(255)), cl.id, ((pj.name)::character varying(255)), pj.id, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, ((userinfo9.displayname)::text), dmv_timesheetday_facts4.userid4
  • Sort Key: dmv_timesheetday_facts4.entrydate5, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts4.userduplicatename6, ((login.loginname)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((userinfo9.displayname)::text) COLLATE "en_US
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=132114 read=3
2.          

CTE dmv_timesheetday_facts0cte

3. 0.026 31,862.559 ↓ 26.0 26 1

GroupAggregate (cost=98,705.45..98,705.48 rows=1 width=81) (actual time=31,862.545..31,862.559 rows=26 loops=1)

  • Output: 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)), sum((CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT 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 NULL) AND akf.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 akf.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)), ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Group Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Buffers: shared hit=131812 read=3
4. 0.033 31,862.533 ↓ 26.0 26 1

Sort (cost=98,705.45..98,705.45 rows=1 width=81) (actual time=31,862.531..31,862.533 rows=26 loops=1)

  • Output: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, (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), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT 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 NULL) AND akf.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 akf.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)
  • Sort Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=131812 read=3
5. 0.011 31,862.500 ↓ 26.0 26 1

Nested Loop (cost=35.27..98,705.44 rows=1 width=81) (actual time=22,457.136..31,862.500 rows=26 loops=1)

  • Output: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, (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), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT 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 NULL) AND akf.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 akf.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)
  • Inner Unique: true
  • Buffers: shared hit=131809 read=3
6. 2.716 31,862.437 ↓ 26.0 26 1

Nested Loop (cost=35.12..98,703.26 rows=1 width=80) (actual time=22,457.114..31,862.437 rows=26 loops=1)

  • Output: ts.userid, (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), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT 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 NULL) AND akf.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 akf.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), at.entrydate, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Join Filter: (at.timesheetid = ts.id)
  • Rows Removed by Join Filter: 27898
  • Buffers: shared hit=131757 read=3
7. 0.465 0.465 ↓ 52.0 52 1

Index Scan using uix2tsuseridstartdate on akf.timesheet ts (cost=0.28..139.32 rows=1 width=20) (actual time=0.013..0.465 rows=52 loops=1)

  • Output: ts.id, ts.userid, ts.startdate, ts.enddate, ts.approvalstatus, ts.duedate, ts.autosubmitdatetimeutc, ts.lastautosubmitattemptdatetimeutc, ts.createdonutc
  • Index Cond: ((ts.startdate >= '2019-09-01'::date) AND (ts.startdate <= '2019-09-30'::date))
  • Filter: ((ts.enddate >= '2019-09-01'::date) AND (ts.enddate <= '2019-09-30'::date))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=51 read=3
8. 904.335 31,859.256 ↓ 1.3 537 52

Hash Join (cost=34.84..98,558.78 rows=413 width=108) (actual time=7.184..612.678 rows=537 loops=52)

  • Output: (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), (CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT 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 NULL) AND akf.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 akf.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), at.entrydate, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid, at.timesheetid, timesheet3.id
  • Inner Unique: true
  • Hash Cond: (at.timesheetid = timesheet3.id)
  • Buffers: shared hit=131706
9. 27,985.484 30,954.872 ↑ 1.0 166,018 52

Hash Left Join (cost=18.21..96,406.63 rows=169,133 width=616) (actual time=0.015..595.286 rows=166,018 loops=52)

  • 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, NULL::text, CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END, NULL::date, at.projectid, NULL::integer, NULL::integer, NULL::integer, NULL::uuid, NULL::interval, NULL::interval, NULL::interval, NULL::interval, CASE WHEN ((at.timeoffcodeid IS NULL) AND (at.projectid IS NOT NULL)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1'::numeric))::double precision) ELSE '00:00:00'::interval END, NULL::interval, CASE WHEN ((at.timeoffcodeid IS NULL) AND akf.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 akf.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=131688
10. 2,067.363 2,969.252 ↑ 1.0 165,941 52

Hash Left Join (cost=7.55..5,499.39 rows=169,133 width=65) (actual time=0.004..57.101 rows=165,941 loops=52)

  • Output: at.entrydate, at.breaktypeid, at.duration, at.userspecifiedclientid, at.projectid, at.timeoffcodeid, at.isbillable, at.timesheetid
  • 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=131670
11. 901.836 901.836 ↑ 1.0 165,941 52

Seq Scan on akf.dm_attendancetimeallocation_facts at (cost=0.00..4,223.33 rows=169,133 width=65) (actual time=0.003..17.343 rows=165,941 loops=52)

  • Output: at.id, at.userid, at.entrydate, at.duration, at.comments, at.projectid, at.taskid, at.activityid, at.billingrateid, at.timeoffcodeid, at.breaktypeid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, at.timeoffinfo1, at.timeoffinfo2, at.timeoffinfo3, at.timeoffinfo4, at.timeoffinfo5, at.timesheetid, at.userspecifiedclientid, at.isbillable
  • Buffers: shared hit=131664
12. 0.002 0.053 ↑ 1.0 1 1

Hash (cost=7.53..7.53 rows=1 width=16) (actual time=0.053..0.053 rows=1 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=6
13. 0.002 0.051 ↑ 1.0 1 1

Nested Loop (cost=0.14..7.53 rows=1 width=16) (actual time=0.046..0.051 rows=1 loops=1)

  • Output: pj_2.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=6
14. 0.033 0.033 ↑ 1.0 1 1

Seq Scan on akf.project pj_2 (cost=0.00..5.36 rows=1 width=4) (actual time=0.028..0.033 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: 108
  • Buffers: shared hit=4
15. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using uix4pc_projectclienteffectiveend on akf.projectclient pc_1 (cost=0.14..2.16 rows=1 width=16) (actual time=0.016..0.016 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=2
16. 0.021 0.136 ↑ 1.0 106 1

Hash (cost=9.34..9.34 rows=106 width=52) (actual time=0.136..0.136 rows=106 loops=1)

  • Output: pj_1.clientbillingallocationmethod, pj_1.id, pc.clientid, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=5
17. 0.028 0.115 ↑ 1.0 106 1

Hash Join (cost=6.71..9.34 rows=106 width=52) (actual time=0.065..0.115 rows=106 loops=1)

  • Output: pj_1.clientbillingallocationmethod, pj_1.id, pc.clientid, pc.effectivedate, pc.enddate, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Inner Unique: true
  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=5
18. 0.037 0.037 ↑ 1.0 107 1

Seq Scan on akf.projectclient pc (cost=0.00..2.34 rows=107 width=48) (actual time=0.006..0.037 rows=107 loops=1)

  • Output: pc.clientid, pc.projectid, pc.effectivedate, pc.enddate, (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)
  • Buffers: shared hit=1
19. 0.021 0.050 ↑ 1.0 108 1

Hash (cost=5.36..5.36 rows=108 width=8) (actual time=0.050..0.050 rows=108 loops=1)

  • Output: pj_1.clientbillingallocationmethod, pj_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=4
20. 0.029 0.029 ↑ 1.0 108 1

Seq Scan on akf.project pj_1 (cost=0.00..5.36 rows=108 width=8) (actual time=0.001..0.029 rows=108 loops=1)

  • Output: pj_1.clientbillingallocationmethod, pj_1.id
  • Filter: (pj_1.clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
21. 0.006 0.049 ↓ 1.0 22 1

Hash (cost=16.36..16.36 rows=21 width=16) (actual time=0.049..0.049 rows=22 loops=1)

  • Output: timesheet3.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=15
22. 0.043 0.043 ↓ 1.0 22 1

Index Scan using uix2tsuseridstartdate on akf.timesheet timesheet3 (cost=0.28..16.36 rows=21 width=16) (actual time=0.018..0.043 rows=22 loops=1)

  • Output: timesheet3.id
  • Index Cond: (timesheet3.userid = 385)
  • Buffers: shared hit=15
23. 0.052 0.052 ↑ 1.0 1 26

Index Scan using userinfo_pkey on akf.userinfo ui_1 (cost=0.15..2.17 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=26)

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

Nested Loop Left Join (cost=0.89..6.32 rows=1 width=1,897) (actual time=31,862.662..31,862.866 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, ui.lastname, ui.firstname, dmv_timesheetday_facts4.userduplicatename6, login.loginname, cl.name, cl.id, pj.name, pj.id, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, userinfo9.displayname, dmv_timesheetday_facts4.userid4
  • Inner Unique: true
  • Buffers: shared hit=132099 read=3
25. 0.007 31,862.811 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.74..6.13 rows=1 width=653) (actual time=31,862.647..31,862.811 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, ui.lastname, ui.firstname, login.loginname, cl.name, cl.id, pj.name, pj.id, userhierarchy10.supervisorid
  • Buffers: shared hit=132047 read=3
26. 0.021 31,862.752 ↓ 26.0 26 1

Nested Loop Left Join (cost=0.47..5.80 rows=1 width=653) (actual time=31,862.634..31,862.752 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, ui.lastname, ui.firstname, ui.id, login.loginname, cl.name, cl.id, pj.name, pj.id
  • Inner Unique: true
  • Buffers: shared hit=131969 read=3
27. 0.020 31,862.705 ↓ 26.0 26 1

Nested Loop (cost=0.33..3.60 rows=1 width=621) (actual time=31,862.624..31,862.705 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, dmv_timesheetday_facts4.projectid8, ui.lastname, ui.firstname, ui.id, login.loginname, cl.name, cl.id
  • Inner Unique: true
  • Join Filter: (dmv_timesheetday_facts4.userid4 = login.userid)
  • Buffers: shared hit=131917 read=3
28. 0.023 31,862.659 ↓ 26.0 26 1

Nested Loop (cost=0.18..3.40 rows=1 width=614) (actual time=31,862.614..31,862.659 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, dmv_timesheetday_facts4.projectid8, ui.lastname, ui.firstname, ui.id, cl.name, cl.id
  • Inner Unique: true
  • Buffers: shared hit=131865 read=3
29. 0.022 31,862.610 ↓ 26.0 26 1

Hash Right Join (cost=0.03..1.22 rows=1 width=597) (actual time=31,862.600..31,862.610 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, dmv_timesheetday_facts4.projectid8, cl.name, cl.id
  • Hash Cond: (cl.id = dmv_timesheetday_facts4.clientid7)
  • Buffers: shared hit=131813 read=3
30. 0.005 0.005 ↑ 1.0 13 1

Seq Scan on akf.clients cl (cost=0.00..1.13 rows=13 width=520) (actual time=0.004..0.005 rows=13 loops=1)

  • Output: cl.id, cl.name, cl.code, cl.comments, cl.disabled, cl.city, cl.stateprovince, cl.zippostalcode, cl.telephone, cl.fax, cl.website, cl.info1, cl.info2, cl.info3, cl.info4, cl.info5, cl.info6, cl.info7, cl.info8, cl.info9, cl.info10, cl.info11, cl.info12, cl.slug, cl.clientmanageruserid, cl.address, cl.country, cl.email, cl.billingcontact, cl.billingaddress, cl.billingcity, cl.billingstateprovince, cl.billingzippostalcode, cl.billingcountry, cl.billingemail, cl.billingtelephone, cl.billingfax, cl.billingwebsite, cl.defaultbillingcurrencyid, cl.guidid
  • Buffers: shared hit=1
31. 0.008 31,862.583 ↓ 26.0 26 1

Hash (cost=0.02..0.02 rows=1 width=81) (actual time=31,862.583..31,862.583 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, dmv_timesheetday_facts4.clientid7, dmv_timesheetday_facts4.projectid8
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=131812 read=3
32. 31,862.575 31,862.575 ↓ 26.0 26 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts4 (cost=0.00..0.02 rows=1 width=81) (actual time=31,862.548..31,862.575 rows=26 loops=1)

  • Output: dmv_timesheetday_facts4.entrydate5, dmv_timesheetday_facts4.userduplicatename6, dmv_timesheetday_facts4.totalduration0, dmv_timesheetday_facts4.projectduration1, dmv_timesheetday_facts4.billableduration2, dmv_timesheetday_facts4.nonbillableduration3, dmv_timesheetday_facts4.userid4, dmv_timesheetday_facts4.clientid7, dmv_timesheetday_facts4.projectid8
  • Buffers: shared hit=131812 read=3
33. 0.026 0.026 ↑ 1.0 1 26

Index Scan using userinfo_pkey on akf.userinfo ui (cost=0.15..2.17 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=26)

  • 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 = dmv_timesheetday_facts4.userid4)
  • Buffers: shared hit=52
34. 0.026 0.026 ↑ 1.0 1 26

Index Scan using login_pkey on akf.login (cost=0.15..0.19 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=26)

  • Output: login.userid, login.loginname, login.password, login.accountexpiry, login.passwordlastchanged, login.forcepasswordchange, login.disablepasswordchange, login.info1, login.info2, login.info3, login.info4, login.info5, login.passwordentropybits, login.ssoname, login.invalidloginattempts, login.lastinvalidloginattemptutc
  • Index Cond: (login.userid = ui.id)
  • Buffers: shared hit=52
35. 0.026 0.026 ↑ 1.0 1 26

Index Scan using project_pkey on akf.project pj (cost=0.14..2.16 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=26)

  • 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.projectid8 = pj.id)
  • Buffers: shared hit=52
36. 0.052 0.052 ↑ 1.0 1 26

Index Scan using ix3uh_usersuperstart on akf.userhierarchy userhierarchy10 (cost=0.27..0.32 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=26)

  • Output: userhierarchy10.id, userhierarchy10.userid, userhierarchy10.supervisorid, userhierarchy10.startdate, userhierarchy10.enddate
  • Index Cond: ((ui.id = userhierarchy10.userid) AND ('2019-10-03'::date >= userhierarchy10.startdate))
  • Filter: ('2019-10-03'::date <= userhierarchy10.enddate)
  • Buffers: shared hit=78
37. 0.026 0.026 ↑ 1.0 1 26

Index Scan using userinfo_pkey on akf.userinfo userinfo9 (cost=0.15..0.19 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=26)

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