# | exclusive | inclusive | rows x | rows | loops | node |
---|---|---|---|---|---|---|
1. | 1,783.227 | 103,783.510 | ↓ 1.7 | 294,411 | 1 |
→
Sort (cost=1,442,734.39..1,443,165.76 rows=172,547 width=208) (actual time=103,670.463..103,783.510 rows=294,411 loops=1)
|
2. | 323.802 | 102,000.283 | ↓ 1.7 | 294,411 | 1 |
→
Subquery Scan on dmv_bi_projectcostbill0 (cost=1,421,686.56..1,427,725.71 rows=172,547 width=208) (actual time=101,170.265..102,000.283 rows=294,411 loops=1)
|
3. | 7,020.185 | 101,676.481 | ↓ 1.7 | 294,411 | 1 |
→
HashAggregate (cost=1,421,686.56..1,426,000.24 rows=172,547 width=184) (actual time=101,170.263..101,676.481 rows=294,411 loops=1)
|
4. |
CTE project_time |
|||||
5. | 3,982.166 | 4,800.329 | ↓ 9.3 | 1,600,460 | 1 |
→
HashAggregate (cost=86,526.39..88,251.48 rows=172,509 width=52) (actual time=3,292.583..4,800.329 rows=1,600,460 loops=1)
|
6. | 818.163 | 818.163 | ↓ 1.0 | 1,725,282 | 1 |
→
Seq Scan on dm_projecttimeallocation_facts allocation (cost=0.00..47,711.89 rows=1,725,089 width=52) (actual time=0.013..818.163 rows=1,725,282 loops=1)
|
7. |
CTE project_billing |
|||||
8. | 4,627.499 | 10,808.448 | ↓ 8.3 | 1,600,258 | 1 |
→
HashAggregate (cost=119,313.38..121,724.77 rows=192,911 width=26) (actual time=9,081.329..10,808.448 rows=1,600,258 loops=1)
|
9. |
Initplan (forHashAggregate) |
|||||
10. | 0.009 | 0.009 | ↑ 1.0 | 1 | 1 |
→
Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)
|
11. | 2,344.720 | 6,180.940 | ↓ 9.0 | 1,736,318 | 1 |
→
Hash Join (cost=58,586.80..116,418.70 rows=192,911 width=26) (actual time=2,937.580..6,180.940 rows=1,736,318 loops=1)
|
12. | 898.956 | 898.956 | ↓ 1.0 | 1,736,318 | 1 |
→
Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..49,392.03 rows=1,736,203 width=28) (actual time=0.013..898.956 rows=1,736,318 loops=1)
|
13. | 979.390 | 2,937.264 | ↓ 9.0 | 1,736,318 | 1 |
→
Hash (cost=56,175.25..56,175.25 rows=192,924 width=30) (actual time=2,937.264..2,937.264 rows=1,736,318 loops=1)
|
14. | 1,043.089 | 1,957.874 | ↓ 9.0 | 1,736,318 | 1 |
→
Nested Loop (cost=0.43..56,175.25 rows=192,924 width=30) (actual time=0.074..1,957.874 rows=1,736,318 loops=1)
|
15. | 0.032 | 0.032 | ↑ 1.0 | 7 | 1 |
→
Seq Scan on exchangerate (cost=0.00..1.61 rows=7 width=19) (actual time=0.023..0.032 rows=7 loops=1)
|
16. | 914.753 | 914.753 | ↓ 1.3 | 248,045 | 7 |
→
Index Scan using dbatest1 on billingitem2 bi (cost=0.43..6,095.57 rows=192,924 width=27) (actual time=0.009..130.679 rows=248,045 loops=7)
|
17. |
CTE project_cost |
|||||
18. | 4,502.649 | 12,703.399 | ↑ 1.0 | 1,725,284 | 1 |
→
Hash Left Join (cost=2,257.06..233,679.34 rows=1,725,468 width=130) (actual time=93.340..12,703.399 rows=1,725,284 loops=1)
|
19. |
Initplan (forHash Left Join) |
|||||
20. | 0.003 | 0.003 | ↑ 1.0 | 1 | 1 |
→
Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
|
21. | 1,934.121 | 8,200.717 | ↑ 1.0 | 1,725,284 | 1 |
→
Hash Left Join (cost=2,254.35..149,560.07 rows=1,725,468 width=127) (actual time=93.270..8,200.717 rows=1,725,284 loops=1)
|
22. | 1,600.060 | 6,265.137 | ↑ 1.0 | 1,725,284 | 1 |
→
Hash Left Join (cost=2,214.73..117,167.92 rows=1,725,468 width=120) (actual time=91.789..6,265.137 rows=1,725,284 loops=1)
|
23. | 1,618.218 | 4,576.306 | ↑ 1.0 | 1,725,284 | 1 |
→
Hash Left Join (cost=172.78..92,708.12 rows=1,725,468 width=116) (actual time=2.932..4,576.306 rows=1,725,284 loops=1)
|
24. | 1,470.846 | 2,955.193 | ↑ 1.0 | 1,725,284 | 1 |
→
Merge Left Join (cost=0.58..69,730.98 rows=1,725,468 width=112) (actual time=0.021..2,955.193 rows=1,725,284 loops=1)
|
25. | 1,484.344 | 1,484.344 | ↑ 1.0 | 1,725,284 | 1 |
→
Index Scan using ixata2userid on dm_attendancetimeallocation_facts (cost=0.43..61,078.45 rows=1,725,468 width=52) (actual time=0.013..1,484.344 rows=1,725,284 loops=1)
|
26. | 0.003 | 0.003 | ↓ 0.0 | 0 | 1 |
→
Index Scan using uix2ucnf_useridentrydate on usercostnormalizationfactor (cost=0.15..19.85 rows=580 width=68) (actual time=0.003..0.003 rows=0 loops=1)
|
27. | 0.963 | 2.895 | ↑ 1.0 | 2,587 | 1 |
→
Hash (cost=139.87..139.87 rows=2,587 width=8) (actual time=2.895..2.895 rows=2,587 loops=1)
|
28. | 1.932 | 1.932 | ↑ 1.0 | 2,587 | 1 |
→
Seq Scan on project (cost=0.00..139.87 rows=2,587 width=8) (actual time=0.004..1.932 rows=2,587 loops=1)
|
29. | 30.807 | 88.771 | ↑ 1.0 | 41,731 | 1 |
→
Hash (cost=1,520.31..1,520.31 rows=41,731 width=8) (actual time=88.771..88.771 rows=41,731 loops=1)
|
30. | 57.964 | 57.964 | ↑ 1.0 | 41,731 | 1 |
→
Seq Scan on task (cost=0.00..1,520.31 rows=41,731 width=8) (actual time=0.009..57.964 rows=41,731 loops=1)
|
31. | 0.770 | 1.459 | ↑ 1.0 | 1,272 | 1 |
→
Hash (cost=23.72..23.72 rows=1,272 width=19) (actual time=1.459..1.459 rows=1,272 loops=1)
|
32. | 0.689 | 0.689 | ↑ 1.0 | 1,272 | 1 |
→
Seq Scan on userprojectroleratehistory (cost=0.00..23.72 rows=1,272 width=19) (actual time=0.004..0.689 rows=1,272 loops=1)
|
33. | 0.007 | 0.030 | ↑ 1.0 | 7 | 1 |
→
Hash (cost=1.61..1.61 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1)
|
34. | 0.023 | 0.023 | ↑ 1.0 | 7 | 1 |
→
Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=7 width=19) (actual time=0.012..0.023 rows=7 loops=1)
|
35. |
CTE expense_cost |
|||||
36. | 0.002 | 0.004 | ↓ 0.0 | 0 | 1 |
→
Nested Loop (cost=0.28..4.33 rows=1 width=60) (actual time=0.004..0.004 rows=0 loops=1)
|
37. | 0.002 | 0.002 | ↓ 0.0 | 0 | 1 |
→
Index Scan using ixex2approvalstatus on expense (cost=0.14..2.16 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
|
38. | 0.000 | 0.000 | ↓ 0.0 | 0 |
→
Index Scan using ixeeexpenseid on expenseentry (cost=0.14..2.16 rows=1 width=60) (never executed)
|
|
39. |
CTE pipeline1 |
|||||
40. | 4,397.297 | 65,554.383 | ↓ 2.0 | 3,446,124 | 1 |
→
Hash Full Join (cost=274,007.38..339,931.56 rows=1,725,468 width=188) (actual time=36,486.608..65,554.383 rows=3,446,124 loops=1)
|
41. | 3,452.332 | 61,157.080 | ↓ 2.0 | 3,446,124 | 1 |
→
Merge Full Join (cost=274,007.35..307,578.99 rows=1,725,468 width=136) (actual time=36,486.585..61,157.080 rows=3,446,124 loops=1)
|
42. | 4,582.693 | 37,501.892 | ↓ 8.9 | 1,720,840 | 1 |
→
Sort (cost=60,751.96..61,234.23 rows=192,911 width=92) (actual time=36,486.580..37,501.892 rows=1,720,840 loops=1)
|
43. | 2,771.183 | 32,919.199 | ↓ 8.9 | 1,720,840 | 1 |
→
Merge Full Join (cost=39,248.75..43,816.71 rows=192,911 width=92) (actual time=28,186.020..32,919.199 rows=1,720,840 loops=1)
|
44. | 4,849.515 | 11,686.971 | ↓ 9.3 | 1,600,460 | 1 |
→
Sort (cost=18,455.28..18,886.55 rows=172,509 width=44) (actual time=10,501.031..11,686.971 rows=1,600,460 loops=1)
|
45. | 6,837.456 | 6,837.456 | ↓ 9.3 | 1,600,460 | 1 |
→
CTE Scan on project_time (cost=0.00..3,450.18 rows=172,509 width=44) (actual time=3,292.587..6,837.456 rows=1,600,460 loops=1)
|
46. | 5,475.216 | 18,461.045 | ↓ 8.3 | 1,600,258 | 1 |
→
Sort (cost=20,793.47..21,275.75 rows=192,911 width=48) (actual time=17,684.980..18,461.045 rows=1,600,258 loops=1)
|
47. | 12,985.829 | 12,985.829 | ↓ 8.3 | 1,600,258 | 1 |
→
CTE Scan on project_billing (cost=0.00..3,858.22 rows=192,911 width=48) (actual time=9,081.333..12,985.829 rows=1,600,258 loops=1)
|
48. | 5,127.519 | 20,202.856 | ↑ 1.0 | 1,725,284 | 1 |
→
Sort (cost=213,255.39..217,569.06 rows=1,725,468 width=44) (actual time=19,143.964..20,202.856 rows=1,725,284 loops=1)
|
49. | 15,075.337 | 15,075.337 | ↑ 1.0 | 1,725,284 | 1 |
→
CTE Scan on project_cost (cost=0.00..34,509.36 rows=1,725,468 width=44) (actual time=93.344..15,075.337 rows=1,725,284 loops=1)
|
50. | 0.002 | 0.006 | ↓ 0.0 | 0 | 1 |
→
Hash (cost=0.02..0.02 rows=1 width=52) (actual time=0.006..0.006 rows=0 loops=1)
|
51. | 0.004 | 0.004 | ↓ 0.0 | 0 | 1 |
→
CTE Scan on expense_cost (cost=0.00..0.02 rows=1 width=52) (actual time=0.004..0.004 rows=0 loops=1)
|
52. |
CTE pipeline2 |
|||||
53. | 3,046.847 | 84,498.671 | ↓ 2.0 | 3,446,124 | 1 |
→
Hash Left Join (cost=103.29..525,939.66 rows=1,725,468 width=184) (actual time=36,487.841..84,498.671 rows=3,446,124 loops=1)
|
54. | 3,499.982 | 81,451.823 | ↓ 2.0 | 3,446,124 | 1 |
→
Hash Left Join (cost=67.87..131,634.80 rows=1,725,468 width=168) (actual time=36,487.832..81,451.823 rows=3,446,124 loops=1)
|
55. | 3,577.403 | 77,951.459 | ↓ 2.0 | 3,446,124 | 1 |
→
Hash Left Join (cost=45.92..99,260.33 rows=1,725,468 width=152) (actual time=36,487.442..77,951.459 rows=3,446,124 loops=1)
|
56. | 4,023.052 | 74,373.623 | ↓ 2.0 | 3,446,124 | 1 |
→
Hash Left Join (cost=30.94..66,892.83 rows=1,725,468 width=136) (actual time=36,487.000..74,373.623 rows=3,446,124 loops=1)
|
57. | 70,350.193 | 70,350.193 | ↓ 2.0 | 3,446,124 | 1 |
→
CTE Scan on pipeline1 (cost=0.00..34,509.36 rows=1,725,468 width=120) (actual time=36,486.610..70,350.193 rows=3,446,124 loops=1)
|
58. | 0.133 | 0.378 | ↑ 1.0 | 353 | 1 |
→
Hash (cost=26.53..26.53 rows=353 width=28) (actual time=0.378..0.378 rows=353 loops=1)
|
59. | 0.245 | 0.245 | ↑ 1.0 | 353 | 1 |
→
Seq Scan on userlocation (cost=0.00..26.53 rows=353 width=28) (actual time=0.013..0.245 rows=353 loops=1)
|
60. | 0.219 | 0.433 | ↑ 1.0 | 353 | 1 |
→
Hash (cost=10.57..10.57 rows=353 width=28) (actual time=0.433..0.433 rows=353 loops=1)
|
61. | 0.214 | 0.214 | ↑ 1.0 | 353 | 1 |
→
Index Only Scan using ix4ud_userdivisionstartend on userdivision (cost=0.27..10.57 rows=353 width=28) (actual time=0.034..0.214 rows=353 loops=1)
|
62. | 0.181 | 0.382 | ↑ 1.0 | 353 | 1 |
→
Hash (cost=17.53..17.53 rows=353 width=28) (actual time=0.382..0.382 rows=353 loops=1)
|
63. | 0.201 | 0.201 | ↑ 1.0 | 353 | 1 |
→
Seq Scan on usercostcenter (cost=0.00..17.53 rows=353 width=28) (actual time=0.013..0.201 rows=353 loops=1)
|
64. | 0.000 | 0.001 | ↓ 0.0 | 0 | 1 |
→
Hash (cost=21.30..21.30 rows=1,130 width=28) (actual time=0.001..0.001 rows=0 loops=1)
|
65. | 0.001 | 0.001 | ↓ 0.0 | 0 | 1 |
→
Seq Scan on userservicecenter (cost=0.00..21.30 rows=1,130 width=28) (actual time=0.001..0.001 rows=0 loops=1) |
66. | 94,656.296 | 94,656.296 | ↓ 2.0 | 3,446,124 | 1 |
→
CTE Scan on pipeline2 (cost=0.00..51,764.04 rows=1,725,468 width=184) (actual time=36,487.858..94,656.296 rows=3,446,124 loops=1)
|