explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IowJ

Settings
# exclusive inclusive rows x rows loops node
1. 84.881 5,235,344.448 ↓ 17.2 11,774 1

Sort (cost=305,770.03..305,771.74 rows=685 width=161) (actual time=5,235,341.660..5,235,344.448 rows=11,774 loops=1)

  • Sort Key: ((program.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", pj.startdate, pj.enddate, ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", imeexpensecostbilling2_facts10.timesheetstartdate2
  • Sort Method: quicksort Memory: 3503kB
2.          

CTE eexpensecostbilling2_facts0cte

3. 61.910 5,234,984.467 ↓ 17.2 11,774 1

HashAggregate (cost=303,267.76..303,274.61 rows=685 width=32) (actual time=5,234,974.087..5,234,984.467 rows=11,774 loops=1)

  • Group Key: timeexpensecostbilling2_facts0.userid, ts.startdate, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid
4. 56.906 5,234,922.557 ↓ 53.6 36,720 1

Nested Loop Left Join (cost=13,959.22..303,259.20 rows=685 width=32) (actual time=21,936.329..5,234,922.557 rows=36,720 loops=1)

5. 29,127.839 5,234,388.291 ↓ 53.6 36,720 1

Nested Loop (cost=13,958.39..302,545.24 rows=685 width=44) (actual time=21,935.677..5,234,388.291 rows=36,720 loops=1)

  • Join Filter: (pj_1.id = timeexpensecostbilling2_facts0.projectid)
  • Rows Removed by Join Filter: 100329960
6. 1.939 46.513 ↓ 573.0 573 1

Nested Loop Semi Join (cost=1.14..1,039.49 rows=1 width=20) (actual time=0.089..46.513 rows=573 loops=1)

7. 3.392 41.700 ↓ 958.0 958 1

Nested Loop Semi Join (cost=0.86..1,038.99 rows=1 width=16) (actual time=0.048..41.700 rows=958 loops=1)

8. 2.396 34.736 ↓ 1,786.0 1,786 1

Nested Loop Semi Join (cost=0.57..1,038.50 rows=1 width=12) (actual time=0.033..34.736 rows=1,786 loops=1)

9. 4.111 26.685 ↓ 36.2 1,885 1

Nested Loop (cost=0.29..1,018.04 rows=52 width=8) (actual time=0.028..26.685 rows=1,885 loops=1)

10. 13.149 13.149 ↓ 36.2 1,885 1

Seq Scan on project pj_2 (cost=0.00..964.79 rows=52 width=4) (actual time=0.013..13.149 rows=1,885 loops=1)

  • Filter: (upper((info2)::text) = 'MIX STANDARD'::text)
  • Rows Removed by Filter: 8510
11. 9.425 9.425 ↑ 1.0 1 1,885

Index Only Scan using project_pkey on project pj_1 (cost=0.29..1.01 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,885)

  • Index Cond: (id = pj_2.id)
  • Heap Fetches: 215
12. 5.655 5.655 ↑ 1.0 1 1,885

Index Scan using project_pkey on project pj_3 (cost=0.29..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,885)

  • Index Cond: (id = pj_1.id)
  • Filter: (upper((info7)::text) = 'US'::text)
  • Rows Removed by Filter: 0
13. 3.572 3.572 ↑ 1.0 1 1,786

Index Scan using project_pkey on project pj_4 (cost=0.29..0.39 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,786)

  • Index Cond: (id = pj_1.id)
  • Filter: (upper((info13)::text) = 'ASSIGNED'::text)
  • Rows Removed by Filter: 0
14. 2.874 2.874 ↑ 1.0 1 958

Index Scan using project_pkey on project pj_5 (cost=0.29..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=958)

  • Index Cond: (id = pj_1.id)
  • Filter: (upper((info18)::text) = 'MIX FULL'::text)
  • Rows Removed by Filter: 0
15. 241,270.818 5,205,213.939 ↓ 16.0 175,160 573

Subquery Scan on timeexpensecostbilling2_facts0 (cost=13,957.25..301,368.68 rows=10,965 width=44) (actual time=400.939..9,084.143 rows=175,160 loops=573)

  • Filter: (((timeexpensecostbilling2_facts0.entrydatesheetdate >= '2018-01-01'::date) AND (timeexpensecostbilling2_facts0.entrydatesheetdate <= '2018-06-29'::date)) OR (timeexpensecostbilling2_facts0.entrydatesheetdate IS NULL))
  • Rows Removed by Filter: 1020658
16. 299,228.622 4,963,943.121 ↓ 1.1 1,195,818 573

Append (cost=13,957.25..284,879.69 rows=1,099,266 width=2,772) (actual time=0.542..8,663.077 rows=1,195,818 loops=573)

17. 400,755.054 4,162,627.260 ↓ 1.0 1,048,734 573

Result (cost=13,957.25..216,349.71 rows=1,048,498 width=2,892) (actual time=0.541..7,264.620 rows=1,048,734 loops=573)

18. 262,772.643 3,761,872.206 ↓ 1.0 1,048,734 573

Append (cost=13,957.25..216,349.71 rows=1,048,498 width=2,892) (actual time=0.539..6,565.222 rows=1,048,734 loops=573)

19. 796,750.330 3,499,098.417 ↓ 1.0 1,048,734 573

Hash Left Join (cost=13,957.25..205,864.72 rows=1,048,497 width=2,892) (actual time=0.538..6,106.629 rows=1,048,734 loops=573)

  • Hash Cond: ((at.projectid = ptm.projectid) AND (at.userid = ptm.userid) AND (at.entrydate = ptma.date))
20.          

Initplan (forHash Left Join)

21. 0.436 0.436 ↑ 1.0 1 1

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

22. 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)

23. 420,061.159 2,702,347.647 ↓ 1.0 1,048,734 573

Hash Left Join (cost=11,822.18..126,402.98 rows=1,048,497 width=2,860) (actual time=0.529..4,716.139 rows=1,048,734 loops=573)

  • Hash Cond: (at.taskid = tk_1.id)
24. 374,362.668 2,282,114.031 ↓ 1.0 1,048,734 573

Hash Left Join (cost=5,506.63..105,865.98 rows=1,048,497 width=2,856) (actual time=0.225..3,982.747 rows=1,048,734 loops=573)

  • Hash Cond: (at.billingrateid = br.id)
25. 407,617.657 1,907,751.357 ↓ 1.0 1,048,734 573

Hash Left Join (cost=5,505.59..96,614.04 rows=1,048,497 width=2,722) (actual time=0.223..3,329.409 rows=1,048,734 loops=573)

  • Hash Cond: (pbr.id = pbrh.projectbillingrateid)
  • Join Filter: ((at.entrydate >= pbrh.effectivedate) AND (at.entrydate <= pbrh.enddate))
26. 448,287.188 1,500,116.292 ↓ 1.0 1,048,734 573

Hash Left Join (cost=4,828.82..76,277.95 rows=1,048,497 width=2,727) (actual time=0.191..2,618.004 rows=1,048,734 loops=573)

  • Hash Cond: ((at.projectid = pbr.projectid) AND (at.billingrateid = pbr.billingrateid))
  • Join Filter: (((pbr.billingrateid = $4) AND (at.userid = pbr.userid)) OR ((pbr.billingrateid <> $5) AND (pbr.userid IS NULL)))
27. 382,395.313 1,051,804.530 ↓ 1.0 1,048,734 573

Hash Left Join (cost=4,058.26..58,041.55 rows=1,048,497 width=2,711) (actual time=0.146..1,835.610 rows=1,048,734 loops=573)

  • Hash Cond: (at.projectid = pj_6.id)
28. 448,969.311 669,399.228 ↓ 1.0 1,048,734 573

Hash Join (cost=3,015.58..42,582.38 rows=1,048,497 width=2,707) (actual time=0.126..1,168.236 rows=1,048,734 loops=573)

  • Hash Cond: (at.timesheetid = ts_1.id)
29. 220,362.048 220,362.048 ↓ 1.0 1,048,734 573

Seq Scan on dm_projecttimeallocation_facts at (cost=0.00..25,149.97 rows=1,048,497 width=2,695) (actual time=0.004..384.576 rows=1,048,734 loops=573)

  • Filter: ((timeoffcodeid IS NULL) AND (duration IS NOT NULL))
30. 34.520 67.869 ↓ 1.0 90,654 1

Hash (cost=1,882.48..1,882.48 rows=90,648 width=28) (actual time=67.869..67.869 rows=90,654 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6336kB
31. 33.349 33.349 ↓ 1.0 90,654 1

Seq Scan on timesheet ts_1 (cost=0.00..1,882.48 rows=90,648 width=28) (actual time=0.009..33.349 rows=90,654 loops=1)

32. 3.415 9.989 ↓ 1.0 10,395 1

Hash (cost=912.86..912.86 rows=10,386 width=8) (actual time=9.989..9.989 rows=10,395 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 494kB
33. 6.574 6.574 ↓ 1.0 10,395 1

Seq Scan on project pj_6 (cost=0.00..912.86 rows=10,386 width=8) (actual time=0.004..6.574 rows=10,395 loops=1)

34. 8.545 24.574 ↓ 1.3 20,773 1

Hash (cost=537.61..537.61 rows=15,530 width=40) (actual time=24.574..24.574 rows=20,773 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1636kB
35. 16.029 16.029 ↓ 1.3 20,773 1

Seq Scan on projectbillingrate pbr (cost=0.00..537.61 rows=15,530 width=40) (actual time=0.448..16.029 rows=20,773 loops=1)

  • Filter: ((billingrateid = $4) OR ((billingrateid <> $5) AND (userid IS NULL)))
36. 7.316 17.408 ↑ 1.0 20,769 1

Hash (cost=414.12..414.12 rows=21,012 width=31) (actual time=17.408..17.408 rows=20,769 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1555kB
37. 10.092 10.092 ↑ 1.0 20,769 1

Seq Scan on projectbillingratehistory pbrh (cost=0.00..414.12 rows=21,012 width=31) (actual time=0.391..10.092 rows=20,769 loops=1)

38. 0.001 0.006 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=134) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on billingrate br (cost=0.00..1.02 rows=2 width=134) (actual time=0.003..0.005 rows=2 loops=1)

40. 74.431 172.457 ↓ 1.0 166,937 1

Hash (cost=4,234.13..4,234.13 rows=166,513 width=8) (actual time=172.457..172.457 rows=166,937 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7917kB
41. 98.026 98.026 ↓ 1.0 166,937 1

Seq Scan on task tk_1 (cost=0.00..4,234.13 rows=166,513 width=8) (actual time=0.003..98.026 rows=166,937 loops=1)

42. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=2,115.20..2,115.20 rows=1,020 width=44) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.002 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.42..2,115.20 rows=1,020 width=44) (actual time=0.002..0.002 rows=0 loops=1)

44. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on projectteammemberallocation ptma (cost=0.00..20.20 rows=1,020 width=52) (actual time=0.000..0.000 rows=0 loops=1)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using projectteammember_pkey on projectteammember ptm (cost=0.42..2.04 rows=1 width=24) (never executed)

  • Index Cond: (id = ptma.projectteammemberid)
46. 0.573 1.146 ↓ 0.0 0 573

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=573)

47. 0.573 0.573 ↓ 0.0 0 573

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=573)

  • One-Time Filter: false
48. 0.573 1.719 ↓ 0.0 0 573

Subquery Scan on *SELECT* 3 (cost=2.77..14.46 rows=1 width=3,315) (actual time=0.003..0.003 rows=0 loops=573)

49. 0.573 1.146 ↓ 0.0 0 573

Nested Loop (cost=2.77..14.45 rows=1 width=3,315) (actual time=0.002..0.002 rows=0 loops=573)

  • Join Filter: (((ee.amount * ee.exchangevalue) * exchangerate_1.exchangevalue) IS NOT NULL)
50.          

Initplan (forNested Loop)

51. 0.000 0.000 ↓ 0.0 0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (never executed)

52. 0.000 0.573 ↓ 0.0 0 573

Hash Join (cost=1.62..13.15 rows=1 width=154) (actual time=0.001..0.001 rows=0 loops=573)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate_1.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate_1.effectivedate) AND (ex.expensedate <= exchangerate_1.enddate))
53. 0.573 0.573 ↓ 0.0 0 573

