explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QVWj

Settings
# exclusive inclusive rows x rows loops node
1. 0.324 6,747.070 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.55..56,497.19 rows=3 width=1,582) (actual time=2,345.691..6,747.070 rows=3 loops=1)

  • Join Filter: (exchangerate8.fixedcurrencyid = project0.fixedbidcurrencyid)
  • Rows Removed by Join Filter: 21
2. 0.021 0.406 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.55..62.12 rows=3 width=1,254) (actual time=0.256..0.406 rows=3 loops=1)

3. 0.090 0.364 ↑ 1.0 3 1

Nested Loop (cost=0.28..37.22 rows=3 width=1,244) (actual time=0.242..0.364 rows=3 loops=1)

  • Join Filter: (project0.projectstatuslabelid = projectstatuslabel1.id)
  • Rows Removed by Join Filter: 18
4. 0.022 0.022 ↑ 1.0 7 1

Seq Scan on projectstatuslabel projectstatuslabel1 (cost=0.00..1.07 rows=7 width=20) (actual time=0.012..0.022 rows=7 loops=1)

5. 0.057 0.252 ↑ 1.0 3 7

Materialize (cost=0.28..35.84 rows=3 width=1,256) (actual time=0.016..0.036 rows=3 loops=7)

6. 0.056 0.195 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.28..35.83 rows=3 width=1,256) (actual time=0.096..0.195 rows=3 loops=1)

  • Join Filter: (program5.id = project0.programid)
  • Rows Removed by Join Filter: 20
7. 0.022 0.079 ↑ 1.0 3 1

Nested Loop Left Join (cost=0.28..34.25 rows=3 width=224) (actual time=0.041..0.079 rows=3 loops=1)

8. 0.036 0.036 ↑ 1.0 3 1

Seq Scan on project project0 (cost=0.00..9.36 rows=3 width=183) (actual time=0.016..0.036 rows=3 loops=1)

  • Filter: (id = ANY ('{21,48,23}'::integer[]))
  • Rows Removed by Filter: 96
9. 0.021 0.021 ↑ 1.0 1 3

Index Scan using userinfo_pkey on userinfo userinfo6 (cost=0.28..8.29 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=3)

  • Index Cond: (id = project0.projectleaderapproverid)
10. 0.043 0.060 ↑ 1.2 8 3

Materialize (cost=0.00..1.15 rows=10 width=1,048) (actual time=0.005..0.020 rows=8 loops=3)

11. 0.017 0.017 ↑ 1.0 10 1

Seq Scan on program program5 (cost=0.00..1.10 rows=10 width=1,048) (actual time=0.005..0.017 rows=10 loops=1)

12. 0.021 0.021 ↑ 1.0 1 3

Index Scan using login_pkey on login login7 (cost=0.28..8.29 rows=1 width=18) (actual time=0.006..0.007 rows=1 loops=3)

  • Index Cond: (userid = project0.projectleaderapproverid)
13. 0.034 0.057 ↓ 7.0 7 3

Materialize (cost=0.00..1.86 rows=1 width=24) (actual time=0.004..0.019 rows=7 loops=3)

14. 0.023 0.023 ↓ 7.0 7 1

Seq Scan on exchangerate exchangerate8 (cost=0.00..1.86 rows=1 width=24) (actual time=0.005..0.023 rows=7 loops=1)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
  • Rows Removed by Filter: 42
15.          

SubPlan (for Nested Loop Left Join)

16. 4.878 754.083 ↑ 1.0 1 3

Aggregate (cost=5,011.19..5,011.20 rows=1 width=32) (actual time=251.360..251.361 rows=1 loops=3)

17. 7.913 749.205 ↓ 1.0 1,031 3

Hash Left Join (cost=2,301.83..5,006.27 rows=984 width=25) (actual time=137.220..249.735 rows=1,031 loops=3)

  • Hash Cond: (billingitem22.currencyid = exchangerate3.fixedcurrencyid)
  • Join Filter: ((exchangerate3.effectivedate <= billingitem22.entrydate) AND (exchangerate3.enddate >= billingitem22.entrydate))
18. 363.996 741.258 ↓ 1.0 1,031 3

Hash Join (cost=2,300.20..5,000.65 rows=984 width=13) (actual time=137.197..247.086 rows=1,031 loops=3)

  • Hash Cond: (billingitem22.id = lingitem2denormalizedmetadata4.billingitemid)
19. 365.766 365.766 ↓ 1.0 98,560 3

Seq Scan on billingitem2 billingitem22 (cost=0.00..2,441.90 rows=98,490 width=29) (actual time=0.004..121.922 rows=98,560 loops=3)

  • Filter: isbillable
20. 4.008 11.496 ↓ 1.0 1,031 3

Hash (cost=2,287.90..2,287.90 rows=984 width=16) (actual time=3.832..3.832 rows=1,031 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
21. 7.329 7.488 ↓ 1.0 1,031 3

Bitmap Heap Scan on billingitem2denormalizedmetadata lingitem2denormalizedmetadata4 (cost=40.08..2,287.90 rows=984 width=16) (actual time=0.068..2.496 rows=1,031 loops=3)

  • Recheck Cond: (project0.id = projectid)
  • Filter: ((billingtypeuri IS NULL) OR (upper(billingtypeuri) <> 'URN:REPLICON:BILLING-ITEM-TYPE:EXPENSE'::text))
  • Heap Blocks: exact=85
22. 0.159 0.159 ↓ 1.0 1,031 3

Bitmap Index Scan on ixbi2dmprojectid (cost=0.00..39.83 rows=989 width=0) (actual time=0.053..0.053 rows=1,031 loops=3)

  • Index Cond: (project0.id = projectid)
23. 0.013 0.034 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=32) (actual time=0.033..0.034 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.021 0.021 ↓ 7.0 7 1

Seq Scan on exchangerate exchangerate3 (cost=0.00..1.61 rows=1 width=32) (actual time=0.007..0.021 rows=7 loops=1)

  • Filter: (variablecurrencyid = 1)
  • Rows Removed by Filter: 42
25. 0.024 0.183 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.060..0.061 rows=1 loops=3)

26. 0.051 0.159 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.049..0.053 rows=1 loops=3)

  • Join Filter: (exchangerate10.fixedcurrencyid = project9.estimatedcostcurrencyid)
  • Rows Removed by Join Filter: 7
