explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NNPF

Settings
# exclusive inclusive rows x rows loops node
1. 17.294 35,238.297 ↑ 1.8 5,873 1

Sort (cost=3,211,964.80..3,211,991.20 rows=10,559 width=2,551) (actual time=35,237.729..35,238.297 rows=5,873 loops=1)

  • Output: ((cl.name)::character varying(255)), ((pj.name)::character varying(255)), ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), timeexpensecostbilling2_facts6.userduplicatename7, ((login.loginname)::character varying(255)), ((ui.externalid)::character varying(255)), ui.id, timeexpensecostbilling2_facts6.billableduration0, ((currencyinfo8.symbol)::character varying(50)), timeexpensecostbilling2_facts6.hourlyrate3, timeexpensecostbilling2_facts6.entrydatesheetdate4, timeexpensecostbilling2_facts6.currencyid5, timeexpensecostbilling2_facts6.billingamount1, timeexpensecostbilling2_facts6.billableexpenseinbasecurrency2, timeexpensecostbilling2_facts6.userid6
  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", timeexpensecostbilling2_facts6.userduplicatename7, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((currencyinfo8.symbol)::character varying(50)) COLLATE "en_US", timeexpensecostbilling2_facts6.hourlyrate3
  • Sort Method: quicksort Memory: 1752kB
  • Buffers: shared hit=6336160 read=4060, temp read=34866 written=34864
2.          

CTE eexpensecostbilling2_facts0cte

3. 7.122 35,207.122 ↑ 1.8 5,873 1

GroupAggregate (cost=3,209,661.35..3,210,083.71 rows=10,559 width=133) (actual time=35,198.471..35,207.122 rows=5,873 loops=1)

  • Output: sum(timeexpensecostbilling2_facts0.billableduration), sum(timeexpensecostbilling2_facts0.billingamount), sum(timeexpensecostbilling2_facts0.billableexpenseinbasecurrency), timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid
  • Group Key: timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid
  • Buffers: shared hit=6335801 read=4060, temp read=34866 written=34864
4. 34.187 35,200.000 ↓ 1.3 13,399 1

Sort (cost=3,209,661.35..3,209,687.75 rows=10,559 width=133) (actual time=35,198.455..35,200.000 rows=13,399 loops=1)

  • Output: timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.billableduration, timeexpensecostbilling2_facts0.billingamount, timeexpensecostbilling2_facts0.billableexpenseinbasecurrency
  • Sort Key: timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid
  • Sort Method: quicksort Memory: 1825kB
  • Buffers: shared hit=6335801 read=4060, temp read=34866 written=34864
5. 7.895 35,165.813 ↓ 1.3 13,399 1

Hash Left Join (cost=22,663.08..3,208,955.68 rows=10,559 width=133) (actual time=1,119.554..35,165.813 rows=13,399 loops=1)

  • Output: timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.billableduration, timeexpensecostbilling2_facts0.billingamount, timeexpensecostbilling2_facts0.billableexpenseinbasecurrency
  • Hash Cond: (timeexpensecostbilling2_facts0.userid = ui_1.id)
  • Buffers: shared hit=6335798 read=4060, temp read=34866 written=34864
6. 11.684 35,156.632 ↓ 1.3 13,399 1

Hash Join (cost=22,536.26..3,208,683.67 rows=10,559 width=132) (actual time=1,118.235..35,156.632 rows=13,399 loops=1)

  • Output: timeexpensecostbilling2_facts0.billableduration, timeexpensecostbilling2_facts0.billingamount, timeexpensecostbilling2_facts0.billableexpenseinbasecurrency, timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.userid, timeexpensecostbilling2_facts0.projectid
  • Hash Cond: (timeexpensecostbilling2_facts0.projectid = pj_1.id)
  • Buffers: shared hit=6335727 read=4060, temp read=34866 written=34864
7. 523.895 35,124.355 ↑ 2.8 39,178 1

Subquery Scan on timeexpensecostbilling2_facts0 (cost=14,852.69..3,200,478.87 rows=110,838 width=132) (actual time=1,097.613..35,124.355 rows=39,178 loops=1)

  • Output: timeexpensecostbilling2_facts0.userid, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid, timeexpensecostbilling2_facts0.entrydatesheetdate, timeexpensecostbilling2_facts0.entrydateincurreddate, timeexpensecostbilling2_facts0.timesheetstartdate, timeexpensecostbilling2_facts0.timesheetenddate, timeexpensecostbilling2_facts0.timesheetid, timeexpensecostbilling2_facts0.timeallocationid, timeexpensecostbilling2_facts0.comments, timeexpensecostbilling2_facts0.activityid, timeexpensecostbilling2_facts0.duration, timeexpensecostbilling2_facts0.billableduration, timeexpensecostbilling2_facts0.nonbillableduration, timeexpensecostbilling2_facts0.capexduration, timeexpensecostbilling2_facts0.opexduration, timeexpensecostbilling2_facts0.notsubmittedduration, timeexpensecostbilling2_facts0.notapprovedduration, timeexpensecostbilling2_facts0.expenseid, timeexpensecostbilling2_facts0.expenseentryid, timeexpensecostbilling2_facts0.expenseentrydescription, timeexpensecostbilling2_facts0.expensetypeid, timeexpensecostbilling2_facts0.expenseinbasecurrency, timeexpensecostbilling2_facts0.billableexpenseinbasecurrency, timeexpensecostbilling2_facts0.nonbillableexpenseinbasecurrency, timeexpensecostbilling2_facts0.allocationid, timeexpensecostbilling2_facts0.estimatedduration, timeexpensecostbilling2_facts0.billingitemid, timeexpensecostbilling2_facts0.billingrateid, timeexpensecostbilling2_facts0.billingratename, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.hourlyrate, timeexpensecostbilling2_facts0.effectivedate, timeexpensecostbilling2_facts0.billingamount, timeexpensecostbilling2_facts0.expensebillingamount, timeexpensecostbilling2_facts0.notinvoicedbillingamount, timeexpensecostbilling2_facts0.notinvoicedexpensebillingamount, timeexpensecostbilling2_facts0.notinvoicedduration, timeexpensecostbilling2_facts0.notsubmittedbillingamount, timeexpensecostbilling2_facts0.notapprovedbillingamount, timeexpensecostbilling2_facts0.timeentryinfo1, timeexpensecostbilling2_facts0.timeentryinfo2, timeexpensecostbilling2_facts0.timeentryinfo3, timeexpensecostbilling2_facts0.timeentryinfo4, timeexpensecostbilling2_facts0.timeentryinfo5, timeexpensecostbilling2_facts0.expenseentryinfo1, timeexpensecostbilling2_facts0.expenseentryinfo2, timeexpensecostbilling2_facts0.expenseentryinfo3, timeexpensecostbilling2_facts0.expenseentryinfo4, timeexpensecostbilling2_facts0.expenseentryinfo5, timeexpensecostbilling2_facts0.trackingnumber
  • Filter: (((timeexpensecostbilling2_facts0.entrydatesheetdate >= '2019-09-01'::date) AND (timeexpensecostbilling2_facts0.entrydatesheetdate <= '2019-09-30'::date)) OR (timeexpensecostbilling2_facts0.entrydatesheetdate IS NULL))
  • Rows Removed by Filter: 3390457
  • Buffers: shared hit=6335535 read=4060, temp read=34866 written=34864
8. 375.797 34,600.460 ↑ 3.2 3,429,635 1

Append (cost=14,852.69..3,033,805.21 rows=11,111,577 width=1,052) (actual time=173.360..34,600.460 rows=3,429,635 loops=1)

  • Buffers: shared hit=6335535 read=4060, temp read=34866 written=34864
9. 470.340 8,952.582 ↓ 1.0 1,564,038 1