Seq Scan on expense ex (cost=0.00..11.10 rows=110 width=138) (actual time=0.001..0.001 rows=0 loops=573)

54. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.61..1.61 rows=1 width=32) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=1 width=32) (never executed)

  • Filter: (variablecurrencyid = $2)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.14..0.26 rows=1 width=3,165) (never executed)

  • Index Cond: (expenseid = ex.id)
57. 0.573 1.146 ↓ 0.0 0 573

Subquery Scan on *SELECT* 4 (cost=0.00..4,234.14 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=573)

58. 0.573 0.573 ↓ 0.0 0 573

Result (cost=0.00..4,234.13 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=573)

  • One-Time Filter: false
59. 0.000 0.000 ↓ 0.0 0

Seq Scan on task tsk (cost=0.00..4,234.13 rows=1 width=8) (never executed)

60. 74,135.886 502,084.374 ↓ 2.9 147,084 573

Subquery Scan on *SELECT* 5 (cost=50,041.27..64,281.39 rows=50,766 width=285) (actual time=2.945..876.238 rows=147,084 loops=573)

61. 125,509.915 427,948.488 ↓ 2.9 147,084 573

Hash Left Join (cost=50,041.27..63,773.73 rows=50,766 width=285) (actual time=2.941..746.856 rows=147,084 loops=573)

  • Hash Cond: (bi.id = iibi.billingitemid)