27. 0.042 0.042 ↑ 1.0 1 3

Index Scan using project_pkey on project project9 (cost=0.14..8.16 rows=1 width=28) (actual time=0.013..0.014 rows=1 loops=3)

  • Index Cond: (id = project0.id)
28. 0.066 0.066 ↓ 7.0 7 3

Seq Scan on exchangerate exchangerate10 (cost=0.00..1.86 rows=1 width=24) (actual time=0.005..0.022 rows=7 loops=3)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
  • Rows Removed by Filter: 42
29. 0.021 0.135 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.043..0.045 rows=1 loops=3)

30. 0.039 0.114 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.035..0.038 rows=1 loops=3)

  • Join Filter: (exchangerate10_1.fixedcurrencyid = project9_1.estimatedcostcurrencyid)
  • Rows Removed by Join Filter: 7
31. 0.015 0.015 ↑ 1.0 1 3

Index Scan using project_pkey on project project9_1 (cost=0.14..8.16 rows=1 width=28) (actual time=0.003..0.005 rows=1 loops=3)

  • Index Cond: (id = project0.id)
32. 0.060 0.060 ↓ 7.0 7 3

Seq Scan on exchangerate exchangerate10_1 (cost=0.00..1.86 rows=1 width=24) (actual time=0.003..0.020 rows=7 loops=3)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
  • Rows Removed by Filter: 42
33. 0.021 0.135 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.043..0.045 rows=1 loops=3)

34. 0.039 0.114 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.035..0.038 rows=1 loops=3)

  • Join Filter: (exchangerate10_2.fixedcurrencyid = project9_2.estimatedcostcurrencyid)
  • Rows Removed by Join Filter: 7
35. 0.015 0.015 ↑ 1.0 1 3

Index Scan using project_pkey on project project9_2 (cost=0.14..8.16 rows=1 width=28) (actual time=0.003..0.005 rows=1 loops=3)

  • Index Cond: (id = project0.id)
36. 0.060 0.060 ↓ 7.0 7 3

Seq Scan on exchangerate exchangerate10_2 (cost=0.00..1.86 rows=1 width=24) (actual time=0.003..0.020 rows=7 loops=3)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
  • Rows Removed by Filter: 42
37. 0.066 1.119 ↑ 1.0 1 3

Aggregate (cost=35.52..35.53 rows=1 width=32) (actual time=0.372..0.373 rows=1 loops=3)

38. 0.429 1.053 ↓ 1.3 13 3

Nested Loop Left Join (cost=4.50..35.44 rows=10 width=30) (actual time=0.033..0.351 rows=13 loops=3)

  • Join Filter: (exchangerate13.fixedcurrencyid = task12.estimatedcostcurrencyid)
  • Rows Removed by Join Filter: 78
39. 0.108 0.234 ↓ 1.3 13 3

Nested Loop (cost=4.50..33.43 rows=10 width=14) (actual time=0.024..0.078 rows=13 loops=3)

40. 0.015 0.015 ↑ 1.0 1 3

Index Scan using project_pkey on project project11 (cost=0.14..8.16 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=3)

  • Index Cond: (id = project0.id)
41. 0.084 0.111 ↓ 1.3 13 3

Bitmap Heap Scan on task task12 (cost=4.35..25.17 rows=10 width=14) (actual time=0.014..0.037 rows=13 loops=3)

  • Recheck Cond: (projectid = project0.id)
  • Heap Blocks: exact=26
42. 0.027 0.027 ↓ 1.3 13 3

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (actual time=0.008..0.009 rows=13 loops=3)

  • Index Cond: (projectid = project0.id)
43. 0.369 0.390 ↓ 7.0 7 39

Materialize (cost=0.00..1.86 rows=1 width=24) (actual time=0.001..0.010 rows=7 loops=39)

44. 0.021 0.021 ↓ 7.0 7 1

Seq Scan on exchangerate exchangerate13 (cost=0.00..1.86 rows=1 width=24) (actual time=0.004..0.021 rows=7 loops=1)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
  • Rows Removed by Filter: 42
45. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=35.52..35.53 rows=1 width=32) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.50..35.44 rows=10 width=30) (never executed)

  • Join Filter: (exchangerate13_1.fixedcurrencyid = task12_1.estimatedcostcurrencyid)
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.50..33.43 rows=10 width=14) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project11_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
49. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on task task12_1 (cost=4.35..25.17 rows=10 width=14) (never executed)

  • Recheck Cond: (projectid = project0.id)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (never executed)

  • Index Cond: (projectid = project0.id)
51. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.86 rows=1 width=24) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate13_1 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
53. 0.012 0.033 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.010..0.011 rows=1 loops=3)

54. 0.006 0.021 ↓ 0.0 0 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.007..0.007 rows=0 loops=3)

  • Join Filter: (exchangerate15.fixedcurrencyid = project14.estimatedcostcurrencyid)
55. 0.015 0.015 ↓ 0.0 0 3

Index Scan using project_pkey on project project14 (cost=0.14..8.16 rows=1 width=28) (actual time=0.004..0.005 rows=0 loops=3)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 0)
  • Rows Removed by Filter: 1
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate15 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
57. 0.012 0.030 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=3)

58. 0.006 0.018 ↓ 0.0 0 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=3)

  • Join Filter: (exchangerate15_1.fixedcurrencyid = project14_1.estimatedcostcurrencyid)
59. 0.012 0.012 ↓ 0.0 0 3

Index Scan using project_pkey on project project14_1 (cost=0.14..8.16 rows=1 width=28) (actual time=0.003..0.004 rows=0 loops=3)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 0)
  • Rows Removed by Filter: 1
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate15_1 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
61. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=10.04..10.05 rows=1 width=32) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (never executed)

  • Join Filter: (exchangerate15_2.fixedcurrencyid = project14_2.estimatedcostcurrencyid)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project14_2 (cost=0.14..8.16 rows=1 width=28) (never executed)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 0)
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate15_2 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
65. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=35.24..35.25 rows=1 width=32) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.49..35.23 rows=1 width=30) (never executed)

  • Join Filter: (exchangerate18.fixedcurrencyid = task17.estimatedcostcurrencyid)
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.49..33.36 rows=1 width=14) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on task task17 (cost=4.35..25.19 rows=1 width=14) (never executed)

  • Recheck Cond: (projectid = project0.id)
  • Filter: (costtype = 0)
69. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (never executed)

  • Index Cond: (projectid = project0.id)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project16 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate18 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
72. 0.012 0.081 ↑ 1.0 1 3

Aggregate (cost=35.24..35.25 rows=1 width=32) (actual time=0.026..0.027 rows=1 loops=3)

73. 0.006 0.069 ↓ 0.0 0 3

Nested Loop Left Join (cost=4.49..35.23 rows=1 width=30) (actual time=0.023..0.023 rows=0 loops=3)

  • Join Filter: (exchangerate18_1.fixedcurrencyid = task17_1.estimatedcostcurrencyid)
74. 0.012 0.063 ↓ 0.0 0 3

Nested Loop (cost=4.49..33.36 rows=1 width=14) (actual time=0.020..0.021 rows=0 loops=3)

75. 0.033 0.051 ↓ 0.0 0 3

Bitmap Heap Scan on task task17_1 (cost=4.35..25.19 rows=1 width=14) (actual time=0.016..0.017 rows=0 loops=3)

  • Recheck Cond: (projectid = project0.id)
  • Filter: (costtype = 0)
  • Rows Removed by Filter: 13
  • Heap Blocks: exact=26
76. 0.018 0.018 ↓ 1.3 13 3

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (actual time=0.005..0.006 rows=13 loops=3)

  • Index Cond: (projectid = project0.id)
77. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project16_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
78. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate18_1 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
79. 0.012 0.030 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=3)

80. 0.006 0.018 ↓ 0.0 0 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=3)

  • Join Filter: (exchangerate20.fixedcurrencyid = project19.estimatedcostcurrencyid)
81. 0.012 0.012 ↓ 0.0 0 3

Index Scan using project_pkey on project project19 (cost=0.14..8.16 rows=1 width=28) (actual time=0.003..0.004 rows=0 loops=3)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 1)
  • Rows Removed by Filter: 1
82. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate20 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
83. 0.009 0.042 ↑ 1.0 1 3

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=3)

84. 0.009 0.033 ↓ 0.0 0 3

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (actual time=0.010..0.011 rows=0 loops=3)

  • Join Filter: (exchangerate20_1.fixedcurrencyid = project19_1.estimatedcostcurrencyid)
85. 0.024 0.024 ↓ 0.0 0 3

Index Scan using project_pkey on project project19_1 (cost=0.14..8.16 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=3)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 1)
  • Rows Removed by Filter: 1
86. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate20_1 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
87. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=10.04..10.05 rows=1 width=32) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.14..10.03 rows=1 width=44) (never executed)

  • Join Filter: (exchangerate20_2.fixedcurrencyid = project19_2.estimatedcostcurrencyid)
89. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project19_2 (cost=0.14..8.16 rows=1 width=28) (never executed)

  • Index Cond: (id = project0.id)
  • Filter: (costtype = 1)
90. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate20_2 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
91. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=35.24..35.25 rows=1 width=32) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.49..35.23 rows=1 width=30) (never executed)

  • Join Filter: (exchangerate23.fixedcurrencyid = task22.estimatedcostcurrencyid)
93. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.49..33.36 rows=1 width=14) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on task task22 (cost=4.35..25.19 rows=1 width=14) (never executed)

  • Recheck Cond: (projectid = project0.id)
  • Filter: (costtype = 1)
95. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (never executed)

  • Index Cond: (projectid = project0.id)
96. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project21 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate23 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
98. 0.009 0.096 ↑ 1.0 1 3

Aggregate (cost=35.24..35.25 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=3)

99. 0.009 0.087 ↓ 0.0 0 3

Nested Loop Left Join (cost=4.49..35.23 rows=1 width=30) (actual time=0.028..0.029 rows=0 loops=3)

  • Join Filter: (exchangerate23_1.fixedcurrencyid = task22_1.estimatedcostcurrencyid)
100. 0.009 0.078 ↓ 0.0 0 3

Nested Loop (cost=4.49..33.36 rows=1 width=14) (actual time=0.026..0.026 rows=0 loops=3)

101. 0.033 0.069 ↓ 0.0 0 3

Bitmap Heap Scan on task task22_1 (cost=4.35..25.19 rows=1 width=14) (actual time=0.022..0.023 rows=0 loops=3)

  • Recheck Cond: (projectid = project0.id)
  • Filter: (costtype = 1)
  • Rows Removed by Filter: 13
  • Heap Blocks: exact=26
102. 0.036 0.036 ↓ 1.3 13 3

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (actual time=0.012..0.012 rows=13 loops=3)

  • Index Cond: (projectid = project0.id)
103. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project21_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
104. 0.000 0.000 ↓ 0.0 0

Seq Scan on exchangerate exchangerate23_1 (cost=0.00..1.86 rows=1 width=24) (never executed)

  • Filter: ((effectivedate <= '2020-05-04'::date) AND (enddate >= '2020-05-04'::date) AND (variablecurrencyid = 1))
105. 0.021 0.042 ↑ 1.0 1 3

Aggregate (cost=8.17..8.18 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=3)

106. 0.021 0.021 ↑ 1.0 1 3

Index Scan using project_pkey on project project24 (cost=0.14..8.16 rows=1 width=20) (actual time=0.006..0.007 rows=1 loops=3)

  • Index Cond: (id = project0.id)
107. 0.021 0.033 ↑ 1.0 1 3

Aggregate (cost=8.17..8.18 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=3)

108. 0.012 0.012 ↑ 1.0 1 3

Index Scan using project_pkey on project project24_1 (cost=0.14..8.16 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=3)

  • Index Cond: (id = project0.id)
109. 0.021 0.033 ↑ 1.0 1 3

Aggregate (cost=8.17..8.18 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=3)

110. 0.012 0.012 ↑ 1.0 1 3

Index Scan using project_pkey on project project24_2 (cost=0.14..8.16 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=3)

  • Index Cond: (id = project0.id)
111. 0.066 0.261 ↑ 1.0 1 3

Aggregate (cost=33.48..33.49 rows=1 width=16) (actual time=0.086..0.087 rows=1 loops=3)

112. 0.099 0.195 ↓ 1.3 13 3

Nested Loop (cost=4.50..33.43 rows=10 width=20) (actual time=0.017..0.065 rows=13 loops=3)