Result (cost=14,852.69..1,488,146.22 rows=1,560,563 width=1,052) (actual time=173.359..8,952.582 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.entrydate, ts.startdate, ts.enddate, ts.id, at.id, at.comments, at.activityid, at.duration, (CASE WHEN (at.billingrateid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END), (CASE WHEN (at.billingrateid IS NULL) THEN at.duration ELSE '00:00:00'::interval END), (CASE WHEN (COALESCE(tk.costtype, pj_2.costtype) = 0) THEN at.duration ELSE '00:00:00'::interval END), (CASE WHEN (COALESCE(tk.costtype, pj_2.costtype) = 1) THEN at.duration ELSE '00:00:00'::interval END), (CASE WHEN (ts.approvalstatus = ANY ('{0,3}'::integer[])) THEN at.duration ELSE '00:00:00'::interval END), (CASE WHEN (ts.approvalstatus = 1) THEN at.duration ELSE '00:00:00'::interval END), (NULL::integer), (NULL::integer), (NULL::text), (NULL::integer), (NULL::numeric), (NULL::numeric), (NULL::numeric), (CASE WHEN (ptma.id IS NOT NULL) THEN ptma.id ELSE NULL::uuid END), (CASE WHEN (ptma.duration IS NOT NULL) THEN ptma.duration ELSE NULL::interval END), (NULL::uuid), br.id, br.name, pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, (NULL::numeric), (NULL::numeric), (NULL::numeric), (NULL::numeric), (NULL::interval), (NULL::numeric), (NULL::numeric), at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text), (NULL::text)
  • Buffers: shared hit=747417 read=1
10. 175.326 8,482.242 ↓ 1.0 1,564,038 1

Append (cost=14,852.69..1,464,737.78 rows=1,560,563 width=1,044) (actual time=173.352..8,482.242 rows=1,564,038 loops=1)

  • Buffers: shared hit=747417 read=1
11. 1,559.405 8,306.915 ↓ 1.0 1,564,038 1

Hash Left Join (cost=14,852.69..1,449,132.16 rows=1,560,562 width=3,463) (actual time=173.352..8,306.915 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.entrydate, ts.startdate, ts.enddate, ts.id, at.id, at.comments, at.activityid, at.duration, CASE WHEN (at.billingrateid IS NOT NULL) THEN at.duration ELSE '00:00:00'::interval END, CASE WHEN (at.billingrateid IS NULL) THEN at.duration ELSE '00:00:00'::interval END, CASE WHEN (COALESCE(tk.costtype, pj_2.costtype) = 0) THEN at.duration ELSE '00:00:00'::interval END, CASE WHEN (COALESCE(tk.costtype, pj_2.costtype) = 1) THEN at.duration ELSE '00:00:00'::interval END, CASE WHEN (ts.approvalstatus = ANY ('{0,3}'::integer[])) THEN at.duration ELSE '00:00:00'::interval END, CASE WHEN (ts.approvalstatus = 1) THEN at.duration ELSE '00:00:00'::interval END, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, CASE WHEN (ptma.id IS NOT NULL) THEN ptma.id ELSE NULL::uuid END, CASE WHEN (ptma.duration IS NOT NULL) THEN ptma.duration ELSE NULL::interval END, NULL::uuid, br.id, br.name, pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
  • Hash Cond: ((at.projectid = ptm.projectid) AND (at.userid = ptm.userid) AND (at.entrydate = ptma.date))
  • Buffers: shared hit=747417 read=1
12.          

Initplan (forHash Left Join)

13. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on logicinforef.projectsysteminformation projectsysteminformation_2 (cost=0.00..1.01 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: projectsysteminformation_2.usercustombillingrateid
  • Buffers: shared hit=1
14. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on logicinforef.projectsysteminformation projectsysteminformation_3 (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: projectsysteminformation_3.usercustombillingrateid
  • Buffers: shared hit=1
15. 468.189 6,708.515 ↓ 1.0 1,564,038 1

Hash Left Join (cost=11,484.72..294,849.49 rows=1,560,562 width=2,803) (actual time=134.058..6,708.515 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, ts.startdate, ts.enddate, ts.id, ts.approvalstatus, pj_2.costtype, pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, br.id, br.name, tk.costtype
  • Hash Cond: (at.taskid = tk.id)
  • Buffers: shared hit=746135 read=1
16. 452.830 6,232.400 ↓ 1.0 1,564,038 1

Hash Left Join (cost=10,737.18..276,389.51 rows=1,560,562 width=2,799) (actual time=125.993..6,232.400 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, ts.startdate, ts.enddate, ts.id, ts.approvalstatus, pj_2.costtype, pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, br.id, br.name
  • Hash Cond: (at.billingrateid = br.id)
  • Buffers: shared hit=745524 read=1
17. 573.771 5,779.538 ↓ 1.0 1,564,038 1

Hash Left Join (cost=10,734.40..261,304.35 rows=1,560,562 width=2,762) (actual time=125.950..5,779.538 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, ts.startdate, ts.enddate, ts.id, ts.approvalstatus, pj_2.costtype, pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate
  • Hash Cond: (pbr.id = pbrh.projectbillingrateid)
  • Join Filter: ((at.entrydate >= pbrh.effectivedate) AND (at.entrydate <= pbrh.enddate))
  • Rows Removed by Join Filter: 253607
  • Buffers: shared hit=745523 read=1
18. 431.635 5,198.219 ↓ 1.0 1,564,038 1

Hash Left Join (cost=10,051.18..214,778.43 rows=1,560,562 width=2,766) (actual time=118.257..5,198.219 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, ts.startdate, ts.enddate, ts.id, ts.approvalstatus, pj_2.costtype, pbr.id
  • Hash Cond: (at.projectid = pj_2.id)
  • Buffers: shared hit=745310 read=1
19. 610.604 4,764.914 ↓ 1.0 1,564,038 1

Hash Join (cost=9,789.27..194,248.66 rows=1,560,562 width=2,762) (actual time=116.555..4,764.914 rows=1,564,038 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, ts.startdate, ts.enddate, ts.id, ts.approvalstatus, pbr.id
  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=745118 read=1
20. 2,203.221 4,055.496 ↓ 1.0 1,564,040 1

Merge Left Join (cost=1,043.79..164,033.04 rows=1,563,872 width=2,750) (actual time=15.656..4,055.496 rows=1,564,040 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, at.timesheetid, pbr.id
  • Merge Cond: ((at.billingrateid = pbr.billingrateid) AND (at.projectid = pbr.projectid))
  • Join Filter: (((pbr.billingrateid = $3) AND (at.userid = pbr.userid)) OR ((pbr.billingrateid <> $4) AND (pbr.userid IS NULL)))
  • Rows Removed by Join Filter: 7927612
  • Buffers: shared hit=739776 read=1
21. 1,090.096 1,090.096 ↓ 1.0 1,564,040 1

Index Scan using ixpta4billingrateid on logicinforef.dm_projecttimeallocation_facts at (cost=0.55..115,022.72 rows=1,563,872 width=2,734) (actual time=0.010..1,090.096 rows=1,564,040 loops=1)

  • Output: at.userid, at.projectid, at.taskid, at.entrydate, at.id, at.comments, at.activityid, at.duration, at.billingrateid, at.timeentryinfo1, at.timeentryinfo2, at.timeentryinfo3, at.timeentryinfo4, at.timeentryinfo5, at.timesheetid
  • Filter: ((at.timeoffcodeid IS NULL) AND (at.duration IS NOT NULL))
  • Rows Removed by Filter: 128804
  • Buffers: shared hit=739617 read=1
22. 758.744 762.179 ↓ 938.7 8,936,455 1

Sort (cost=1,041.54..1,065.34 rows=9,520 width=40) (actual time=15.632..762.179 rows=8,936,455 loops=1)

  • Output: pbr.projectid, pbr.billingrateid, pbr.userid, pbr.id
  • Sort Key: pbr.billingrateid, pbr.projectid
  • Sort Method: quicksort Memory: 2120kB
  • Buffers: shared hit=159
23. 3.435 3.435 ↓ 1.8 17,303 1

Seq Scan on logicinforef.projectbillingrate pbr (cost=0.00..412.43 rows=9,520 width=40) (actual time=0.020..3.435 rows=17,303 loops=1)

  • Output: pbr.projectid, pbr.billingrateid, pbr.userid, pbr.id
  • Filter: ((pbr.billingrateid = $3) OR ((pbr.billingrateid <> $4) AND (pbr.userid IS NULL)))
  • Buffers: shared hit=155
24. 59.763 98.814 ↑ 1.0 265,177 1

Hash (cost=5,430.77..5,430.77 rows=265,177 width=28) (actual time=98.814..98.814 rows=265,177 loops=1)

  • Output: ts.startdate, ts.enddate, ts.id, ts.approvalstatus
  • Buckets: 524288 Batches: 1 Memory Usage: 19634kB
  • Buffers: shared hit=5339
25. 39.051 39.051 ↑ 1.0 265,177 1

Seq Scan on logicinforef.timesheet ts (cost=0.00..5,430.77 rows=265,177 width=28) (actual time=0.113..39.051 rows=265,177 loops=1)

  • Output: ts.startdate, ts.enddate, ts.id, ts.approvalstatus
  • Buffers: shared hit=5339
26. 0.481 1.670 ↓ 1.0 3,118 1

Hash (cost=223.07..223.07 rows=3,107 width=8) (actual time=1.670..1.670 rows=3,118 loops=1)

  • Output: pj_2.costtype, pj_2.id
  • Buckets: 4096 Batches: 1 Memory Usage: 142kB
  • Buffers: shared hit=192
27. 1.189 1.189 ↓ 1.0 3,118 1

Seq Scan on logicinforef.project pj_2 (cost=0.00..223.07 rows=3,107 width=8) (actual time=0.005..1.189 rows=3,118 loops=1)

  • Output: pj_2.costtype, pj_2.id
  • Buffers: shared hit=192
28. 4.490 7.548 ↓ 1.0 21,033 1

Hash (cost=421.99..421.99 rows=20,899 width=32) (actual time=7.548..7.548 rows=21,033 loops=1)

  • Output: pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, pbrh.projectbillingrateid, pbrh.enddate
  • Buckets: 32768 Batches: 1 Memory Usage: 1627kB
  • Buffers: shared hit=213
29. 3.058 3.058 ↓ 1.0 21,033 1

Seq Scan on logicinforef.projectbillingratehistory pbrh (cost=0.00..421.99 rows=20,899 width=32) (actual time=0.005..3.058 rows=21,033 loops=1)

  • Output: pbrh.currencyid, pbrh.hourlyrate, pbrh.effectivedate, pbrh.projectbillingrateid, pbrh.enddate
  • Buffers: shared hit=213
30. 0.023 0.032 ↑ 1.0 79 1

Hash (cost=1.79..1.79 rows=79 width=37) (actual time=0.032..0.032 rows=79 loops=1)

  • Output: br.id, br.name
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=1
31. 0.009 0.009 ↑ 1.0 79 1

Seq Scan on logicinforef.billingrate br (cost=0.00..1.79 rows=79 width=37) (actual time=0.005..0.009 rows=79 loops=1)

  • Output: br.id, br.name
  • Buffers: shared hit=1
32. 2.789 7.926 ↓ 1.0 17,554 1

Hash (cost=529.46..529.46 rows=17,446 width=8) (actual time=7.926..7.926 rows=17,554 loops=1)

  • Output: tk.costtype, tk.id
  • Buckets: 32768 Batches: 1 Memory Usage: 874kB
  • Buffers: shared hit=611
33. 5.137 5.137 ↓ 1.0 17,554 1

Seq Scan on logicinforef.task tk (cost=0.00..529.46 rows=17,446 width=8) (actual time=0.117..5.137 rows=17,554 loops=1)

  • Output: tk.costtype, tk.id
  • Buffers: shared hit=611
34. 13.165 38.990 ↓ 1.1 54,010 1

Hash (cost=2,469.78..2,469.78 rows=51,210 width=44) (actual time=38.990..38.990 rows=54,010 loops=1)

  • Output: ptma.id, ptma.duration, ptma.date, ptm.userid, ptm.projectid
  • Buckets: 65536 Batches: 1 Memory Usage: 4521kB
  • Buffers: shared hit=1279
35. 14.337 25.825 ↓ 1.1 54,071 1

Hash Join (cost=647.54..2,469.78 rows=51,210 width=44) (actual time=6.931..25.825 rows=54,071 loops=1)

  • Output: ptma.id, ptma.duration, ptma.date, ptm.userid, ptm.projectid
  • Hash Cond: (ptma.projectteammemberid = ptm.id)
  • Buffers: shared hit=1279
36. 4.808 4.808 ↓ 1.1 54,071 1

Seq Scan on logicinforef.projectteammemberallocation ptma (cost=0.00..1,118.10 rows=51,210 width=52) (actual time=0.108..4.808 rows=54,071 loops=1)

  • Output: ptma.id, ptma.projectteammemberid, ptma.date, ptma.duration
  • Buffers: shared hit=1118
37. 3.900 6.680 ↑ 1.0 21,522 1

Hash (cost=377.24..377.24 rows=21,624 width=24) (actual time=6.680..6.680 rows=21,522 loops=1)

  • Output: ptm.id, ptm.userid, ptm.projectid
  • Buckets: 32768 Batches: 1 Memory Usage: 1433kB
  • Buffers: shared hit=161
38. 2.780 2.780 ↑ 1.0 21,522 1

Seq Scan on logicinforef.projectteammember ptm (cost=0.00..377.24 rows=21,624 width=24) (actual time=0.005..2.780 rows=21,522 loops=1)

  • Output: ptm.id, ptm.userid, ptm.projectid
  • Buffers: shared hit=161
39. 0.001 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.00 rows=1 width=1,044) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: "*SELECT* 2".userid, "*SELECT* 2".projectid, NULL::integer, "*SELECT* 2".entrydatesheetdate, "*SELECT* 2".entrydateincurreddate, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, "*SELECT* 2".allocationid, "*SELECT* 2".estimatedduration, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
40. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,044) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: userid, projectid, NULL::integer, date, date, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, '00:00:00'::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, id, duration, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
  • One-Time Filter: false
41. 277.295 1,871.182 ↑ 17.4 519,495 1

Subquery Scan on *SELECT* 3 (cost=192,859.95..579,169.06 rows=9,020,400 width=1,052) (actual time=629.738..1,871.182 rows=519,495 loops=1)

  • Output: "*SELECT* 3".userid, "*SELECT* 3".projectid, "*SELECT* 3".taskid, "*SELECT* 3".entrydatesheetdate, "*SELECT* 3".entrydateincurreddate, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, "*SELECT* 3".expenseid, "*SELECT* 3".expenseentryid, "*SELECT* 3".expenseentrydescription, "*SELECT* 3".expensetypeid, "*SELECT* 3".expenseinbasecurrency, "*SELECT* 3".billableexpenseinbasecurrency, "*SELECT* 3".nonbillableexpenseinbasecurrency, NULL::uuid, NULL::interval, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, "*SELECT* 3".expenseentryinfo1, "*SELECT* 3".expenseentryinfo2, "*SELECT* 3".expenseentryinfo3, "*SELECT* 3".expenseentryinfo4, "*SELECT* 3".expenseentryinfo5, "*SELECT* 3".trackingnumber
  • Buffers: shared hit=413549
42. 919.391 1,593.887 ↑ 17.4 519,495 1

Hash Join (cost=192,859.95..488,965.06 rows=9,020,400 width=1,931) (actual time=629.734..1,593.887 rows=519,495 loops=1)

  • Output: ex.userid, ee.projectid, ee.taskid, ex.expensedate, ee.entrydate, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, ex.id, ee.id, ee.description, ee.expensetypeid, ((ee.amount * ee.exchangevalue) * exchangerate.exchangevalue), CASE WHEN ee.billtoclient THEN ((ee.amount * ee.exchangevalue) * exchangerate.exchangevalue) ELSE '0'::numeric END, CASE WHEN (NOT ee.billtoclient) THEN ((ee.amount * ee.exchangevalue) * exchangerate.exchangevalue) ELSE '0'::numeric END, NULL::uuid, NULL::interval, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, ee.info1, ee.info2, ee.info3, ee.info4, ee.info5, ex.trackingnumber
  • Hash Cond: (ee.expenseid = ex.id)
  • Join Filter: (((ee.amount * ee.exchangevalue) * exchangerate.exchangevalue) IS NOT NULL)
  • Buffers: shared hit=413549
43.          

Initplan (forHash Join)

44. 0.007 0.007 ↑ 1.0 1 1

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

  • Output: systeminformation.basecurrencyid
  • Buffers: shared hit=1
45. 53.108 53.108 ↓ 1.0 519,495 1

Seq Scan on logicinforef.expenseentry ee (cost=0.00..12,480.65 rows=519,265 width=1,136) (actual time=0.108..53.108 rows=519,495 loops=1)

  • Output: ee.id, ee.expenseid, ee.expensetypeid, ee.description, ee.entrydate, ee.netamount, ee.amount, ee.currencyid, ee.expenserate, ee.projectid, ee.taskid, ee.paymentmethodid, ee.requestreimbursement, ee.billtoclient, ee.numberofunits, ee.exchangevalue, ee.paidonutc, ee.invoicedonutc, ee.taxcodeid1, ee.taxamount1, ee.taxcodeid2, ee.taxamount2, ee.taxcodeid3, ee.taxamount3, ee.taxcodeid4, ee.taxamount4, ee.taxcodeid5, ee.taxamount5, ee.info1, ee.info2, ee.info3, ee.info4, ee.info5
  • Buffers: shared hit=14456
46. 28.174 621.381 ↑ 17.1 84,099 1

Hash (cost=174,928.56..174,928.56 rows=1,434,430 width=30) (actual time=621.381..621.381 rows=84,099 loops=1)

  • Output: ex.userid, ex.expensedate, ex.id, ex.trackingnumber, exchangerate.exchangevalue
  • Buckets: 2097152 Batches: 1 Memory Usage: 21734kB
  • Buffers: shared hit=399093
47. 0.000 593.207 ↑ 17.1 84,099 1

Nested Loop (cost=0.42..174,928.56 rows=1,434,430 width=30) (actual time=0.144..593.207 rows=84,099 loops=1)

  • Output: ex.userid, ex.expensedate, ex.id, ex.trackingnumber, exchangerate.exchangevalue
  • Buffers: shared hit=399093
48. 8.820 8.820 ↓ 1.0 84,099 1

Seq Scan on logicinforef.expense ex (cost=0.00..2,078.61 rows=82,161 width=27) (actual time=0.114..8.820 rows=84,099 loops=1)

  • Output: ex.id, ex.description, ex.userid, ex.supervisorid, ex.departmentid, ex.approvalstatus, ex.expensedate, ex.submittedonutc, ex.savedonutc, ex.reimbursementcurrencyid, ex.paid, ex.generatedkey, ex.trackingnumber, ex.disclaimeraccepted
  • Buffers: shared hit=2281
49. 588.693 588.693 ↑ 18.0 1 84,099

Index Scan using uix3er_currencyideffectivedate on logicinforef.exchangerate (cost=0.42..1.92 rows=18 width=19) (actual time=0.006..0.007 rows=1 loops=84,099)

  • Output: exchangerate.variablecurrencyid, exchangerate.exchangevalue, exchangerate.effectivedate, exchangerate.enddate, exchangerate.id, exchangerate.fixedcurrencyid
  • Index Cond: ((exchangerate.variablecurrencyid = $2) AND (exchangerate.fixedcurrencyid = ex.reimbursementcurrencyid) AND (ex.expensedate >= exchangerate.effectivedate))
  • Filter: (ex.expensedate <= exchangerate.enddate)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=396811
50. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..0.01 rows=1 width=1,052) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: NULL::integer, "*SELECT* 4".projectid, "*SELECT* 4".taskid, NULL::date, NULL::date, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::uuid, NULL::interval, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
51. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,044) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: NULL::integer, projectid, id, NULL::date, NULL::date, NULL::date, NULL::date, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::uuid, NULL::interval, NULL::uuid, NULL::uuid, NULL::text, NULL::integer, NULL::numeric, NULL::date, NULL::numeric, NULL::numeric, NULL::numeric, NULL::numeric, NULL::interval, NULL::numeric, NULL::numeric, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
  • One-Time Filter: false
52. 751.349 23,400.898 ↓ 2.5 1,346,102 1

Subquery Scan on *SELECT* 5 (cost=462,179.69..966,489.93 rows=530,613 width=941) (actual time=9,786.718..23,400.898 rows=1,346,102 loops=1)

  • Output: "*SELECT* 5".userid, "*SELECT* 5".projectid, "*SELECT* 5".taskid, "*SELECT* 5".entrydatesheetdate, "*SELECT* 5".entrydateincurreddate, "*SELECT* 5".timesheetstartdate, "*SELECT* 5".timesheetenddate, "*SELECT* 5".timesheetid, NULL::uuid, NULL::text, "*SELECT* 5".activityid, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::uuid, NULL::interval, "*SELECT* 5".billingitemid, "*SELECT* 5".billingrateid, "*SELECT* 5".billingratename, "*SELECT* 5".currencyid, "*SELECT* 5".hourlyrate, "*SELECT* 5".effectivedate, "*SELECT* 5".billingamount, "*SELECT* 5".expensebillingamount, "*SELECT* 5".notinvoicedbillingamount, "*SELECT* 5".notinvoicedexpensebillingamount, "*SELECT* 5".notinvoicedduration, "*SELECT* 5".notsubmittedbillingamount, "*SELECT* 5".notapprovedbillingamount, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
  • Buffers: shared hit=5174569 read=4059, temp read=34866 written=34864
53. 2,260.096 22,649.549 ↓ 2.5 1,346,102 1

Nested Loop Left Join (cost=462,179.69..958,530.73 rows=530,613 width=933) (actual time=9,786.708..22,649.549 rows=1,346,102 loops=1)

  • Output: bidm.userid, bidm.projectid, bidm.taskid, bi.entrydate, bi.entrydate, t.startdate, t.enddate, t.id, NULL::uuid, NULL::text, bidm.activityid, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::interval, NULL::integer, NULL::integer, NULL::text, NULL::integer, NULL::numeric, NULL::numeric, NULL::numeric, NULL::uuid, NULL::interval, bi.id, br_1.id, br_1.name, bi.currencyid, bidm.rate, pbrh_1.effectivedate, CASE WHEN (bidm.billingtypeuri = 'urn:replicon:billing-item-type:timesheet'::text) THEN bi.amount ELSE 0.0000::numeric(19,4) END, CASE WHEN (bidm.billingtypeuri = 'urn:replicon:billing-item-type:expense'::text) THEN bi.amount ELSE 0.0000::numeric(19,4) END, CASE WHEN ((iibi.id IS NULL) AND (bidm.billingtypeuri = 'urn:replicon:billing-item-type:timesheet'::text)) THEN bi.amount ELSE 0.0000::numeric(19,4) END, CASE WHEN ((iibi.id IS NULL) AND (bidm.billingtypeuri = 'urn:replicon:billing-item-type:expense'::text)) THEN bi.amount ELSE 0.0000::numeric(19,4) END, CASE WHEN ((iibi.id IS NULL) AND (bidm.billingtypeuri = 'urn:replicon:billing-item-type:timesheet'::text)) THEN bi2m.timespan ELSE '00:00:00'::interval END, CASE WHEN (t.approvalstatus = ANY ('{0,3}'::integer[])) THEN bi.amount ELSE 0.0000::numeric(19,4) END, CASE WHEN (t.approvalstatus = 1) THEN bi.amount ELSE 0.0000::numeric(19,4) END, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
  • Buffers: shared hit=5174569 read=4059, temp read=34866 written=34864
54.          

Initplan (forNested Loop Left Join)

55. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on logicinforef.projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1)

  • Output: projectsysteminformation.usercustombillingrateid
  • Buffers: shared hit=1
56. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on logicinforef.projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: projectsysteminformation_1.usercustombillingrateid
  • Buffers: shared hit=1
57. 287.651 10,966.734 ↓ 2.5 1,346,102 1

Hash Left Join (cost=462,177.24..496,112.53 rows=530,613 width=173) (actual time=9,786.680..10,966.734 rows=1,346,102 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, br_1.id, br_1.name, pbrh_1.effectivedate, t.startdate, t.enddate, t.id, t.approvalstatus, iibi.id
  • Hash Cond: (bi.id = iibi.billingitemid)
  • Buffers: shared hit=134215, temp read=34866 written=34864
58. 908.295 10,679.081 ↓ 2.5 1,346,102 1

Hash Right Join (cost=462,143.17..494,086.10 rows=530,613 width=157) (actual time=9,786.661..10,679.081 rows=1,346,102 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, br_1.id, br_1.name, pbrh_1.effectivedate, t.startdate, t.enddate, t.id, t.approvalstatus
  • Hash Cond: (aggm.billingitemid = bi.id)
  • Filter: (aggm.uri IS NULL)
  • Rows Removed by Filter: 17256
  • Buffers: shared hit=134215, temp read=34866 written=34864
59. 14.616 14.616 ↓ 1.1 17,747 1

Index Scan using ixbiminvoicinghint on logicinforef.billingitem2metadata aggm (cost=0.41..895.77 rows=15,957 width=67) (actual time=0.009..14.616 rows=17,747 loops=1)

  • Output: aggm.billingitemid, aggm.uri
  • Buffers: shared hit=11585
60. 734.743 9,756.170 ↓ 1.0 1,363,358 1

Hash (cost=414,644.24..414,644.24 rows=1,358,601 width=157) (actual time=9,756.170..9,756.170 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, br_1.id, br_1.name, pbrh_1.effectivedate, t.startdate, t.enddate, t.id, t.approvalstatus
  • Buckets: 2097152 Batches: 2 Memory Usage: 135647kB
  • Buffers: shared hit=122630, temp read=20810 written=34729
61. 497.692 9,021.427 ↓ 1.0 1,363,358 1

Hash Left Join (cost=293,495.78..414,644.24 rows=1,358,601 width=157) (actual time=3,650.008..9,021.427 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, br_1.id, br_1.name, pbrh_1.effectivedate, t.startdate, t.enddate, t.id, t.approvalstatus
  • Hash Cond: (bidm.timesheetid = t.id)
  • Buffers: shared hit=122630, temp read=20810 written=20810
62. 466.518 8,422.685 ↓ 1.0 1,363,358 1

Hash Left Join (cost=284,750.30..390,821.28 rows=1,358,601 width=145) (actual time=3,546.891..8,422.685 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.timesheetid, br_1.id, br_1.name, pbrh_1.effectivedate
  • Hash Cond: (pbr_1.id = pbrh_1.projectbillingrateid)
  • Join Filter: ((bi.entrydate >= pbrh_1.effectivedate) AND (bi.entrydate <= pbrh_1.enddate))
  • Rows Removed by Join Filter: 254098
  • Buffers: shared hit=117291, temp read=20810 written=20810
63. 329.645 7,949.702 ↓ 1.0 1,363,358 1

Hash Left Join (cost=284,067.07..350,228.12 rows=1,358,601 width=157) (actual time=3,540.373..7,949.702 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.timesheetid, br_1.id, br_1.name, pbr_1.id
  • Hash Cond: (bidm.billingrateid = br_1.id)
  • Buffers: shared hit=117078, temp read=20810 written=20810
64. 254.526 7,620.025 ↓ 1.0 1,363,358 1

Merge Join (cost=284,064.30..338,976.57 rows=1,358,601 width=136) (actual time=3,540.319..7,620.025 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.billingrateid, bidm.timesheetid, pbr_1.id
  • Merge Cond: (pj_3.id = bidm.projectid)
  • Buffers: shared hit=117077, temp read=20810 written=20810
65. 2.970 2.970 ↓ 1.0 3,115 1

Index Only Scan using project_pkey on logicinforef.project pj_3 (cost=0.28..151.88 rows=3,107 width=4) (actual time=0.045..2.970 rows=3,115 loops=1)

  • Output: pj_3.id
  • Heap Fetches: 1632
  • Buffers: shared hit=1510
66. 362.205 7,362.529 ↓ 1.0 1,363,358 1

Materialize (cost=284,064.02..325,009.30 rows=1,358,601 width=136) (actual time=3,540.269..7,362.529 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.billingrateid, bidm.timesheetid, pbr_1.id
  • Buffers: shared hit=115567, temp read=20810 written=20810
67. 2,348.444 7,000.324 ↓ 1.0 1,363,358 1

Merge Left Join (cost=284,064.02..321,612.80 rows=1,358,601 width=136) (actual time=3,540.258..7,000.324 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.billingrateid, bidm.timesheetid, pbr_1.id
  • Merge Cond: ((bidm.projectid = pbr_1.projectid) AND (bidm.billingrateid = pbr_1.billingrateid))
  • Join Filter: (((pbr_1.billingrateid = $0) AND (bidm.userid = pbr_1.userid)) OR ((pbr_1.billingrateid <> $1) AND (pbr_1.userid IS NULL)))
  • Rows Removed by Join Filter: 7950653
  • Buffers: shared hit=115567, temp read=20810 written=20810
68. 1,825.289 3,868.487 ↓ 1.0 1,363,358 1

Sort (cost=283,022.47..286,418.98 rows=1,358,601 width=120) (actual time=3,530.700..3,868.487 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.billingrateid, bidm.timesheetid
  • Sort Key: bidm.projectid, bidm.billingrateid
  • Sort Method: external merge Disk: 166464kB
  • Buffers: shared hit=115412, temp read=20810 written=20810
69. 1,016.357 2,043.198 ↓ 1.0 1,363,358 1

Hash Join (cost=55,782.93..144,623.89 rows=1,358,601 width=120) (actual time=697.992..2,043.198 rows=1,363,358 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.rate, bidm.billingtypeuri, bidm.billingrateid, bidm.timesheetid
  • Hash Cond: (bidm.billingitemid = bi.id)
  • Join Filter: (CASE WHEN (bidm.billingtypeuri = 'urn:replicon:billing-item-type:timesheet'::text) THEN bi.amount ELSE 0.0000::numeric(19,4) END IS NOT NULL)
  • Buffers: shared hit=115412
70. 337.172 337.172 ↓ 1.0 2,218,722 1

Seq Scan on logicinforef.billingitem2denormalizedmetadata bidm (cost=0.00..63,457.28 rows=2,218,300 width=108) (actual time=0.113..337.172 rows=2,218,722 loops=1)

  • Output: bidm.id, bidm.billingitemid, bidm.userid, bidm.projectid, bidm.taskid, bidm.activityid, bidm.billingrateid, bidm.timesheetid, bidm.billingtypeuri, bidm.rate, bidm.expensetypeid, bidm.clientid
  • Filter: (bidm.userid IS NOT NULL)
  • Rows Removed by Filter: 41
  • Buffers: shared hit=82490
71. 402.887 689.669 ↑ 1.0 1,363,399 1

Hash (cost=38,717.65..38,717.65 rows=1,365,222 width=28) (actual time=689.669..689.669 rows=1,363,399 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount
  • Buckets: 2097152 Batches: 1 Memory Usage: 96996kB
  • Buffers: shared hit=32922
72. 286.782 286.782 ↑ 1.0 1,363,399 1

Seq Scan on logicinforef.billingitem2 bi (cost=0.00..38,717.65 rows=1,365,222 width=28) (actual time=0.109..286.782 rows=1,363,399 loops=1)

  • Output: bi.entrydate, bi.id, bi.currencyid, bi.amount
  • Filter: bi.isbillable
  • Rows Removed by Filter: 855364
  • Buffers: shared hit=32922
73. 779.839 783.393 ↓ 941.5 8,963,187 1

Sort (cost=1,041.54..1,065.34 rows=9,520 width=40) (actual time=9.539..783.393 rows=8,963,187 loops=1)

  • Output: pbr_1.projectid, pbr_1.billingrateid, pbr_1.userid, pbr_1.id
  • Sort Key: pbr_1.projectid, pbr_1.billingrateid
  • Sort Method: quicksort Memory: 2120kB
  • Buffers: shared hit=155
74. 3.554 3.554 ↓ 1.8 17,303 1

Seq Scan on logicinforef.projectbillingrate pbr_1 (cost=0.00..412.43 rows=9,520 width=40) (actual time=0.030..3.554 rows=17,303 loops=1)

  • Output: pbr_1.projectid, pbr_1.billingrateid, pbr_1.userid, pbr_1.id
  • Filter: ((pbr_1.billingrateid = $0) OR ((pbr_1.billingrateid <> $1) AND (pbr_1.userid IS NULL)))
  • Buffers: shared hit=155
75. 0.012 0.032 ↑ 1.0 79 1

Hash (cost=1.79..1.79 rows=79 width=37) (actual time=0.032..0.032 rows=79 loops=1)

  • Output: br_1.id, br_1.name
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=1
76. 0.020 0.020 ↑ 1.0 79 1

Seq Scan on logicinforef.billingrate br_1 (cost=0.00..1.79 rows=79 width=37) (actual time=0.008..0.020 rows=79 loops=1)

  • Output: br_1.id, br_1.name
  • Buffers: shared hit=1
77. 3.390 6.465 ↓ 1.0 21,033 1

Hash (cost=421.99..421.99 rows=20,899 width=24) (actual time=6.465..6.465 rows=21,033 loops=1)

  • Output: pbrh_1.effectivedate, pbrh_1.projectbillingrateid, pbrh_1.enddate
  • Buckets: 32768 Batches: 1 Memory Usage: 1407kB
  • Buffers: shared hit=213
78. 3.075 3.075 ↓ 1.0 21,033 1

Seq Scan on logicinforef.projectbillingratehistory pbrh_1 (cost=0.00..421.99 rows=20,899 width=24) (actual time=0.004..3.075 rows=21,033 loops=1)

  • Output: pbrh_1.effectivedate, pbrh_1.projectbillingrateid, pbrh_1.enddate
  • Buffers: shared hit=213
79. 59.314 101.050 ↑ 1.0 265,177 1

Hash (cost=5,430.77..5,430.77 rows=265,177 width=28) (actual time=101.050..101.050 rows=265,177 loops=1)

  • Output: t.startdate, t.enddate, t.id, t.approvalstatus
  • Buckets: 524288 Batches: 1 Memory Usage: 19634kB
  • Buffers: shared hit=5339
80. 41.736 41.736 ↑ 1.0 265,177 1

Seq Scan on logicinforef.timesheet t (cost=0.00..5,430.77 rows=265,177 width=28) (actual time=0.111..41.736 rows=265,177 loops=1)

  • Output: t.startdate, t.enddate, t.id, t.approvalstatus
  • Buffers: shared hit=5339
81. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=20.70..20.70 rows=1,070 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: iibi.id, iibi.billingitemid
  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
82. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on logicinforef.invoiceitembillingitems iibi (cost=0.00..20.70 rows=1,070 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: iibi.id, iibi.billingitemid
83. 9,422.714 9,422.714 ↑ 1.0 1 1,346,102

Index Scan using ixbimbillingitemidkey on logicinforef.billingitem2metadata bi2m (cost=0.43..0.84 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=1,346,102)

  • Output: bi2m.id, bi2m.billingitemid, bi2m.parentid, bi2m.index, bi2m.key, bi2m.uri, bi2m.slug, bi2m."boolean", bi2m.date, bi2m.number, bi2m.text, bi2m."time", bi2m.timespan, bi2m.daterange_startdate, bi2m.daterange_enddate, bi2m.daterange_relativedaterangeuri, bi2m.daterange_relativedaterangeasofdate, bi2m.workdayduration_decimalworkdays, bi2m.workdayduration_workdays, bi2m.workdayduration_hours, bi2m.workdayduration_minutes
  • Index Cond: (bi2m.billingitemid = bi.id)
  • Buffers: shared hit=5040354 read=4059
84. 0.156 20.593 ↓ 2.2 652 1

Hash (cost=7,679.87..7,679.87 rows=296 width=4) (actual time=20.593..20.593 rows=652 loops=1)

  • Output: pj_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
  • Buffers: shared hit=192
85. 20.437 20.437 ↓ 2.2 652 1

Seq Scan on logicinforef.project pj_1 (cost=0.00..7,679.87 rows=296 width=4) (actual time=0.175..20.437 rows=652 loops=1)

  • Output: pj_1.id
  • Filter: ((pj_1.id = ANY ('{350,3820,6757,6955,7252,1377,3076,1880,2313,2784,1776,5965,6922,223,2585,3011,2581,6856,7120,2968,1986,2513,2520,203,518,210,2188,212,6857,3028,2916,2543,5506,2090,7285,7483,2490,7484,220,2579,2307,719,7384,2080,1378,2315,2522,511,2314,1063,2506,7517,309,310,311,312,2788,2569,7219,316,317,3787,7516,334,7220,346,347,2056,348,391,7385,363,369,2806,2816,5668,392,375,376,2809,2776,1404,377,378,380,381,2046,389,390,2729,2290,1854,2316,7417,2849,2053,410,2703,3226,1133,2771,2762,1864,1499,3025,1745,2781,2834,2823,435,434,439,468,474,2280,2782,1866,438,2886,2832,479,110,2310,485,2867,4546,6396,510,2755,5701,2919,1795,3953,5998,2795,2789,2147,1868,104,1844,1208,2214,2815,2072,3075,2928,2925,3260,1643,2859,2874,2547,2571,2549,2605,2517,1958,2870,2312,2570,2260,2448,2294,1985,2545,2641,2606,517,5800,2575,2665,2406,2664,2535,2692,2688,2012,2054,2713,2708,2998,1990,93,227,4,940,6625,1982,2071,1722,509,2913,2295,2300,2722,1959,1619,2539,1384,2341,1434,1988,2105,2015,1572,2792,5206,447,6592,5076,6328,4348,1197,349,2952,2305,6559,4711,308,1835,2008,368,2303,2934,278,383,2286,2161,2680,2266,101,5272,2224,405,323,2927,452,302,2883,2283,2498,2268,2586,540,705,1707,430,2287,1557,1979,1871,576,2265,2679,2267,475,5239,4777,526,117,2427,1992,5504,2426,5734,2269,2682,4153,354,532,2160,2397,2931,1850,2747,1544,2271,1570,733,2912,2837,2263,2770,2917,6295,867,4018,6658,911,1809,6659,5173,2272,1393,1808,2978,1948,1961,978,2760,1089,2499,2489,5505,2078,3789,1105,2418,5075,2765,2655,6493,1770,2484,1173,3006,4183,1952,1887,2079,1187,2142,6494,1714,4216,2026,569,1376,1326,1962,1553,1867,1424,2308,1508,1038,2183,2421,1453,2414,1878,1504,2156,440,2754,1885,1576,1039,1290,1797,1214,1419,2,1927,1843,1595,1609,1970,1085,1796,1799,1407,1305,441,1849,2100,1605,1253,1934,580,1410,2592,2524,1046,2262,1760,1391,531,2047,988,1172,1086,753,1883,1011,1758,3073,1209,957,669,1382,581,2276,2001,6460,546,548,550,559,566,1840,1545,2032,2239,1471,1449,1953,1999,1125,1040,956,726,815,1002,1775,986,1774,1273,1569,1316,1636,1418,1561,2302,1167,834,1257,1495,1010,1334,3070,1163,1014,4051,997,982,912,2667,2656,3043,1012,4579,3952,917,2943,2853,2675,2229,1642,1232,2293,1614,1314,1233,425,687,920,4084,2981,1318,5175,999,574,3457,2014,1716,2009,1678,2021,2020,1585,1332,1356,2092,2029,2036,2016,2023,2024,2018,2025,2022,1793,2037,2480,1823,2051,1815,1695,2153,2040,1881,1684,1746,2258,1360,1933,2048,2000,1836,1710,2087,444,2767,1991,2199,2494,2301,2270,1433,1142,1870,190,2243,1852,2455,2119,2137,1097,1107,2151,2117,2109,1150,2149,2158,2127,1757,1951,159,1489,2278,1718,2256,2432,1996,1373,2070,2107,2067,1058,1879,2327,2061,2235,2236,2247,2121,1579,2306,2035,2246,567,2491,781,2254,2250,959,1728,2408,1977,2342,779,2759,1372,850,1578,2390,2391,471,2696,2436,2145,2261,2415,2392,2419,2393,493,2318,2449,1976,2138,2416,2052,2433,2141,2159,2386,1081,943,2521,2328,657,140,1717,2340,2112,2096,1571,2157,2405,2450,2483,2454,2425,2458,2532,2273,2264,2259,2740,2476,2304,2420,2479,849,2389,2228,1359,2143,2574,2456,2443,1577,820,843,469,2451,2556,2394,1408,1091,1949,2598,1517,2439,1659,470,2529,711,495,1380,2101,2576,832,817,700,2681,2077,2446,2537,1148,1963,496,2223,1474,2710,2726,835,2721,2724,221,486,1563,2288,1685,1551,977,491,2435,1845,214,213,507,244,1016,449,492,1648,1588,432,582,2434,1950,1766,433,1175,2088,1733,2518,231,217,426,364,340,5503,370,483,1567,216,1903,508,484,429,2311,388,3292,2233,2139,2591,235,1518,2914,791,2115,1566,1369,193,205,343,335,246,414,182,855,1773,1671,4447,454,472,1189,1764,1023,215,1767,1238,258,2123,1831,1102,2843,1769,1865,3005,1729,2890,1762,2082,2010,2116,329,1698,1674,2873,1701,1749,2003,1250,1134,232,1602,2819,1207,320,2017,1732,1677,1960,1699,2150,1513,1696,910,1615,2257,2482,1396,2227,1826,1686,1713,2548,1613,747,903,859,797,703,2019,553,698,945,1343,955,620,901,710,1047,1198,825,1293,799,831,951,1306,998,754,715,1328,939,1062,759,1013,685,1155,652,772,704,622,942,1291,1180,1171,1255,557,640,521,671,636,1114,556,714,769,852,756,1103,731,807,944,1143,1118,840,643,952,1082,874,1300,621,446,634,1219,851,568,716,794,428,743,846,1003,1368,987,1381,2163,2553,2918,2748,2946,3688,3027,4678,2646,2926,2999,3040,3021,3045,2879,3047,5536,2986,2897,2974,2512,2825,3024,3009,2900,2728,2850,2603,3034,3722,2704,2838,2198,2733,2940,1846,2848,2495,3042,2930,4975,3919,2580,2799,5074,3490,2929,2875,2811,4249,749,5404,5174,2824,1035,3023,2676,5569}'::integer[])) AND (pj_1.id = ANY ('{2761,1435,2359,3820,1820,6757,6955,7252,2588,1377,2162,1734,2373,2041,3076,1880,2313,2042,3055,2784,1530,2457,1776,1853,5965,6922,2585,1675,3011,2581,2983,2354,6856,7120,1573,2238,2968,1986,2513,2520,4282,1529,2188,2841,6857,3028,2916,1522,1527,2543,5506,668,2090,7285,7483,1559,2490,2869,7484,2579,2842,2307,7384,2080,1378,2315,1521,2522,2314,2993,2994,2995,2506,7517,2800,2788,2569,7219,3787,7516,2921,2801,7220,2056,7385,2806,2816,5668,1488,2809,2776,1404,1533,2046,2729,1405,2290,1854,1726,2316,2829,7417,2849,2961,1995,2649,2053,2732,501,2703,2845,3226,1133,2771,2762,1864,1499,3025,1745,2781,2834,2823,563,2911,2345,2280,2782,1866,2375,2886,2832,4184,1048,2349,2310,2867,4546,6396,2755,2985,5669,2376,5701,2919,1795,3953,5998,2795,2789,2147,2371,1868,1844,2971,1208,2214,2815,2072,2346,3075,2928,2925,2364,3260,1643,2365,2859,2874,2547,2571,2549,2605,2517,1958,2870,2312,2570,2260,2448,2294,1985,2545,2641,2606,5800,2575,2665,2406,2664,2535,2692,2688,1837,2012,2054,2713,2708,2998,1990,2357,6625,1982,2071,1722,2362,2381,2913,2295,2360,2358,2363,2300,2722,1556,1248,1959,1619,2539,1473,1384,2341,2374,2369,983,1434,871,1988,2984,186,2355,2105,2015,1572,2792,5206,6592,2353,1778,5076,2094,6328,4348,2352,1197,1519,2960,2952,2305,6559,2351,4711,1274,2350,1835,2008,2303,2934,2982,2286,2161,2680,555,2266,2348,1400,5272,2224,2347,1511,3067,2927,2883,2283,3954,1115,2944,2498,2268,549,2586,540,1707,2287,1557,1979,1709,1871,2265,2679,1249,2997,2651,2267,5239,4777,2399,1814,2055,325,1930,2430,2427,2668,2379,2380,1992,2382,5504,2426,5734,2269,2682,4153,2370,854,2160,2397,2931,3721,1850,1270,2747,1544,128,506,2271,1570,1635,1170,709,2912,5373,2837,2263,2770,2917,6295,1975,4018,6658,1809,6659,5173,2272,1393,1856,1808,2978,1948,1961,1633,1317,2760,1089,520,2499,1552,2489,5505,2078,3789,2418,5075,2765,2655,6493,1528,1770,2484,538,3006,4183,1952,1887,2079,1187,1565,530,1204,1751,2118,1811,1307,2142,1285,2249,6494,1714,3626,4216,2383,1331,616,1338,2120,1302,2026,1376,1326,1962,1553,1867,1424,1411,1394,916,2486,2308,900,1508,1038,2183,2421,1453,2414,1878,476,1504,1525,2156,1531,2754,1885,1510,1886,1576,1241,1496,1797,1214,1419,1644,2,1597,1818,1927,1843,1598,1595,1640,1655,1609,1970,795,487,1796,905,1799,1407,1747,1752,1849,1724,2100,1605,1723,1715,1708,2790,1934,1410,2592,2524,1046,2262,1760,2505,2996,1391,2047,893,1172,1406,2384,1883,1011,1758,729,1021,3073,1817,1209,132,1382,2276,2001,1452,6460,1587,1840,1545,2032,2239,1240,353,1487,125,1275,2738,1471,1449,1953,1999,2516,20,654,1125,1437,1211,1333,1017,1584,1490,1555,1549,1002,1775,986,1774,1273,1765,1569,1636,288,1418,1561,1580,2302,1427,1465,1495,252,3070,4051,2667,2656,1467,3043,4579,3952,2943,2853,2675,1941,2229,1658,1642,694,2293,1614,1314,4084,2981,5175,3457,2014,946,1716,2009,1678,2021,2020,1460,1585,2578,1455,2092,2029,2036,2016,2023,2024,2018,2025,2022,2339,1793,2037,2480,1823,2051,1815,1695,2153,2040,1881,1684,1746,1583,2258,1360,730,1933,2048,2000,1836,1710,2087,2767,1991,2199,2494,2301,2270,1433,1142,1870,190,2243,1852,2455,2119,2137,1971,2057,2151,1550,2117,2109,1150,2149,2158,2127,1757,1951,159,1489,2278,1718,2289,2256,2432,1996,2070,2107,2067,1058,1879,1973,2327,2061,2235,2236,2247,2121,1579,2306,2035,2246,2491,1694,2254,2250,1728,2408,2368,1977,488,326,1009,776,1210,1574,2342,2759,2377,1578,419,1672,2390,2391,2531,2696,2709,2436,2145,1702,2261,2415,2392,2419,2393,2496,2318,2449,1976,2138,1070,2251,2416,2052,2433,1439,1771,2141,2159,2386,1942,2557,2521,2326,2328,140,1816,1088,1717,2340,1303,2112,627,2096,1571,2157,1987,66,2405,2450,2483,2454,2425,2458,2532,1838,2273,2264,2478,2259,1200,2740,2476,2551,2304,2420,2479,2536,2389,1277,2228,1803,1359,2143,2574,2456,2443,1577,2451,2556,2394,1408,1091,1949,2598,2081,1517,2361,2439,1659,1532,445,875,1507,2529,500,2372,2378,1380,1322,80,2101,2576,2681,2441,2077,2043,1178,2446,2537,1963,1700,2223,1474,2366,2367,2698,2710,2726,2721,2724,1563,2288,1685,1551,2435,1845,1330,1271,1648,1506,1741,1588,1804,2434,1928,1950,1766,1175,2088,1733,1687,2518,3523,5503,1567,1903,2311,1721,3358,3292,2233,2139,1436,2387,2591,3458,1518,2914,791,2115,1676,990,1566,2356,2111,1534,1369,3627,1158,1773,1671,1399,1679,3623,4447,2398,1189,1764,1162,1972,1023,1767,1238,1558,2123,1831,1784,96,1535,1102,2843,1769,1865,914,915,321,3005,1729,2890,1697,1762,2082,2010,2116,322,329,1345,141,1750,1429,1698,1674,2873,505,1701,63,1749,847,2003,1250,1265,331,1134,232,1329,1602,2819,761,775,320,758,2017,1732,1677,655,1157,1355,1020,1960,1562,1699,2150,696,1513,1144,1472,327,876,1600,1696,910,1615,2257,2482,1396,2227,1826,1686,1713,2548,1613,2019,503,1616,1313,2163,2553,2804,3589,2918,2748,2946,3688,3027,4678,2530,2587,2646,2926,2999,3040,3021,3045,2879,3047,5536,2986,2897,2974,1304,2512,2825,3024,3009,2900,2728,2850,2603,3034,3722,2704,1466,2838,2198,2733,2940,1846,2848,2495,3042,2930,4975,3919,2580,2799,5074,3490,2929,2875,2811,4249,749,5404,5174,877,2824,678,1035,3023,2676,5569}'::integer[])))
  • Rows Removed by Filter: 2466
  • Buffers: shared hit=192
86. 0.369 1.286 ↓ 1.0 2,483 1

Hash (cost=95.81..95.81 rows=2,481 width=5) (actual time=1.286..1.286 rows=2,483 loops=1)

  • Output: ui_1.duplicatename, ui_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 129kB
  • Buffers: shared hit=71
87. 0.917 0.917 ↓ 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.008..0.917 rows=2,483 loops=1)

  • Output: ui_1.duplicatename, ui_1.id
  • Buffers: shared hit=71
88. 2.119 35,221.003 ↑ 1.8 5,873 1

Hash Left Join (cost=614.22..1,175.43 rows=10,559 width=2,551) (actual time=35,203.975..35,221.003 rows=5,873 loops=1)

  • Output: cl.name, pj.name, ui.lastname, ui.firstname, timeexpensecostbilling2_facts6.userduplicatename7, login.loginname, ui.externalid, ui.id, timeexpensecostbilling2_facts6.billableduration0, currencyinfo8.symbol, timeexpensecostbilling2_facts6.hourlyrate3, timeexpensecostbilling2_facts6.entrydatesheetdate4, timeexpensecostbilling2_facts6.currencyid5, timeexpensecostbilling2_facts6.billingamount1, timeexpensecostbilling2_facts6.billableexpenseinbasecurrency2, timeexpensecostbilling2_facts6.userid6
  • Hash Cond: (timeexpensecostbilling2_facts6.currencyid5 = currencyinfo8.id)
  • Buffers: shared hit=6336145 read=4060, temp read=34866 written=34864
89. 1.669 35,218.869 ↑ 1.8 5,873 1

Hash Left Join (cost=612.36..1,113.91 rows=10,559 width=239) (actual time=35,203.945..35,218.869 rows=5,873 loops=1)

  • Output: timeexpensecostbilling2_facts6.userduplicatename7, timeexpensecostbilling2_facts6.billableduration0, timeexpensecostbilling2_facts6.hourlyrate3, timeexpensecostbilling2_facts6.entrydatesheetdate4, timeexpensecostbilling2_facts6.currencyid5, timeexpensecostbilling2_facts6.billingamount1, timeexpensecostbilling2_facts6.billableexpenseinbasecurrency2, timeexpensecostbilling2_facts6.userid6, pj.name, cl.name, ui.lastname, ui.firstname, ui.externalid, ui.id, login.loginname
  • Hash Cond: (timeexpensecostbilling2_facts6.projectid8 = pj.id)
  • Buffers: shared hit=6336144 read=4060, temp read=34866 written=34864
90. 2.109 35,214.459 ↑ 1.8 5,873 1

Hash Left Join (cost=272.76..629.12 rows=10,559 width=180) (actual time=35,201.178..35,214.459 rows=5,873 loops=1)

  • Output: timeexpensecostbilling2_facts6.userduplicatename7, timeexpensecostbilling2_facts6.billableduration0, timeexpensecostbilling2_facts6.hourlyrate3, timeexpensecostbilling2_facts6.entrydatesheetdate4, timeexpensecostbilling2_facts6.currencyid5, timeexpensecostbilling2_facts6.billingamount1, timeexpensecostbilling2_facts6.billableexpenseinbasecurrency2, timeexpensecostbilling2_facts6.userid6, timeexpensecostbilling2_facts6.projectid8, ui.lastname, ui.firstname, ui.externalid, ui.id, login.loginname
  • Hash Cond: (timeexpensecostbilling2_facts6.userid6 = ui.id)
  • Buffers: shared hit=6335928 read=4060, temp read=34866 written=34864
91. 35,209.671 35,209.671 ↑ 1.8 5,873 1

CTE Scan on eexpensecostbilling2_facts0cte timeexpensecostbilling2_facts6 (cost=0.00..211.18 rows=10,559 width=133) (actual time=35,198.474..35,209.671 rows=5,873 loops=1)

  • Output: timeexpensecostbilling2_facts6.billableduration0, timeexpensecostbilling2_facts6.billingamount1, timeexpensecostbilling2_facts6.billableexpenseinbasecurrency2, timeexpensecostbilling2_facts6.hourlyrate3, timeexpensecostbilling2_facts6.entrydatesheetdate4, timeexpensecostbilling2_facts6.currencyid5, timeexpensecostbilling2_facts6.userid6, timeexpensecostbilling2_facts6.userduplicatename7, timeexpensecostbilling2_facts6.projectid8
  • Buffers: shared hit=6335801 read=4060, temp read=34866 written=34864
92. 0.674 2.679 ↓ 1.0 2,483 1

Hash (cost=241.75..241.75 rows=2,481 width=47) (actual time=2.679..2.679 rows=2,483 loops=1)

  • Output: ui.lastname, ui.firstname, ui.externalid, ui.id, login.loginname
  • Buckets: 4096 Batches: 1 Memory Usage: 233kB
  • Buffers: shared hit=127
93. 0.838 2.005 ↓ 1.0 2,483 1

Hash Left Join (cost=111.82..241.75 rows=2,481 width=47) (actual time=0.902..2.005 rows=2,483 loops=1)

  • Output: ui.lastname, ui.firstname, ui.externalid, ui.id, login.loginname
  • Hash Cond: (ui.id = login.userid)
  • Buffers: shared hit=127
94. 0.298 0.298 ↓ 1.0 2,483 1

Seq Scan on logicinforef.userinfo ui (cost=0.00..95.81 rows=2,481 width=34) (actual time=0.005..0.298 rows=2,483 loops=1)

  • 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
  • Buffers: shared hit=71
95. 0.495 0.869 ↓ 1.0 2,483 1

Hash (cost=80.81..80.81 rows=2,481 width=17) (actual time=0.869..0.869 rows=2,483 loops=1)

  • Output: login.loginname, login.userid
  • Buckets: 4096 Batches: 1 Memory Usage: 156kB
  • Buffers: shared hit=56
96. 0.374 0.374 ↓ 1.0 2,483 1

Seq Scan on logicinforef.login (cost=0.00..80.81 rows=2,481 width=17) (actual time=0.004..0.374 rows=2,483 loops=1)

  • Output: login.loginname, login.userid
  • Buffers: shared hit=56
97. 0.757 2.741 ↓ 1.0 3,118 1

Hash (cost=300.77..300.77 rows=3,107 width=67) (actual time=2.741..2.741 rows=3,118 loops=1)

  • Output: pj.name, pj.id, cl.name
  • Buckets: 4096 Batches: 1 Memory Usage: 340kB
  • Buffers: shared hit=216
98. 1.289 1.984 ↓ 1.0 3,118 1

Hash Left Join (cost=35.20..300.77 rows=3,107 width=67) (actual time=0.230..1.984 rows=3,118 loops=1)

  • Output: pj.name, pj.id, cl.name
  • Hash Cond: (pj.clientid = cl.id)
  • Buffers: shared hit=216
99. 0.480 0.480 ↓ 1.0 3,118 1

Seq Scan on logicinforef.project pj (cost=0.00..223.07 rows=3,107 width=56) (actual time=0.003..0.480 rows=3,118 loops=1)

  • 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
  • Buffers: shared hit=192
100. 0.120 0.215 ↑ 1.0 498 1

Hash (cost=28.98..28.98 rows=498 width=19) (actual time=0.215..0.215 rows=498 loops=1)

  • Output: cl.name, cl.id
  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=24
101. 0.095 0.095 ↑ 1.0 498 1

Seq Scan on logicinforef.clients cl (cost=0.00..28.98 rows=498 width=19) (actual time=0.004..0.095 rows=498 loops=1)

  • Output: cl.name, cl.id
  • Buffers: shared hit=24
102. 0.008 0.015 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=8) (actual time=0.015..0.015 rows=38 loops=1)

  • Output: currencyinfo8.symbol, currencyinfo8.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
103. 0.007 0.007 ↑ 1.0 38 1

Seq Scan on logicinforef.currencyinfo currencyinfo8 (cost=0.00..1.38 rows=38 width=8) (actual time=0.003..0.007 rows=38 loops=1)

  • Output: currencyinfo8.symbol, currencyinfo8.id
  • Buffers: shared hit=1
Planning time : 61.562 ms
Execution time : 35,292.200 ms