62.          

Initplan (forHash Left Join)

63. 0.003 0.003 ↑ 1.0 1 1

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

64. 0.001 0.001 ↑ 1.0 1 1

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

65. 150,580.329 302,438.568 ↓ 2.9 147,084 573

Hash Right Join (cost=50,005.18..62,657.21 rows=50,766 width=269) (actual time=2.930..527.816 rows=147,084 loops=573)

  • Hash Cond: (bi2m.billingitemid = bi.id)
66. 150,189.030 150,189.030 ↓ 23.9 329,614 573

Index Scan using ixbimbillingitemidkey on billingitem2metadata bi2m (cost=0.42..12,579.59 rows=13,774 width=32) (actual time=0.012..262.110 rows=329,614 loops=573)

67. 90.437 1,669.209 ↓ 2.9 147,084 1

Hash (cost=49,370.18..49,370.18 rows=50,766 width=253) (actual time=1,669.209..1,669.209 rows=147,084 loops=1)

  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 31336kB
68. 90.058 1,578.772 ↓ 2.9 147,084 1

Hash Right Join (cost=36,798.25..49,370.18 rows=50,766 width=253) (actual time=1,489.021..1,578.772 rows=147,084 loops=1)

  • Hash Cond: (aggm.billingitemid = bi.id)
  • Filter: (aggm.uri IS NULL)
