explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aTkS : pike_2 03oct2019

Settings
# exclusive inclusive rows x rows loops node
1. 88.404 45,774.046 ↓ 57.9 18,882 1

Sort (cost=3,184,783.76..3,184,784.57 rows=326 width=2,475) (actual time=45,773.051..45,774.046 rows=18,882 loops=1)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.info16)::character varying(255)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", timeexpensecostbilling2_facts8.billingratename3 COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", timeexpensecostbilling2_facts8.userduplicatename11, ((login.loginname)::character varying(255)) COLLATE "en_US", timeexpensecostbilling2_facts8.entrydateincurreddate4, timeexpensecostbilling2_facts8.timeentryinfo15 COLLATE "en_US", ((currencyinfo11.symbol)::character varying(50)) COLLATE "en_US", timeexpensecostbilling2_facts8.hourlyrate7, timeexpensecostbilling2_facts8.timeentryinfo48 COLLATE "en_US", ((expensetype12.name)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 5765kB
  • Buffers: shared hit=11972924, temp read=74141 written=74141
2.          

CTE eexpensecostbilling2_facts0cte

3. 12.353 45,509.190 ↓ 57.9 18,882 1

GroupAggregate (cost=3,183,134.78..3,183,160.86 rows=326 width=381) (actual time=45,494.845..45,509.190 rows=18,882 loops=1)

  • Group Key: (upper((timeexpensecostbilling2_facts0.billingratename)::text)), timeexpensecostbilling2_facts0.entrydateincurreddate, (upper((timeexpensecostbilling2_facts0.timeentryinfo1)::text)), timeexpensecostbilling2_facts0.timeallocationid, timeexpensecostbilling2_facts0.hourlyrate, (upper((timeexpensecostbilling2_facts0.timeentryinfo4)::text)), (CASE WHEN ((timeexpensecostbilling2_facts0.timeentryinfo3)::text ~ '^\s*[-]?[0-9]+\.{0,1}[0-9]{0,}\s*$'::text) THEN (timeexpensecostbilling2_facts0.timeentryinfo3)::numeric ELSE NULL::numeric END), timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.expensetypeid
  • Buffers: shared hit=11757314, temp read=74141 written=74141
4. 29.958 45,496.837 ↓ 57.9 18,885 1

Sort (cost=3,183,134.78..3,183,135.60 rows=326 width=381) (actual time=45,494.833..45,496.837 rows=18,885 loops=1)

  • Sort Key: (upper((timeexpensecostbilling2_facts0.billingratename)::text)), timeexpensecostbilling2_facts0.entrydateincurreddate, (upper((timeexpensecostbilling2_facts0.timeentryinfo1)::text)), timeexpensecostbilling2_facts0.timeallocationid, timeexpensecostbilling2_facts0.hourlyrate, (upper((timeexpensecostbilling2_facts0.timeentryinfo4)::text)), (CASE WHEN ((timeexpensecostbilling2_facts0.timeentryinfo3)::text ~ '^\s*[-]?[0-9]+\.{0,1}[0-9]{0,}\s*$'::text) THEN (timeexpensecostbilling2_facts0.timeentryinfo3)::numeric ELSE NULL::numeric END), timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid, timeexpensecostbilling2_facts0.currencyid, timeexpensecostbilling2_facts0.expensetypeid
  • Sort Method: quicksort Memory: 1854kB
  • Buffers: shared hit=11757314, temp read=74141 written=74141
5. 18.515 45,466.879 ↓ 57.9 18,885 1

Nested Loop Left Join (cost=192,858.34..3,183,121.17 rows=326 width=381) (actual time=4,295.406..45,466.879 rows=18,885 loops=1)

  • Buffers: shared hit=11757314, temp read=74141 written=74141
6. 30.458 45,448.364 ↓ 57.9 18,885 1

Hash Join (cost=192,858.06..3,183,017.54 rows=326 width=284) (actual time=4,295.380..45,448.364 rows=18,885 loops=1)

  • Hash Cond: (timeexpensecostbilling2_facts0.projectid = pj_1.id)
  • Buffers: shared hit=11753534, temp read=74141 written=74141
7. 631.943 45,415.703 ↓ 5.0 242,012 1

Subquery Scan on timeexpensecostbilling2_facts0 (cost=192,349.02..3,182,325.39 rows=47,962 width=284) (actual time=1,691.717..45,415.703 rows=242,012 loops=1)

  • Filter: (((timeexpensecostbilling2_facts0.entrydatesheetdate >= '2019-09-23'::date) AND (timeexpensecostbilling2_facts0.entrydatesheetdate <= '2019-09-30'::date)) OR (timeexpensecostbilling2_facts0.entrydatesheetdate IS NULL))
  • Rows Removed by Filter: 6514833
  • Buffers: shared hit=11750775, temp read=74141 written=74141
8. 665.924 44,783.760 ↓ 1.4 6,756,845 1

Append (cost=192,349.02..3,110,202.32 rows=4,808,205 width=1,052) (actual time=1,689.929..44,783.760 rows=6,756,845 loops=1)

  • Buffers: shared hit=11750775, temp read=74141 written=74141
9. 974.157 14,787.138 ↓ 1.0 3,686,183 1

Result (cost=192,349.02..946,880.80 rows=3,663,736 width=1,052) (actual time=1,689.928..14,787.138 rows=3,686,183 loops=1)

  • Buffers: shared hit=518286
10. 362.572 13,812.981 ↓ 1.0 3,686,183 1

Append (cost=192,349.02..891,924.76 rows=3,663,736 width=1,044) (actual time=1,689.927..13,812.981 rows=3,686,183 loops=1)

  • Buffers: shared hit=518286
11. 1,794.913 13,353.233 ↓ 1.0 3,681,983 1

Hash Left Join (cost=192,349.02..844,213.83 rows=3,663,735 width=1,414) (actual time=1,689.926..13,353.233 rows=3,681,983 loops=1)

  • Hash Cond: ((at_1.projectid = ptm.projectid) AND (at_1.userid = ptm.userid) AND (at_1.entrydate = ptma.date))
  • Buffers: shared hit=481744
12.          

Initplan (forHash Left Join)

13. 0.009 0.009 ↑ 1.0 1 1

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

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

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

  • Buffers: shared hit=1
15. 1,199.086 11,549.685 ↓ 1.0 3,681,983 1

Hash Left Join (cost=188,454.37..638,813.74 rows=3,663,735 width=754) (actual time=1,681.249..11,549.685 rows=3,681,983 loops=1)

  • Hash Cond: (at_1.taskid = tk_1.id)
  • Buffers: shared hit=463998
16. 973.403 10,171.283 ↓ 1.0 3,681,983 1

Hash Left Join (cost=166,391.43..607,133.41 rows=3,663,735 width=750) (actual time=1,499.986..10,171.283 rows=3,681,983 loops=1)

  • Hash Cond: (at_1.billingrateid = br.id)
  • Buffers: shared hit=452747
17. 1,207.894 9,196.544 ↓ 1.0 3,681,983 1

Hash Left Join (cost=166,239.92..597,358.78 rows=3,663,735 width=706) (actual time=1,498.606..9,196.544 rows=3,681,983 loops=1)

  • Hash Cond: (pbr.id = pbrh.projectbillingrateid)
  • Join Filter: ((at_1.entrydate >= pbrh.effectivedate) AND (at_1.entrydate <= pbrh.enddate))
  • Rows Removed by Join Filter: 1037107
  • Buffers: shared hit=452696
18. 943.928 7,617.387 ↓ 1.0 3,681,983 1

Hash Left Join (cost=132,238.51..419,963.51 rows=3,663,735 width=709) (actual time=1,123.428..7,617.387 rows=3,681,983 loops=1)

  • Hash Cond: (at_1.projectid = pj_2.id)
  • Buffers: shared hit=441955
19. 1,221.839 6,670.167 ↓ 1.0 3,681,983 1

Hash Join (cost=131,741.41..409,844.64 rows=3,663,735 width=705) (actual time=1,120.103..6,670.167 rows=3,681,983 loops=1)

  • Hash Cond: (at_1.timesheetid = ts.id)
  • Buffers: shared hit=441657
20. 1,248.179 5,316.745 ↓ 1.0 3,681,994 1

Merge Left Join (cost=119,077.92..387,522.17 rows=3,679,572 width=693) (actual time=986.566..5,316.745 rows=3,681,994 loops=1)

  • Merge Cond: ((at_1.billingrateid = pbr.billingrateid) AND (at_1.projectid = pbr.projectid))
  • Join Filter: (((pbr.billingrateid = $5) AND (at_1.userid = pbr.userid)) OR ((pbr.billingrateid <> $6) AND (pbr.userid IS NULL)))
  • Rows Removed by Join Filter: 265004
  • Buffers: shared hit=437585
21. 2,775.594 2,775.594 ↓ 1.0 3,681,994 1

Index Scan using ixpta4billingrateid on dm_projecttimeallocation_facts at_1 (cost=0.56..235,900.24 rows=3,679,572 width=677) (actual time=0.030..2,775.594 rows=3,681,994 loops=1)

  • Filter: (timeoffcodeid IS NULL)
  • Rows Removed by Filter: 113312
  • Buffers: shared hit=429086
22. 1,177.414 1,292.972 ↓ 3.4 3,424,519 1

Sort (cost=119,052.98..121,572.19 rows=1,007,683 width=40) (actual time=986.419..1,292.972 rows=3,424,519 loops=1)

  • Sort Key: pbr.billingrateid, pbr.projectid
  • Sort Method: quicksort Memory: 103286kB
  • Buffers: shared hit=8497
23. 115.558 115.558 ↑ 1.0 1,007,484 1

Seq Scan on projectbillingrate pbr (cost=0.00..18,573.83 rows=1,007,683 width=40) (actual time=0.005..115.558 rows=1,007,484 loops=1)

  • Buffers: shared hit=8497
24. 79.844 131.583 ↑ 1.0 381,774 1

Hash (cost=7,890.44..7,890.44 rows=381,844 width=28) (actual time=131.583..131.583 rows=381,774 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 26466kB
  • Buffers: shared hit=4072
25. 51.739 51.739 ↑ 1.0 381,774 1

Seq Scan on timesheet ts (cost=0.00..7,890.44 rows=381,844 width=28) (actual time=0.005..51.739 rows=381,774 loops=1)

  • Buffers: shared hit=4072
26. 1.127 3.292 ↓ 1.0 8,854 1

Hash (cost=386.49..386.49 rows=8,849 width=8) (actual time=3.292..3.292 rows=8,854 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 440kB
  • Buffers: shared hit=298
27. 2.165 2.165 ↓ 1.0 8,854 1

Seq Scan on project pj_2 (cost=0.00..386.49 rows=8,849 width=8) (actual time=0.004..2.165 rows=8,854 loops=1)

  • Buffers: shared hit=298
28. 234.206 371.263 ↓ 1.0 1,033,953 1

Hash (cost=21,078.96..21,078.96 rows=1,033,796 width=33) (actual time=371.263..371.263 rows=1,033,953 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 76732kB
  • Buffers: shared hit=10741
29. 137.057 137.057 ↓ 1.0 1,033,953 1

Seq Scan on projectbillingratehistory pbrh (cost=0.00..21,078.96 rows=1,033,796 width=33) (actual time=0.008..137.057 rows=1,033,953 loops=1)

  • Buffers: shared hit=10741
30. 0.812 1.336 ↑ 1.0 4,372 1

Hash (cost=95.67..95.67 rows=4,467 width=44) (actual time=1.336..1.336 rows=4,372 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 389kB
  • Buffers: shared hit=51
31. 0.524 0.524 ↑ 1.0 4,372 1

Seq Scan on billingrate br (cost=0.00..95.67 rows=4,467 width=44) (actual time=0.005..0.524 rows=4,372 loops=1)

  • Buffers: shared hit=51
32. 74.567 179.316 ↓ 1.0 480,834 1

Hash (cost=16,056.31..16,056.31 rows=480,531 width=8) (actual time=179.316..179.316 rows=480,834 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 21001kB
  • Buffers: shared hit=11251
33. 104.749 104.749 ↓ 1.0 480,834 1

Seq Scan on task tk_1 (cost=0.00..16,056.31 rows=480,531 width=8) (actual time=0.005..104.749 rows=480,834 loops=1)

  • Buffers: shared hit=11251
34. 0.951 8.625 ↑ 1.0 4,425 1

Hash (cost=3,815.19..3,815.19 rows=4,425 width=44) (actual time=8.625..8.625 rows=4,425 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 393kB
  • Buffers: shared hit=17746
35. 2.882 7.674 ↑ 1.0 4,425 1

Nested Loop (cost=0.42..3,815.19 rows=4,425 width=44) (actual time=0.023..7.674 rows=4,425 loops=1)

  • Buffers: shared hit=17746
36. 0.367 0.367 ↑ 1.0 4,425 1

Seq Scan on projectteammemberallocation ptma (cost=0.00..90.25 rows=4,425 width=52) (actual time=0.006..0.367 rows=4,425 loops=1)

  • Buffers: shared hit=46
37. 4.425 4.425 ↑ 1.0 1 4,425

Index Scan using projectteammember_pkey on projectteammember ptm (cost=0.42..0.84 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=4,425)

  • Index Cond: (id = ptma.projectteammemberid)
  • Buffers: shared hit=17700
38. 1.239 97.176 ↓ 4,200.0 4,200 1

Subquery Scan on *SELECT* 2 (cost=1,268.96..11,073.57 rows=1 width=1,044) (actual time=10.587..97.176 rows=4,200 loops=1)

  • Buffers: shared hit=36542
39. 8.627 95.937 ↓ 4,200.0 4,200 1

Gather (cost=1,268.96..11,073.56 rows=1 width=1,044) (actual time=10.585..95.937 rows=4,200 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=36542
40. 8.135 87.310 ↓ 2,100.0 2,100 2

Nested Loop Left Join (cost=268.96..10,073.46 rows=1 width=1,044) (actual time=8.848..87.310 rows=2,100 loops=2)

  • Filter: (at_2.id IS NULL)
  • Rows Removed by Filter: 138
  • Buffers: shared hit=36542
41. 6.630 79.172 ↑ 1.2 2,212 2

Merge Join (cost=268.53..3,907.11 rows=2,603 width=44) (actual time=8.802..79.172 rows=2,212 loops=2)

  • Merge Cond: (ptm_1.id = ptma_1.projectteammemberid)
  • Buffers: shared hit=17396
42. 71.475 71.475 ↑ 1.2 78,680 2

Parallel Index Scan using projectteammember_pkey on projectteammember ptm_1 (cost=0.42..3,554.12 rows=95,389 width=24) (actual time=0.038..71.475 rows=78,680 loops=2)

  • Buffers: shared hit=17327
43. 1.067 1.067 ↑ 1.0 4,425 2

Index Scan using uix2projectteammemberallocation on projectteammemberallocation ptma_1 (cost=0.28..136.66 rows=4,425 width=52) (actual time=0.022..1.067 rows=4,425 loops=2)

  • Filter: (id IS NOT NULL)
  • Buffers: shared hit=69
44. 0.003 0.003 ↓ 0.0 0 4,425

Index Scan using ixpta2projectid on dm_projecttimeallocation_facts at_2 (cost=0.43..2.36 rows=1 width=28) (actual time=0.003..0.003 rows=0 loops=4,425)

  • Index Cond: ((ptm_1.projectid = projectid) AND (ptma_1.date = entrydate))
  • Filter: (ptm_1.userid = userid)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=19146
45. 72.276 575.724 ↓ 9.0 290,043 1

Subquery Scan on *SELECT* 3 (cost=3.13..9,633.35 rows=32,197 width=1,052) (actual time=0.057..575.724 rows=290,043 loops=1)

  • Buffers: shared hit=350729
46. 319.759 503.448 ↓ 9.0 290,043 1

Nested Loop (cost=3.13..9,311.38 rows=32,197 width=2,410) (actual time=0.055..503.448 rows=290,043 loops=1)

  • Buffers: shared hit=350729
47.          

Initplan (forNested Loop)

48. 0.003 0.003 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
49. 26.784 34.584 ↓ 8.9 74,551 1

Hash Join (cost=1.70..3,795.04 rows=8,344 width=30) (actual time=0.037..34.584 rows=74,551 loops=1)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate_1.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate_1.effectivedate) AND (ex.expensedate <= exchangerate_1.enddate))
  • Buffers: shared hit=1073
50. 7.782 7.782 ↑ 1.0 74,551 1

Seq Scan on expense ex (cost=0.00..1,821.99 rows=75,099 width=27) (actual time=0.004..7.782 rows=74,551 loops=1)

  • Buffers: shared hit=1071
51. 0.007 0.018 ↑ 1.0 7 1

Hash (cost=1.61..1.61 rows=7 width=19) (actual time=0.018..0.018 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
52. 0.011 0.011 ↑ 1.0 7 1

Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=7 width=19) (actual time=0.008..0.011 rows=7 loops=1)

  • Filter: (variablecurrencyid = $4)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=2
53. 149.102 149.102 ↑ 1.0 4 74,551

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.42..0.56 rows=4 width=1,615) (actual time=0.002..0.002 rows=4 loops=74,551)

  • Index Cond: (expenseid = ex.id)
  • Buffers: shared hit=349656
54. 51.262 1,711.724 ↓ 1.6 195,748 1

Subquery Scan on *SELECT* 4 (cost=119,557.72..139,818.68 rows=120,133 width=1,052) (actual time=1,462.194..1,711.724 rows=195,748 loops=1)

  • Buffers: shared hit=551029
55. 299.755 1,660.462 ↓ 1.6 195,748 1

Seq Scan on task tsk (cost=119,557.72..138,016.69 rows=120,133 width=1,044) (actual time=1,462.192..1,660.462 rows=195,748 loops=1)

  • Filter: ((NOT (hashed SubPlan 3)) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 285086
  • Buffers: shared hit=551029
56.          

SubPlan (forSeq Scan)

57. 219.859 1,312.485 ↓ 2.1 283,955 1

Unique (cost=0.43..113,349.07 rows=138,104 width=4) (actual time=0.050..1,312.485 rows=283,955 loops=1)

  • Buffers: shared hit=537673
58. 1,092.626 1,092.626 ↓ 1.0 3,474,110 1

Index Only Scan using ixpta2taskid on dm_projecttimeallocation_facts at (cost=0.43..104,668.47 rows=3,472,239 width=4) (actual time=0.049..1,092.626 rows=3,474,110 loops=1)

  • Index Cond: (taskid IS NOT NULL)
  • Heap Fetches: 1255256
  • Buffers: shared hit=537673
59. 19.994 48.222 ↓ 1.1 76,134 1

Unique (cost=0.42..5,689.32 rows=69,629 width=4) (actual time=0.035..48.222 rows=76,134 loops=1)

  • Buffers: shared hit=2105
60. 28.228 28.228 ↓ 1.0 248,990 1

Index Only Scan using ixeetaskid on expenseentry (cost=0.42..5,067.09 rows=248,895 width=4) (actual time=0.034..28.228 rows=248,990 loops=1)

  • Index Cond: (taskid IS NOT NULL)
  • Heap Fetches: 8693
  • Buffers: shared hit=2105
61. 801.429 27,043.250 ↓ 2.6 2,584,871 1

Subquery Scan on *SELECT* 5 (cost=1,021,595.82..2,013,869.48 rows=992,139 width=949) (actual time=13,488.131..27,043.250 rows=2,584,871 loops=1)

  • Buffers: shared hit=10330731, temp read=74141 written=74141
62. 2,731.598 26,241.821 ↓ 2.6 2,584,871 1

Nested Loop Left Join (cost=1,021,595.82..1,998,987.40 rows=992,139 width=941) (actual time=13,488.129..26,241.821 rows=2,584,871 loops=1)

  • Buffers: shared hit=10330731, temp read=74141 written=74141
63.          

Initplan (forNested Loop Left Join)

64. 0.007 0.007 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
65. 0.005 0.005 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
66. 529.965 15,755.598 ↓ 2.6 2,584,871 1

Hash Left Join (cost=1,021,593.37..1,086,533.05 rows=992,139 width=181) (actual time=13,488.104..15,755.598 rows=2,584,871 loops=1)

  • Hash Cond: (bi.id = iibi.billingitemid)
  • Buffers: shared hit=136592, temp read=74141 written=74141
67. 1,750.031 15,225.630 ↓ 2.6 2,584,871 1

Hash Right Join (cost=1,021,559.30..1,083,894.61 rows=992,139 width=165) (actual time=13,488.079..15,225.630 rows=2,584,871 loops=1)

  • Hash Cond: (aggm.billingitemid = bi.id)
  • Filter: (aggm.uri IS NULL)
  • Rows Removed by Filter: 10253
  • Buffers: shared hit=136592, temp read=74141 written=74141
68. 10.302 10.302 ↑ 2.0 11,311 1

Index Scan using ixbiminvoicinghint on billingitem2metadata aggm (cost=0.41..895.67 rows=22,539 width=65) (actual time=0.034..10.302 rows=11,311 loops=1)

  • Buffers: shared hit=181
69. 1,189.606 13,465.297 ↓ 1.0 2,595,124 1

Hash (cost=928,597.81..928,597.81 rows=2,586,726 width=165) (actual time=13,465.297..13,465.297 rows=2,595,124 loops=1)

  • Buckets: 2097152 Batches: 2 Memory Usage: 276792kB
  • Buffers: shared hit=136411, temp read=43350 written=74054
70. 792.058 12,275.691 ↓ 1.0 2,595,124 1

Hash Left Join (cost=775,617.63..928,597.81 rows=2,586,726 width=165) (actual time=7,361.491..12,275.691 rows=2,595,124 loops=1)

  • Hash Cond: (bidm.timesheetid = t.id)
  • Buffers: shared hit=136411, temp read=43350 written=43352
71. 773.179 11,360.679 ↓ 1.0 2,595,124 1

Hash Left Join (cost=762,954.14..909,144.10 rows=2,586,726 width=153) (actual time=7,236.602..11,360.679 rows=2,595,124 loops=1)

  • 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: 1037297
  • Buffers: shared hit=132339, temp read=43350 written=43352
72. 575.594 10,256.921 ↓ 1.0 2,595,124 1

Hash Join (cost=728,952.73..773,901.58 rows=2,586,726 width=165) (actual time=6,902.443..10,256.921 rows=2,595,124 loops=1)

  • Hash Cond: (bidm.projectid = pj_3.id)
  • Buffers: shared hit=121598, temp read=43350 written=43352
73. 643.552 9,679.088 ↓ 1.0 2,595,124 1

Hash Left Join (cost=728,682.09..766,837.62 rows=2,586,726 width=165) (actual time=6,900.183..9,679.088 rows=2,595,124 loops=1)

  • Hash Cond: (bidm.billingrateid = br_1.id)
  • Buffers: shared hit=121401, temp read=43350 written=43352
74. 1,147.677 9,034.315 ↓ 1.0 2,595,124 1

Merge Left Join (cost=728,530.59..759,892.13 rows=2,586,726 width=137) (actual time=6,898.936..9,034.315 rows=2,595,124 loops=1)

  • 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: 265004
  • Buffers: shared hit=121350, temp read=43350 written=43352
75. 3,655.625 7,013.744 ↓ 1.0 2,595,124 1

Sort (cost=609,477.61..615,944.42 rows=2,586,726 width=121) (actual time=6,256.767..7,013.744 rows=2,595,124 loops=1)

  • Sort Key: bidm.projectid, bidm.billingrateid
  • Sort Method: external merge Disk: 346800kB
  • Buffers: shared hit=112851, temp read=43350 written=43352
76. 1,593.675 3,358.119 ↓ 1.0 2,595,124 1

Hash Join (cost=104,702.49..237,964.42 rows=2,586,726 width=121) (actual time=1,163.820..3,358.119 rows=2,595,124 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=112851
77. 615.858 615.858 ↓ 1.0 4,102,330 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..122,497.64 rows=4,100,679 width=109) (actual time=0.005..615.858 rows=4,102,330 loops=1)

  • Filter: (userid IS NOT NULL)
  • Rows Removed by Filter: 33
  • Buffers: shared hit=81490
78. 675.719 1,148.586 ↓ 1.0 2,595,157 1

Hash (cost=72,368.14..72,368.14 rows=2,586,748 width=28) (actual time=1,148.586..1,148.586 rows=2,595,157 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 189142kB
  • Buffers: shared hit=31361
79. 472.867 472.867 ↓ 1.0 2,595,157 1

Seq Scan on billingitem2 bi (cost=0.00..72,368.14 rows=2,586,748 width=28) (actual time=0.009..472.867 rows=2,595,157 loops=1)

  • Filter: isbillable
  • Rows Removed by Filter: 1507206
  • Buffers: shared hit=31361
80. 762.375 872.894 ↓ 3.4 3,424,667 1

Sort (cost=119,052.98..121,572.19 rows=1,007,683 width=40) (actual time=642.143..872.894 rows=3,424,667 loops=1)

  • Sort Key: pbr_1.projectid, pbr_1.billingrateid
  • Sort Method: quicksort Memory: 103286kB
  • Buffers: shared hit=8497
81. 110.519 110.519 ↑ 1.0 1,007,484 1

Seq Scan on projectbillingrate pbr_1 (cost=0.00..18,573.83 rows=1,007,683 width=40) (actual time=0.011..110.519 rows=1,007,484 loops=1)

  • Buffers: shared hit=8497
82. 0.674 1.221 ↑ 1.0 4,372 1

Hash (cost=95.67..95.67 rows=4,467 width=44) (actual time=1.221..1.221 rows=4,372 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 389kB
  • Buffers: shared hit=51
83. 0.547 0.547 ↑ 1.0 4,372 1

Seq Scan on billingrate br_1 (cost=0.00..95.67 rows=4,467 width=44) (actual time=0.007..0.547 rows=4,372 loops=1)

  • Buffers: shared hit=51
84. 1.106 2.239 ↓ 1.0 8,854 1

Hash (cost=160.02..160.02 rows=8,849 width=4) (actual time=2.239..2.239 rows=8,854 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 440kB
  • Buffers: shared hit=197
85. 1.133 1.133 ↓ 1.0 8,854 1

Index Only Scan using project_pkey on project pj_3 (cost=0.29..160.02 rows=8,849 width=4) (actual time=0.037..1.133 rows=8,854 loops=1)

  • Heap Fetches: 386
  • Buffers: shared hit=197
86. 202.407 330.579 ↓ 1.0 1,033,953 1

Hash (cost=21,078.96..21,078.96 rows=1,033,796 width=24) (actual time=330.579..330.579 rows=1,033,953 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 64737kB
  • Buffers: shared hit=10741
87. 128.172 128.172 ↓ 1.0 1,033,953 1

Seq Scan on projectbillingratehistory pbrh_1 (cost=0.00..21,078.96 rows=1,033,796 width=24) (actual time=0.005..128.172 rows=1,033,953 loops=1)

  • Buffers: shared hit=10741
88. 72.823 122.954 ↑ 1.0 381,774 1

Hash (cost=7,890.44..7,890.44 rows=381,844 width=28) (actual time=122.954..122.954 rows=381,774 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 26466kB
  • Buffers: shared hit=4072
89. 50.131 50.131 ↑ 1.0 381,774 1

Seq Scan on timesheet t (cost=0.00..7,890.44 rows=381,844 width=28) (actual time=0.006..50.131 rows=381,774 loops=1)

  • Buffers: shared hit=4072
90. 0.000 0.003 ↓ 0.0 0 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
91. 0.003 0.003 ↓ 0.0 0 1

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

92. 7,754.613 7,754.613 ↑ 3.0 1 2,584,871

Index Scan using ixbimbillingitemidkey on billingitem2metadata bi2m (cost=0.43..0.87 rows=3 width=32) (actual time=0.003..0.003 rows=1 loops=2,584,871)

  • Index Cond: (billingitemid = bi.id)
  • Buffers: shared hit=10194139
93. 0.116 2.203 ↓ 12.3 736 1

Hash (cost=508.29..508.29 rows=60 width=8) (actual time=2.203..2.203 rows=736 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
  • Buffers: shared hit=2759
94. 0.092 2.087 ↓ 12.3 736 1

Nested Loop (cost=0.57..508.29 rows=60 width=8) (actual time=0.043..2.087 rows=736 loops=1)

  • Buffers: shared hit=2759
95. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on program program4 (cost=0.00..1.59 rows=1 width=16) (actual time=0.007..0.009 rows=1 loops=1)

  • Filter: (id = 'ab43879c-9045-4302-991b-948cb6b54652'::uuid)
  • Rows Removed by Filter: 46
  • Buffers: shared hit=1
96. 0.509 1.986 ↓ 12.3 736 1

Nested Loop (cost=0.57..506.10 rows=60 width=24) (actual time=0.035..1.986 rows=736 loops=1)

  • Buffers: shared hit=2758
97. 0.568 0.568 ↓ 6.3 909 1

Index Scan using ixprjclientid on project pj_1 (cost=0.29..317.88 rows=145 width=20) (actual time=0.026..0.568 rows=909 loops=1)

  • Index Cond: (clientid = 31)
  • Filter: (programid = 'ab43879c-9045-4302-991b-948cb6b54652'::uuid)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=25
98. 0.909 0.909 ↑ 1.0 1 909

Index Scan using project_pkey on project project3 (cost=0.29..1.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=909)

  • Index Cond: (id = pj_1.id)
  • Filter: (projectstatuslabelid = '3bbf6214-ea06-4044-8d23-558674d217f1'::uuid)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2733
99. 0.000 0.000 ↓ 0.0 0 18,885

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..0.30 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=18,885)

  • Index Cond: (timeexpensecostbilling2_facts0.userid = id)
  • Buffers: shared hit=3780
100.          

Initplan (forSort)

101. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Buffers: shared hit=1
102. 4.938 45,685.638 ↓ 57.9 18,882 1

Hash Left Join (cost=6.78..1,608.28 rows=326 width=2,475) (actual time=45,495.013..45,685.638 rows=18,882 loops=1)

  • Hash Cond: (timeexpensecostbilling2_facts8.expensetypeid15 = expensetype12.id)
  • Buffers: shared hit=11972924, temp read=74141 written=74141
103. 4.512 45,680.687 ↓ 57.9 18,882 1

Hash Left Join (cost=5.29..1,605.91 rows=326 width=426) (actual time=45,494.987..45,680.687 rows=18,882 loops=1)

  • Hash Cond: (timeexpensecostbilling2_facts8.currencyid14 = currencyinfo11.id)
  • Buffers: shared hit=11972923, temp read=74141 written=74141
104. 12.858 45,676.167 ↓ 57.9 18,882 1

Nested Loop Left Join (cost=4.13..1,603.87 rows=326 width=427) (actual time=45,494.966..45,676.167 rows=18,882 loops=1)

  • Buffers: shared hit=11972922, temp read=74141 written=74141
105. 13.138 45,663.309 ↓ 57.9 18,882 1

Nested Loop Left Join (cost=3.85..1,497.68 rows=326 width=413) (actual time=45,494.951..45,663.309 rows=18,882 loops=1)

  • Buffers: shared hit=11969151, temp read=74141 written=74141
106. 7.245 45,650.171 ↓ 57.9 18,882 1

Nested Loop Left Join (cost=3.56..1,260.88 rows=326 width=395) (actual time=45,494.936..45,650.171 rows=18,882 loops=1)

  • Buffers: shared hit=11965380, temp read=74141 written=74141
107. 5.343 45,605.162 ↓ 57.9 18,882 1

Hash Left Join (cost=3.14..1,107.71 rows=326 width=348) (actual time=45,494.922..45,605.162 rows=18,882 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=11889856, temp read=74141 written=74141
108. 10.748 45,599.790 ↓ 57.9 18,882 1

Nested Loop Left Join (cost=0.71..1,099.58 rows=326 width=352) (actual time=45,494.879..45,599.790 rows=18,882 loops=1)

  • Buffers: shared hit=11889854, temp read=74141 written=74141
109. 15.187 45,551.278 ↓ 57.9 18,882 1

Nested Loop Left Join (cost=0.29..343.14 rows=326 width=335) (actual time=45,494.865..45,551.278 rows=18,882 loops=1)

  • Buffers: shared hit=11814324, temp read=74141 written=74141
110. 45,517.209 45,517.209 ↓ 57.9 18,882 1

CTE Scan on eexpensecostbilling2_facts0cte timeexpensecostbilling2_facts8 (cost=0.00..6.52 rows=326 width=285) (actual time=45,494.848..45,517.209 rows=18,882 loops=1)

  • Buffers: shared hit=11757314, temp read=74141 written=74141
111. 18.882 18.882 ↑ 1.0 1 18,882

Index Scan using project_pkey on project pj (cost=0.29..1.03 rows=1 width=58) (actual time=0.001..0.001 rows=1 loops=18,882)

  • Index Cond: (timeexpensecostbilling2_facts8.projectid12 = id)
  • Buffers: shared hit=57010
112. 37.764 37.764 ↑ 1.0 1 18,882

Index Scan using task_pkey on task tk (cost=0.42..2.32 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=18,882)

  • Index Cond: (timeexpensecostbilling2_facts8.taskid13 = id)
  • Buffers: shared hit=75530
113. 0.005 0.029 ↑ 1.0 7 1

Hash (cost=2.35..2.35 rows=7 width=4) (actual time=0.029..0.029 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
114. 0.024 0.024 ↑ 1.0 7 1

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual time=0.017..0.024 rows=7 loops=1)

  • Filter: ((variablecurrencyid = $17) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=2
115. 37.764 37.764 ↑ 1.0 1 18,882

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.42..0.47 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=18,882)

  • Index Cond: (tk.id = taskid)
  • Buffers: shared hit=75524
116. 0.000 0.000 ↓ 0.0 0 18,882

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.73 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=18,882)

  • Index Cond: (timeexpensecostbilling2_facts8.userid10 = id)
  • Buffers: shared hit=3771
117. 0.000 0.000 ↓ 0.0 0 18,882

Index Scan using login_pkey on login (cost=0.28..0.33 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=18,882)

  • Index Cond: (ui.id = userid)
  • Buffers: shared hit=3771
118. 0.005 0.008 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=7) (actual time=0.007..0.008 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
119. 0.003 0.003 ↑ 1.0 7 1

Seq Scan on currencyinfo currencyinfo11 (cost=0.00..1.07 rows=7 width=7) (actual time=0.003..0.003 rows=7 loops=1)

  • Buffers: shared hit=1
120. 0.009 0.013 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=16) (actual time=0.012..0.013 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
121. 0.004 0.004 ↑ 1.0 22 1

Seq Scan on expensetype expensetype12 (cost=0.00..1.22 rows=22 width=16) (actual time=0.002..0.004 rows=22 loops=1)

  • Buffers: shared hit=1
Planning time : 57.219 ms
Execution time : 45,880.623 ms