113. 0.015 0.015 ↑ 1.0 1 3

Index Scan using project_pkey on project project25 (cost=0.14..8.16 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=3)

  • Index Cond: (id = project0.id)
114. 0.066 0.081 ↓ 1.3 13 3

Bitmap Heap Scan on task task26 (cost=4.35..25.17 rows=10 width=20) (actual time=0.008..0.027 rows=13 loops=3)

  • Recheck Cond: (projectid = project0.id)
  • Heap Blocks: exact=26
115. 0.015 0.015 ↓ 1.3 13 3

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (actual time=0.004..0.005 rows=13 loops=3)

  • Index Cond: (projectid = project0.id)
116. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=33.48..33.49 rows=1 width=16) (never executed)

117. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.50..33.43 rows=10 width=20) (never executed)

118. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project project25_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (id = project0.id)
119. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on task task26_1 (cost=4.35..25.17 rows=10 width=20) (never executed)

  • Recheck Cond: (projectid = project0.id)
120. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on uix3tk_projectparentname (cost=0.00..4.35 rows=10 width=0) (never executed)

  • Index Cond: (projectid = project0.id)
121. 0.066 0.120 ↑ 1.0 1 3

Aggregate (cost=9.68..9.69 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops=3)

122. 0.036 0.054 ↑ 1.2 5 3

Bitmap Heap Scan on projectteammember projectteammember27 (cost=4.32..9.66 rows=6 width=4) (actual time=0.010..0.018 rows=5 loops=3)

  • Recheck Cond: (projectid = project0.id)
  • Filter: (userid IS NOT NULL)
  • Heap Blocks: exact=10
123. 0.018 0.018 ↑ 1.2 5 3

Bitmap Index Scan on uix3ptm_projectrole (cost=0.00..4.32 rows=6 width=0) (actual time=0.005..0.006 rows=5 loops=3)

  • Index Cond: (projectid = project0.id)
124. 0.012 0.024 ↑ 1.0 1 3

Aggregate (cost=8.29..8.30 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=3)

125. 0.012 0.012 ↓ 0.0 0 3

Index Only Scan using uix3ptm_projectrole on projectteammember projectteammember28 (cost=0.28..8.29 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=3)

  • Index Cond: ((projectid = project0.id) AND (placeholderprojectroleid IS NOT NULL))
  • Heap Fetches: 0
126. 4.743 767.031 ↑ 1.0 1 3

Aggregate (cost=5,011.19..5,011.20 rows=1 width=32) (actual time=255.676..255.677 rows=1 loops=3)

127. 8.358 762.288 ↓ 1.0 1,031 3

Hash Left Join (cost=2,301.83..5,006.27 rows=984 width=25) (actual time=140.446..254.096 rows=1,031 loops=3)

  • Hash Cond: (billingitem22_1.currencyid = exchangerate3_1.fixedcurrencyid)
  • Join Filter: ((exchangerate3_1.effectivedate <= billingitem22_1.entrydate) AND (exchangerate3_1.enddate >= billingitem22_1.entrydate))
128. 368.616 753.816 ↓ 1.0 1,031 3

Hash Join (cost=2,300.20..5,000.65 rows=984 width=13) (actual time=140.367..251.272 rows=1,031 loops=3)

  • Hash Cond: (billingitem22_1.id = lingitem2denormalizedmetadata4_1.billingitemid)
129. 373.887 373.887 ↓ 1.0 98,560 3

Seq Scan on billingitem2 billingitem22_1 (cost=0.00..2,441.90 rows=98,490 width=29) (actual time=0.004..124.629 rows=98,560 loops=3)

  • Filter: isbillable
130. 3.795 11.313 ↓ 1.0 1,031 3