69. 0.003 0.003 ↓ 0.0 0 1

Index Scan using ixbiminvoicinghint on billingitem2metadata aggm (cost=0.12..12,459.74 rows=13,774 width=76) (actual time=0.003..0.003 rows=0 loops=1)

70. 97.557 1,488.711 ↓ 1.0 147,084 1

Hash (cost=34,983.52..34,983.52 rows=145,169 width=253) (actual time=1,488.711..1,488.711 rows=147,084 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 31336kB
71. 110.628 1,391.154 ↓ 1.0 147,084 1

Hash Left Join (cost=12,355.72..34,983.52 rows=145,169 width=253) (actual time=355.234..1,391.154 rows=147,084 loops=1)

  • Hash Cond: (bidm.timesheetid = t.id)
72. 111.149 1,217.114 ↓ 1.0 147,084 1

Hash Left Join (cost=9,340.14..29,971.86 rows=145,169 width=241) (actual time=291.665..1,217.114 rows=147,084 loops=1)

  • Hash Cond: (pbr_1.id = pbrh_1.projectbillingrateid)
  • Join Filter: ((bi.entrydate >= pbrh_1.effectivedate) AND (bi.entrydate <= pbrh_1.enddate))
73. 123.877 1,092.479 ↓ 1.0 147,084 1

Hash Left Join (cost=8,663.36..26,573.17 rows=145,169 width=253) (actual time=278.137..1,092.479 rows=147,084 loops=1)

  • Hash 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)))
74. 91.330 953.122 ↓ 1.0 147,084 1

Hash Left Join (cost=7,892.81..23,458.87 rows=145,169 width=253) (actual time=262.623..953.122 rows=147,084 loops=1)

  • Hash Cond: (bidm.billingrateid = br_1.id)
75. 92.087 861.787 ↓ 1.0 147,084 1

Hash Join (cost=7,891.76..22,233.76 rows=145,169 width=119) (actual time=262.607..861.787 rows=147,084 loops=1)

  • Hash Cond: (bidm.projectid = pj_7.id)
76. 205.481 763.115 ↓ 1.0 147,084 1

Hash Join (cost=7,543.86..19,889.78 rows=145,169 width=119) (actual time=255.995..763.115 rows=147,084 loops=1)

  • 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)
77. 302.505 302.505 ↑ 1.0 329,614 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..9,286.14 rows=329,614 width=108) (actual time=0.574..302.505 rows=329,614 loops=1)

  • Filter: (userid IS NOT NULL)
78. 59.654 255.129 ↓ 1.0 147,084 1

Hash (cost=5,720.14..5,720.14 rows=145,898 width=27) (actual time=255.129..255.129 rows=147,084 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10523kB
79. 195.475 195.475 ↓ 1.0 147,084 1

Seq Scan on billingitem2 bi (cost=0.00..5,720.14 rows=145,898 width=27) (actual time=0.460..195.475 rows=147,084 loops=1)

  • Filter: isbillable
  • Rows Removed by Filter: 182530
80. 3.258 6.585 ↓ 1.0 10,395 1

Hash (cost=218.08..218.08 rows=10,386 width=4) (actual time=6.585..6.585 rows=10,395 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 494kB
81. 3.327 3.327 ↓ 1.0 10,395 1

Index Only Scan using project_pkey on project pj_7 (cost=0.29..218.08 rows=10,386 width=4) (actual time=0.018..3.327 rows=10,395 loops=1)

  • Heap Fetches: 944
82. 0.001 0.005 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=134) (actual time=0.005..0.005 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on billingrate br_1 (cost=0.00..1.02 rows=2 width=134) (actual time=0.002..0.004 rows=2 loops=1)

84. 7.528 15.480 ↓ 1.3 20,773 1

Hash (cost=537.61..537.61 rows=15,530 width=40) (actual time=15.480..15.480 rows=20,773 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1636kB
85. 7.952 7.952 ↓ 1.3 20,773 1

Seq Scan on projectbillingrate pbr_1 (cost=0.00..537.61 rows=15,530 width=40) (actual time=0.013..7.952 rows=20,773 loops=1)

  • Filter: ((billingrateid = $0) OR ((billingrateid <> $1) AND (userid IS NULL)))
86. 7.097 13.486 ↑ 1.0 20,769 1

Hash (cost=414.12..414.12 rows=21,012 width=24) (actual time=13.486..13.486 rows=20,769 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1392kB
87. 6.389 6.389 ↑ 1.0 20,769 1

Seq Scan on projectbillingratehistory pbrh_1 (cost=0.00..414.12 rows=21,012 width=24) (actual time=0.004..6.389 rows=20,769 loops=1)

88. 33.058 63.412 ↓ 1.0 90,654 1

Hash (cost=1,882.48..1,882.48 rows=90,648 width=28) (actual time=63.412..63.412 rows=90,654 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6336kB
89. 30.354 30.354 ↓ 1.0 90,654 1

Seq Scan on timesheet t (cost=0.00..1,882.48 rows=90,648 width=28) (actual time=0.003..30.354 rows=90,654 loops=1)

90. 0.000 0.001 ↓ 0.0 0 1

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

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

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

92. 73.440 477.360 ↑ 1.0 1 36,720

Nested Loop (cost=0.83..1.03 rows=1 width=20) (actual time=0.012..0.013 rows=1 loops=36,720)

93. 183.600 183.600 ↑ 1.0 1 36,720

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.56 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=36,720)

  • Index Cond: (timeexpensecostbilling2_facts0.timesheetid = id)
94. 220.320 220.320 ↑ 1.0 1 36,720

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.46 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=36,720)

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 12544
95.          

Initplan (forSort)

96. 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)

97. 18.707 5,235,259.563 ↓ 17.2 11,774 1

Nested Loop Left Join (cost=27.16..2,462.15 rows=685 width=161) (actual time=5,234,975.120..5,235,259.563 rows=11,774 loops=1)

98. 47.900 5,235,193.760 ↓ 17.2 11,774 1

Nested Loop Left Join (cost=26.74..2,126.95 rows=685 width=116) (actual time=5,234,975.107..5,235,193.760 rows=11,774 loops=1)

  • Join Filter: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Rows Removed by Join Filter: 82418
99. 21.166 5,235,122.312 ↓ 17.2 11,774 1

Nested Loop Left Join (cost=26.59..2,114.50 rows=685 width=120) (actual time=5,234,974.618..5,235,122.312 rows=11,774 loops=1)

100. 12.599 5,235,065.824 ↓ 17.2 11,774 1

Hash Left Join (cost=26.17..806.97 rows=685 width=116) (actual time=5,234,974.598..5,235,065.824 rows=11,774 loops=1)

  • Hash Cond: (pj.programid = program.id)
101. 20.854 5,235,052.749 ↓ 17.2 11,774 1

Nested Loop Left Join (cost=0.29..771.76 rows=685 width=99) (actual time=5,234,974.099..5,235,052.749 rows=11,774 loops=1)

102. 5,234,996.573 5,234,996.573 ↓ 17.2 11,774 1

CTE Scan on eexpensecostbilling2_facts0cte imeexpensecostbilling2_facts10 (cost=0.00..13.70 rows=685 width=32) (actual time=5,234,974.092..5,234,996.573 rows=11,774 loops=1)

103. 35.322 35.322 ↑ 1.0 1 11,774

Index Scan using project_pkey on project pj (cost=0.29..1.10 rows=1 width=71) (actual time=0.003..0.003 rows=1 loops=11,774)

  • Index Cond: (imeexpensecostbilling2_facts10.projectid3 = id)
104. 0.246 0.476 ↓ 1.0 738 1

Hash (cost=17.06..17.06 rows=706 width=33) (actual time=0.476..0.476 rows=738 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
105. 0.230 0.230 ↓ 1.0 738 1

Seq Scan on program (cost=0.00..17.06 rows=706 width=33) (actual time=0.003..0.230 rows=738 loops=1)

106. 35.322 35.322 ↑ 1.0 1 11,774

Index Scan using task_pkey on task tk (cost=0.42..1.90 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11,774)

  • Index Cond: (imeexpensecostbilling2_facts10.taskid4 = id)
107. 23.072 23.548 ↓ 7.0 7 11,774

Materialize (cost=0.14..2.18 rows=1 width=4) (actual time=0.000..0.002 rows=7 loops=11,774)

108. 0.476 0.476 ↓ 7.0 7 1

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..2.17 rows=1 width=4) (actual time=0.471..0.476 rows=7 loops=1)

  • Index Cond: ((variablecurrencyid = $12) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
109. 47.096 47.096 ↑ 1.0 1 11,774

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.42..0.48 rows=1 width=53) (actual time=0.003..0.004 rows=1 loops=11,774)

  • Index Cond: (tk.id = taskid)