Hash (cost=2,287.90..2,287.90 rows=984 width=16) (actual time=3.770..3.771 rows=1,031 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
131. 7.299 7.518 ↓ 1.0 1,031 3

Bitmap Heap Scan on billingitem2denormalizedmetadata lingitem2denormalizedmetadata4_1 (cost=40.08..2,287.90 rows=984 width=16) (actual time=0.091..2.506 rows=1,031 loops=3)

  • Recheck Cond: (project0.id = projectid)
  • Filter: ((billingtypeuri IS NULL) OR (upper(billingtypeuri) <> 'URN:REPLICON:BILLING-ITEM-TYPE:EXPENSE'::text))
  • Heap Blocks: exact=85
132. 0.219 0.219 ↓ 1.0 1,031 3

Bitmap Index Scan on ixbi2dmprojectid (cost=0.00..39.83 rows=989 width=0) (actual time=0.073..0.073 rows=1,031 loops=3)

  • Index Cond: (project0.id = projectid)
133. 0.090 0.114 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=32) (actual time=0.113..0.114 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
134. 0.024 0.024 ↓ 7.0 7 1

Seq Scan on exchangerate exchangerate3_1 (cost=0.00..1.61 rows=1 width=32) (actual time=0.009..0.024 rows=7 loops=1)

  • Filter: (variablecurrencyid = 1)
  • Rows Removed by Filter: 42
135. 0.015 10.965 ↑ 1.0 1 3

Aggregate (cost=1,241.74..1,241.75 rows=1 width=32) (actual time=3.654..3.655 rows=1 loops=3)

136. 0.009 10.950 ↓ 0.0 0 3

Nested Loop Left Join (cost=68.87..1,241.72 rows=1 width=40) (actual time=3.650..3.650 rows=0 loops=3)

  • Join Filter: ((exchangerate30.effectivedate <= _projecttimeallocation_facts31.entrydate) AND (exchangerate30.enddate >= _projecttimeallocation_facts31.entrydate))
137. 0.006 10.941 ↓ 0.0 0 3

Nested Loop (cost=68.73..1,241.54 rows=1 width=28) (actual time=3.647..3.647 rows=0 loops=3)

138. 4.427 10.935 ↓ 0.0 0 3

Hash Left Join (cost=68.45..1,241.07 rows=1 width=24) (actual time=3.644..3.645 rows=0 loops=3)

  • Hash Cond: (_projecttimeallocation_facts31.taskid = task32.id)
  • Filter: (((task32.id IS NOT NULL) AND (task32.costtype = 1)) OR ((task32.id IS NULL) AND (project0.costtype = 1)))
  • Rows Removed by Filter: 1004
139. 3.870 4.044 ↓ 1.4 1,004 3

Bitmap Heap Scan on dm_projecttimeallocation_facts _projecttimeallocation_facts31 (cost=17.95..1,188.64 rows=730 width=28) (actual time=0.077..1.348 rows=1,004 loops=3)

  • Recheck Cond: (project0.id = projectid)
  • Heap Blocks: exact=375
140. 0.174 0.174 ↓ 1.4 1,004 3

Bitmap Index Scan on ixpta2projectid (cost=0.00..17.77 rows=730 width=0) (actual time=0.057..0.058 rows=1,004 loops=3)

  • Index Cond: (project0.id = projectid)
141. 1.194 2.464 ↑ 1.0 1,000 1

Hash (cost=38.00..38.00 rows=1,000 width=8) (actual time=2.463..2.464 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
142. 1.270 1.270 ↑ 1.0 1,000 1

Seq Scan on task task32 (cost=0.00..38.00 rows=1,000 width=8) (actual time=0.006..1.270 rows=1,000 loops=1)

143. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuprrhuserid on userprojectroleratehistory userprojectroleratehistory29 (cost=0.28..0.46 rows=1 width=20) (never executed)

  • Index Cond: (userid = _projecttimeallocation_facts31.userid)
  • Filter: ((effectivedate <= _projecttimeallocation_facts31.entrydate) AND (enddate >= _projecttimeallocation_facts31.entrydate))
144. 0.000 0.000 ↓ 0.0 0

Index Scan using ixprj11fixedcurrencyid on exchangerate exchangerate30 (cost=0.14..0.17 rows=1 width=32) (never executed)

  • Index Cond: (fixedcurrencyid = userprojectroleratehistory29.currencyid)
  • Filter: (variablecurrencyid = 1)
145. 0.027 10.980 ↑ 1.0 1 3

Aggregate (cost=1,241.74..1,241.75 rows=1 width=32) (actual time=3.654..3.660 rows=1 loops=3)

146. 0.009 10.953 ↓ 0.0 0 3

Nested Loop Left Join (cost=68.87..1,241.72 rows=1 width=40) (actual time=3.650..3.651 rows=0 loops=3)

  • Join Filter: ((exchangerate34.effectivedate <= _projecttimeallocation_facts35.entrydate) AND (exchangerate34.enddate >= _projecttimeallocation_facts35.entrydate))
147. 0.009 10.944 ↓ 0.0 0 3

Nested Loop (cost=68.73..1,241.54 rows=1 width=28) (actual time=3.648..3.648 rows=0 loops=3)

148. 4.525 10.935 ↓ 0.0 0 3

Hash Left Join (cost=68.45..1,241.07 rows=1 width=24) (actual time=3.645..3.645 rows=0 loops=3)

  • Hash Cond: (_projecttimeallocation_facts35.taskid = task36.id)
  • Filter: (((task36.id IS NOT NULL) AND (task36.costtype = 0)) OR ((task36.id IS NULL) AND (project0.costtype = 0)))
  • Rows Removed by Filter: 1004
149. 3.813 4.002 ↓ 1.4 1,004 3

Bitmap Heap Scan on dm_projecttimeallocation_facts _projecttimeallocation_facts35 (cost=17.95..1,188.64 rows=730 width=28) (actual time=0.083..1.334 rows=1,004 loops=3)

  • Recheck Cond: (project0.id = projectid)
  • Heap Blocks: exact=375
150. 0.189 0.189 ↓ 1.4 1,004 3

Bitmap Index Scan on ixpta2projectid (cost=0.00..17.77 rows=730 width=0) (actual time=0.062..0.063 rows=1,004 loops=3)

  • Index Cond: (project0.id = projectid)
151. 1.183 2.408 ↑ 1.0 1,000 1

Hash (cost=38.00..38.00 rows=1,000 width=8) (actual time=2.407..2.408 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
152. 1.225 1.225 ↑ 1.0 1,000 1

Seq Scan on task task36 (cost=0.00..38.00 rows=1,000 width=8) (actual time=0.005..1.225 rows=1,000 loops=1)

153. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuprrhuserid on userprojectroleratehistory userprojectroleratehistory33 (cost=0.28..0.46 rows=1 width=20) (never executed)

  • Index Cond: (userid = _projecttimeallocation_facts35.userid)
  • Filter: ((effectivedate <= _projecttimeallocation_facts35.entrydate) AND (enddate >= _projecttimeallocation_facts35.entrydate))
154. 0.000 0.000 ↓ 0.0 0

Index Scan using ixprj11fixedcurrencyid on exchangerate exchangerate34 (cost=0.14..0.17 rows=1 width=32) (never executed)

  • Index Cond: (fixedcurrencyid = userprojectroleratehistory33.currencyid)
  • Filter: (variablecurrencyid = 1)
155. 8.748 89.283 ↑ 1.0 1 3

Aggregate (cost=1,283.94..1,283.95 rows=1 width=32) (actual time=29.759..29.761 rows=1 loops=3)

156. 35.286 80.535 ↓ 6.2 1,004 3

Nested Loop Left Join (cost=80.42..1,281.50 rows=162 width=40) (actual time=1.700..26.845 rows=1,004 loops=3)

  • Join Filter: ((exchangerate38.effectivedate <= _projecttimeallocation_facts39.entrydate) AND (exchangerate38.enddate >= _projecttimeallocation_facts39.entrydate) AND (exchangerate38.fixedcurrencyid = userprojectroleratehistory37.currencyid) (...)
  • Rows Removed by Join Filter: 6024
157. 9.158 18.141 ↓ 6.2 1,004 3

Hash Join (cost=80.42..1,276.65 rows=162 width=28) (actual time=1.691..6.047 rows=1,004 loops=3)

  • Hash Cond: (_projecttimeallocation_facts39.userid = userprojectroleratehistory37.userid)
  • Join Filter: ((userprojectroleratehistory37.effectivedate <= _projecttimeallocation_facts39.entrydate) AND (userprojectroleratehistory37.enddate >= _projecttimeallocation_facts39.entrydate))
  • Rows Removed by Join Filter: 1004
158. 3.984 4.137 ↓ 1.4 1,004 3

Bitmap Heap Scan on dm_projecttimeallocation_facts _projecttimeallocation_facts39 (cost=17.95..1,188.64 rows=730 width=24) (actual time=0.067..1.379 rows=1,004 loops=3)

  • Recheck Cond: (project0.id = projectid)
  • Heap Blocks: exact=375
159. 0.153 0.153 ↓ 1.4 1,004 3

Bitmap Index Scan on ixpta2projectid (cost=0.00..17.77 rows=730 width=0) (actual time=0.050..0.051 rows=1,004 loops=3)

  • Index Cond: (project0.id = projectid)
160. 2.388 4.846 ↑ 1.0 2,021 1

Hash (cost=37.21..37.21 rows=2,021 width=20) (actual time=4.845..4.846 rows=2,021 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 127kB
161. 2.458 2.458 ↑ 1.0 2,021 1

Seq Scan on userprojectroleratehistory userprojectroleratehistory37 (cost=0.00..37.21 rows=2,021 width=20) (actual time=0.007..2.458 rows=2,021 loops=1)

162. 27.089 27.108 ↓ 7.0 7 3,012

Materialize (cost=0.00..1.62 rows=1 width=32) (actual time=0.001..0.009 rows=7 loops=3,012)

163. 0.019 0.019 ↓ 7.0 7 1

Seq Scan on exchangerate exchangerate38 (cost=0.00..1.61 rows=1 width=32) (actual time=0.005..0.019 rows=7 loops=1)

  • Filter: (variablecurrencyid = 1)
  • Rows Removed by Filter: 42
164. 5.358 5,017.695 ↑ 1.0 1 3

Aggregate (cost=3,122.23..3,122.24 rows=1 width=32) (actual time=1,672.564..1,672.565 rows=1 loops=3)

165. 16.596 5,012.337 ↓ 1,017.0 1,017 3

Nested Loop Left Join (cost=421.37..3,122.22 rows=1 width=25) (actual time=863.105..1,670.779 rows=1,017 loops=3)

166. 720.528 4,977.429 ↓ 1,017.0 1,017 3

Nested Loop (cost=421.23..3,122.05 rows=1 width=13) (actual time=863.091..1,659.143 rows=1,017 loops=3)

167. 1,058.196 3,232.257 ↓ 85,387.0 85,387 3

Nested Loop (cost=420.81..3,121.42 rows=1 width=45) (actual time=12.704..1,077.419 rows=85,387 loops=3)

168. 727.955 1,149.417 ↓ 85,387.0 85,387 3

Hash Left Join (cost=420.39..3,120.84 rows=1 width=29) (actual time=12.688..383.139 rows=85,387 loops=3)

  • Hash Cond: (billingitem240.id = invoiceitembillingitems44.billingitemid)
  • Filter: (invoiceitembillingitems44.id IS NULL)
  • Rows Removed by Filter: 13173
169. 383.490 383.490 ↓ 1.0 98,560 3

Seq Scan on billingitem2 billingitem240 (cost=0.00..2,441.90 rows=98,490 width=29) (actual time=0.005..127.830 rows=98,560 loops=3)

  • Filter: isbillable
170. 19.557 37.972 ↑ 1.0 13,173 1

Hash (cost=255.73..255.73 rows=13,173 width=32) (actual time=37.971..37.972 rows=13,173 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 952kB
171. 18.415 18.415 ↑ 1.0 13,173 1

Seq Scan on invoiceitembillingitems invoiceitembillingitems44 (cost=0.00..255.73 rows=13,173 width=32) (actual time=0.007..18.415 rows=13,173 loops=1)

172. 1,024.644 1,024.644 ↑ 1.0 1 256,161

Index Scan using uixbii on billingitem2invoiceable billingitem2invoiceable43 (cost=0.42..0.58 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=256,161)

  • Index Cond: (billingitemid = billingitem240.id)
  • Filter: invoiceable
173. 1,024.644 1,024.644 ↓ 0.0 0 256,161

Index Scan using uixbi2dm on billingitem2denormalizedmetadata ingitem2denormalizedmetadata42 (cost=0.42..0.62 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=256,161)

  • Index Cond: (billingitemid = billingitem240.id)
  • Filter: (project0.id = projectid)
  • Rows Removed by Filter: 1
174. 18.312 18.312 ↑ 1.0 1 3,052

Index Scan using ixprj11fixedcurrencyid on exchangerate exchangerate41 (cost=0.14..0.17 rows=1 width=32) (actual time=0.003..0.006 rows=1 loops=3,052)

  • Index Cond: (fixedcurrencyid = billingitem240.currencyid)
  • Filter: ((variablecurrencyid = 1) AND (effectivedate <= billingitem240.entrydate) AND (enddate >= billingitem240.entrydate))
  • Rows Removed by Filter: 6
175. 0.114 93.849 ↑ 1.0 1 3

Aggregate (cost=1,486.91..1,486.92 rows=1 width=32) (actual time=31.282..31.283 rows=1 loops=3)

176.          

Initplan (for Aggregate)

177. 0.006 0.006 ↑ 1.0 1 1

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

178. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

179. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=8) (never executed)

180. 0.198 93.729 ↑ 4.1 7 3

Nested Loop (cost=1,022.35..1,485.65 rows=29 width=25) (actual time=15.146..31.243 rows=7 loops=3)

181. 0.258 93.426 ↑ 4.1 7 3

Nested Loop (cost=1,022.07..1,474.23 rows=29 width=57) (actual time=15.141..31.142 rows=7 loops=3)

182. 2.402 93.042 ↑ 4.1 7 3

Hash Join (cost=1,021.80..1,463.87 rows=29 width=41) (actual time=15.095..31.014 rows=7 loops=3)

  • Hash Cond: (ii.id = oiceitemdenormalizedmetadata49.invoiceitemid)
183. 3.335 89.992 ↑ 1.0 1,092 1

Hash Left Join (cost=988.78..1,427.98 rows=1,092 width=73) (actual time=40.965..89.992 rows=1,092 loops=1)

  • Hash Cond: (ii.id = timesheetperiod.invoiceitemid)
184. 3.174 86.641 ↑ 1.0 1,092 1

Hash Left Join (cost=980.41..1,415.50 rows=1,092 width=73) (actual time=40.945..86.641 rows=1,092 loops=1)

  • Hash Cond: (ii.id = fixedbidperiod.invoiceitemid)
185. 3.738 83.439 ↑ 1.0 1,092 1

Hash Left Join (cost=972.04..1,403.02 rows=1,092 width=73) (actual time=40.912..83.439 rows=1,092 loops=1)

  • Hash Cond: (ii.id = description.invoiceitemid)
186. 3.542 79.684 ↑ 1.0 1,092 1

Hash Left Join (cost=963.66..1,390.54 rows=1,092 width=73) (actual time=40.886..79.684 rows=1,092 loops=1)

  • Hash Cond: (ii.id = iimd2.invoiceitemid)
187. 3.777 76.059 ↑ 1.0 1,092 1

Hash Left Join (cost=955.29..1,378.06 rows=1,092 width=73) (actual time=40.796..76.059 rows=1,092 loops=1)

  • Hash Cond: (i2.invoicecurrencyid = exchangerate.fixedcurrencyid)
  • Join Filter: (((COALESCE((COALESCE(iim.daterange_enddate, iim.daterange_startdate))::timestamp without time zone, (i2m.date)::timestamp without time zone, $41)) >= exchangerate.effectivedate) AND (( (...)
188. 4.259 72.240 ↑ 1.0 1,092 1

Hash Left Join (cost=953.67..1,372.01 rows=1,092 width=65) (actual time=40.745..72.240 rows=1,092 loops=1)

  • Hash Cond: ((i2.id = ii_1.invoiceid) AND (ii.id = ii_1.id))
189. 3.980 50.312 ↑ 1.0 1,092 1

Hash Join (cost=646.35..1,056.50 rows=1,092 width=73) (actual time=23.061..50.312 rows=1,092 loops=1)

  • Hash Cond: (ii.id = invoiceitem46.id)
190. 3.757 43.690 ↑ 1.0 1,092 1

Hash Join (cost=611.78..1,019.05 rows=1,092 width=41) (actual time=20.406..43.690 rows=1,092 loops=1)

  • Hash Cond: (i2.id = i2dm.invoiceid)
191. 3.523 37.390 ↑ 1.0 1,092 1

Hash Join (cost=566.94..971.34 rows=1,092 width=57) (actual time=17.850..37.390 rows=1,092 loops=1)

  • Hash Cond: (ii.invoiceid = i2.id)
192. 3.924 31.200 ↑ 1.0 1,092 1

Hash Right Join (cost=532.10..933.62 rows=1,092 width=37) (actual time=15.173..31.200 rows=1,092 loops=1)

  • Hash Cond: (iidm.invoiceitemid = ii.id)
193. 3.443 24.574 ↑ 1.0 1,092 1

Hash Right Join (cost=497.53..896.17 rows=1,092 width=16) (actual time=12.461..24.574 rows=1,092 loops=1)

  • Hash Cond: (iir.invoiceitemid = iidm.invoiceitemid)
194. 4.394 18.259 ↑ 1.0 1,092 1

Hash Right Join (cost=453.96..849.72 rows=1,092 width=16) (actual time=9.576..18.259 rows=1,092 loops=1)

  • Hash Cond: ((iimrrate.invoiceitemid = iir.invoiceitemid) AND (iimrrate.fromtimestamputc = iir.fromtimestamputc))
195. 4.309 4.309 ↑ 1.0 1,092 1

Seq Scan on invoiceitemmetadatarevision iimrrate (cost=0.00..387.55 rows=1,092 width=24) (actual time=0.006..4.309 rows=1,092 loops=1)

  • Filter: (key = 'urn:replicon:invoice-item-metadata-key:rate'::text)
  • Rows Removed by Filter: 6552
196. 1.428 9.556 ↑ 1.0 1,092 1

Hash (cost=437.58..437.58 rows=1,092 width=24) (actual time=9.555..9.556 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
197. 2.757 8.128 ↑ 1.0 1,092 1

Hash Right Join (cost=44.30..437.58 rows=1,092 width=24) (actual time=2.761..8.128 rows=1,092 loops=1)

  • Hash Cond: ((iimrqty.invoiceitemid = iir.invoiceitemid) AND (iimrqty.fromtimestamputc = iir.fromtimestamputc))
198. 2.627 2.627 ↑ 1.0 1,092 1

Seq Scan on invoiceitemmetadatarevision iimrqty (cost=0.00..387.55 rows=1,092 width=24) (actual time=0.006..2.627 rows=1,092 loops=1)

  • Filter: (key = 'urn:replicon:invoice-item-metadata-key:quantity'::text)
  • Rows Removed by Filter: 6552
199. 1.351 2.744 ↑ 1.0 1,092 1

Hash (cost=27.92..27.92 rows=1,092 width=24) (actual time=2.743..2.744 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
200. 1.393 1.393 ↑ 1.0 1,092 1

Seq Scan on invoiceitemrevision iir (cost=0.00..27.92 rows=1,092 width=24) (actual time=0.009..1.393 rows=1,092 loops=1)

  • Filter: (previousrevisionid IS NULL)
201. 1.459 2.872 ↑ 1.0 1,092 1

Hash (cost=29.92..29.92 rows=1,092 width=16) (actual time=2.871..2.872 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
202. 1.413 1.413 ↑ 1.0 1,092 1

Seq Scan on invoiceitemdenormalizedmetadata iidm (cost=0.00..29.92 rows=1,092 width=16) (actual time=0.005..1.413 rows=1,092 loops=1)

203. 1.396 2.702 ↑ 1.0 1,092 1

Hash (cost=20.92..20.92 rows=1,092 width=37) (actual time=2.701..2.702 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
204. 1.306 1.306 ↑ 1.0 1,092 1

Seq Scan on invoiceitem ii (cost=0.00..20.92 rows=1,092 width=37) (actual time=0.015..1.306 rows=1,092 loops=1)

205. 1.376 2.667 ↑ 1.0 1,104 1

Hash (cost=21.04..21.04 rows=1,104 width=20) (actual time=2.666..2.667 rows=1,104 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
206. 1.291 1.291 ↑ 1.0 1,104 1

Seq Scan on invoice2 i2 (cost=0.00..21.04 rows=1,104 width=20) (actual time=0.008..1.291 rows=1,104 loops=1)

207. 1.295 2.543 ↑ 1.0 1,104 1

Hash (cost=31.04..31.04 rows=1,104 width=16) (actual time=2.542..2.543 rows=1,104 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
208. 1.248 1.248 ↑ 1.0 1,104 1

Seq Scan on invoice2denormalizedmetadata i2dm (cost=0.00..31.04 rows=1,104 width=16) (actual time=0.005..1.248 rows=1,104 loops=1)

209. 1.365 2.642 ↑ 1.0 1,092 1

Hash (cost=20.92..20.92 rows=1,092 width=32) (actual time=2.641..2.642 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
210. 1.277 1.277 ↑ 1.0 1,092 1

Seq Scan on invoiceitem invoiceitem46 (cost=0.00..20.92 rows=1,092 width=32) (actual time=0.007..1.277 rows=1,092 loops=1)

211. 1.420 17.669 ↑ 1.0 1,092 1

Hash (cost=290.93..290.93 rows=1,092 width=40) (actual time=17.668..17.669 rows=1,092 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
212. 2.771 16.249 ↑ 1.0 1,092 1

Hash Left Join (cost=239.97..290.93 rows=1,092 width=40) (actual time=9.728..16.249 rows=1,092 loops=1)

  • Hash Cond: (ii_1.invoiceid = i2m.invoiceid)
213. 2.517 3.799 ↑ 1.0 1,092 1

Hash Left Join (cost=8.37..33.40 rows=1,092 width=40) (actual time=0.038..3.799 rows=1,092 loops=1)

  • Hash Cond: (ii_1.id = iim.invoiceitemid)
214. 1.260 1.260 ↑ 1.0 1,092 1

Seq Scan on invoiceitem ii_1 (cost=0.00..20.92 rows=1,092 width=32) (actual time=0.007..1.260 rows=1,092 loops=1)

215. 0.003 0.022 ↓ 0.0 0 1

Hash (cost=8.36..8.36 rows=1 width=24) (actual time=0.021..0.022 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
216. 0.019 0.019 ↓ 0.0 0 1

Index Scan using ix2iimkeyinvoiceitemid on invoiceitemmetadata iim (cost=0.41..8.36 rows=1 width=24) (actual time=0.018..0.019 rows=0 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:INVOICE-ITEM-METADATA-KEY:DATE-RANGE'::text)
217. 1.481 9.679 ↑ 1.0 1,104 1

Hash (cost=217.80..217.80 rows=1,104 width=20) (actual time=9.678..9.679 rows=1,104 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
218. 8.198 8.198 ↑ 1.0 1,104 1

Seq Scan on invoice2metadata i2m (cost=0.00..217.80 rows=1,104 width=20) (actual time=0.012..8.198 rows=1,104 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:INVOICE-METADATA-KEY:INVOICE-DATE'::text)
  • Rows Removed by Filter: 4416
219. 0.011 0.042 ↓ 7.0 7 1

Hash (cost=1.61..1.61 rows=1 width=32) (actual time=0.041..0.042 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
220. 0.031 0.031 ↓ 7.0 7 1

Seq Scan on exchangerate (cost=0.00..1.61 rows=1 width=32) (actual time=0.017..0.031 rows=7 loops=1)

  • Filter: (variablecurrencyid = $40)
  • Rows Removed by Filter: 42
221. 0.025 0.083 ↓ 0.0 0 1

Hash (cost=8.36..8.36 rows=1 width=16) (actual time=0.082..0.083 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
222. 0.058 0.058 ↓ 0.0 0 1

Index Scan using ix2iimkeyinvoiceitemid on invoiceitemmetadata iimd2 (cost=0.41..8.36 rows=1 width=16) (actual time=0.057..0.058 rows=0 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:INVOICE-ITEM-METADATA-KEY:EXPENSE-DESCRIPTION'::text)
223. 0.003 0.017 ↓ 0.0 0 1

Hash (cost=8.36..8.36 rows=1 width=16) (actual time=0.016..0.017 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
224. 0.014 0.014 ↓ 0.0 0 1

Index Scan using ix2iimkeyinvoiceitemid on invoiceitemmetadata description (cost=0.41..8.36 rows=1 width=16) (actual time=0.013..0.014 rows=0 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:INVOICE-ITEM-METADATA-KEY:DESCRIPTION'::text)
225. 0.003 0.028 ↓ 0.0 0 1

Hash (cost=8.36..8.36 rows=1 width=16) (actual time=0.027..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
226. 0.025 0.025 ↓ 0.0 0 1

Index Scan using ix2iimkeyinvoiceitemid on invoiceitemmetadata fixedbidperiod (cost=0.41..8.36 rows=1 width=16) (actual time=0.024..0.025 rows=0 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:INVOICE-ITEM-METADATA-KEY:FIXED-BID-ENTRY-PERIOD'::text)
227. 0.002 0.016 ↓ 0.0 0 1

Hash (cost=8.36..8.36 rows=1 width=16) (actual time=0.015..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
228. 0.014 0.014 ↓ 0.0 0 1

Index Scan using ix2iimkeyinvoiceitemid on invoiceitemmetadata timesheetperiod (cost=0.41..8.36 rows=1 width=16) (actual time=0.013..0.014 rows=0 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:INVOICE-ITEM-METADATA-KEY:TIMESHEET-PERIOD'::text)
229. 0.039 0.648 ↑ 4.1 7 3

Hash (cost=32.65..32.65 rows=29 width=16) (actual time=0.215..0.216 rows=7 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
230. 0.609 0.609 ↑ 4.1 7 3

Seq Scan on invoiceitemdenormalizedmetadata oiceitemdenormalizedmetadata49 (cost=0.00..32.65 rows=29 width=16) (actual time=0.126..0.203 rows=7 loops=3)

  • Filter: (project0.id = projectid)
  • Rows Removed by Filter: 1085
231. 0.126 0.126 ↑ 1.0 1 21

Index Only Scan using invoice2_pkey on invoice2 invoice247 (cost=0.28..0.36 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=21)

  • Index Cond: (id = invoiceitem46.invoiceid)
  • Heap Fetches: 21
232. 0.105 0.105 ↑ 1.0 1 21

Index Scan using uixinv2dm on invoice2denormalizedmetadata invoice2denormalizedmetadata48 (cost=0.28..0.39 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=21)

  • Index Cond: (invoiceid = invoiceitem46.invoiceid)
  • Filter: (invoicedate IS NOT NULL)
Planning time : 15.864 ms
Execution time : 6,750.540 ms