explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qr97 : Optimization for: DWH - Slave AndRept; plan #d5IZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13.798 2,041.595 ↑ 9,257.4 1,336 1

Hash Left Join (cost=10,002,735,146.40..10,003,724,092.42 rows=12,367,832 width=1,021) (actual time=2,017.675..2,041.595 rows=1,336 loops=1)

  • Hash Cond: (xco.tactic_order_type = atp.order_type)
2.          

CTE temp_claim_custom_fields

3. 4.716 4.716 ↑ 1.2 6,874 1

Seq Scan on fact_custom_fields_claims ccf (cost=10,000,000,000.00..10,000,000,936.17 rows=8,509 width=51) (actual time=0.017..4.716 rows=6,874 loops=1)

  • Filter: ((activity_sub_detail IS NOT NULL) OR (product_series IS NOT NULL) OR (submitting_dealer IS NOT NULL))
  • Rows Removed by Filter: 16760
4.          

CTE cte_activity_type_mapping

5. 0.022 0.092 ↑ 1.0 78 1

Append (cost=0.00..1.56 rows=78 width=64) (actual time=0.002..0.092 rows=78 loops=1)

6. 0.001 0.001 ↑ 1.0 1 1

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

7. 0.001 0.001 ↑ 1.0 1 1

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

8. 0.001 0.001 ↑ 1.0 1 1

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

9. 0.001 0.001 ↑ 1.0 1 1

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

10. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

11. 0.001 0.001 ↑ 1.0 1 1

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

12. 0.001 0.001 ↑ 1.0 1 1

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

13. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

14. 0.001 0.001 ↑ 1.0 1 1

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

15. 0.001 0.001 ↑ 1.0 1 1

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

16. 0.001 0.001 ↑ 1.0 1 1

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

17. 0.001 0.001 ↑ 1.0 1 1

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

18. 0.001 0.001 ↑ 1.0 1 1

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

19. 0.001 0.001 ↑ 1.0 1 1

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

20. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1

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

22. 0.001 0.001 ↑ 1.0 1 1

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

23. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

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

25. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

26. 0.001 0.001 ↑ 1.0 1 1

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

27. 0.001 0.001 ↑ 1.0 1 1

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

28. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

29. 0.001 0.001 ↑ 1.0 1 1

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

30. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

31. 0.001 0.001 ↑ 1.0 1 1

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

32. 0.001 0.001 ↑ 1.0 1 1

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

33. 0.001 0.001 ↑ 1.0 1 1

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

34. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=1)

35. 0.001 0.001 ↑ 1.0 1 1

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

36. 0.001 0.001 ↑ 1.0 1 1

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

37. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

38. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

39. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

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

41. 0.001 0.001 ↑ 1.0 1 1

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

42. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

43. 0.001 0.001 ↑ 1.0 1 1

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

44. 0.001 0.001 ↑ 1.0 1 1

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

45. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

46. 0.001 0.001 ↑ 1.0 1 1

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

47. 0.001 0.001 ↑ 1.0 1 1

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

48. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

49. 0.001 0.001 ↑ 1.0 1 1

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

50. 0.001 0.001 ↑ 1.0 1 1

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

51. 0.001 0.001 ↑ 1.0 1 1

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

52. 0.001 0.001 ↑ 1.0 1 1

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

53. 0.001 0.001 ↑ 1.0 1 1

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

54. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

55. 0.001 0.001 ↑ 1.0 1 1

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

56. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

57. 0.001 0.001 ↑ 1.0 1 1

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

58. 0.001 0.001 ↑ 1.0 1 1

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

59. 0.001 0.001 ↑ 1.0 1 1

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

60. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

61. 0.001 0.001 ↑ 1.0 1 1

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

62. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=1)

63. 0.001 0.001 ↑ 1.0 1 1

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

64. 0.001 0.001 ↑ 1.0 1 1

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

65. 0.001 0.001 ↑ 1.0 1 1

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

66. 0.001 0.001 ↑ 1.0 1 1

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

67. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

68. 0.001 0.001 ↑ 1.0 1 1

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

69. 0.001 0.001 ↑ 1.0 1 1

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

70. 0.001 0.001 ↑ 1.0 1 1

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

71. 0.001 0.001 ↑ 1.0 1 1

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

72. 0.001 0.001 ↑ 1.0 1 1

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

73. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.008..0.008 rows=1 loops=1)

74. 0.001 0.001 ↑ 1.0 1 1

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

75. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

76. 0.001 0.001 ↑ 1.0 1 1

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

77. 0.001 0.001 ↑ 1.0 1 1

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

78. 0.001 0.001 ↑ 1.0 1 1

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

79. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

80. 0.001 0.001 ↑ 1.0 1 1

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

81. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.002..0.002 rows=1 loops=1)

82. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=1)

83. 0.001 0.001 ↑ 1.0 1 1

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

84.          

CTE cte_payment_types

85. 0.003 0.007 ↑ 1.0 4 1

Append (cost=0.00..0.08 rows=4 width=64) (actual time=0.001..0.007 rows=4 loops=1)

86. 0.001 0.001 ↑ 1.0 1 1

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

87. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.000..0.001 rows=1 loops=1)

88. 0.001 0.001 ↑ 1.0 1 1

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

89. 0.001 0.001 ↑ 1.0 1 1

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

90. 4.055 2,027.652 ↑ 34,259.9 361 1

Hash Right Join (cost=2,734,206.05..2,888,995.41 rows=12,367,832 width=859) (actual time=2,017.470..2,027.652 rows=361 loops=1)

  • Hash Cond: (tt.sk_claim_id = cte_1.sk_claim_id)
91. 6.932 6.932 ↑ 1.2 6,874 1

CTE Scan on temp_claim_custom_fields tt (cost=0.00..170.18 rows=8,509 width=100) (actual time=0.019..6.932 rows=6,874 loops=1)

92. 0.659 2,016.665 ↑ 805.3 361 1

Hash (cost=2,730,572.30..2,730,572.30 rows=290,700 width=767) (actual time=2,016.665..2,016.665 rows=361 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4298kB
93. 0.371 2,016.006 ↑ 805.3 361 1

Hash Left Join (cost=39,313.89..2,730,572.30 rows=290,700 width=767) (actual time=1,937.318..2,016.006 rows=361 loops=1)

  • Hash Cond: ((cte_1.claim_id = xpm.claim_id) AND (xco.order_id = xpm.order_id))
94. 0.847 1,983.269 ↑ 805.3 361 1

Nested Loop Left Join (cost=37,164.63..2,726,242.79 rows=290,700 width=750) (actual time=1,904.905..1,983.269 rows=361 loops=1)

95. 0.603 1,981.339 ↑ 8.1 361 1

Nested Loop Left Join (cost=37,154.32..2,686,809.33 rows=2,907 width=458) (actual time=1,904.894..1,981.339 rows=361 loops=1)

96. 0.263 1,959.437 ↑ 8.1 361 1

Nested Loop Left Join (cost=36,345.30..334,922.78 rows=2,907 width=426) (actual time=1,904.799..1,959.437 rows=361 loops=1)

97. 0.449 1,942.568 ↓ 5.5 361 1

Nested Loop Left Join (cost=36,340.70..334,182.05 rows=66 width=403) (actual time=1,904.746..1,942.568 rows=361 loops=1)

98. 0.381 1,940.314 ↓ 5.5 361 1

Nested Loop (cost=36,340.28..334,150.97 rows=66 width=403) (actual time=1,904.716..1,940.314 rows=361 loops=1)

99. 0.313 1,932.713 ↓ 180.5 361 1

Hash Join (cost=35,967.08..333,766.15 rows=2 width=324) (actual time=1,904.618..1,932.713 rows=361 loops=1)

  • Hash Cond: (xfj.sk_fund_class = xfc.sk_fund_class)
100. 0.391 1,932.060 ↓ 180.5 361 1

Nested Loop (cost=35,916.44..333,715.48 rows=2 width=310) (actual time=1,904.262..1,932.060 rows=361 loops=1)

  • Join Filter: (xrm.account_id = xct.account_id)
101. 0.355 1,928.059 ↓ 120.3 361 1

Nested Loop (cost=35,916.01..333,713.48 rows=3 width=302) (actual time=1,904.232..1,928.059 rows=361 loops=1)

102. 0.236 1,925.899 ↓ 2.7 361 1

Nested Loop (cost=35,915.59..333,645.89 rows=132 width=277) (actual time=1,904.195..1,925.899 rows=361 loops=1)

103. 0.291 1,924.219 ↓ 2.7 361 1

Nested Loop Left Join (cost=35,915.17..333,399.86 rows=132 width=239) (actual time=1,904.174..1,924.219 rows=361 loops=1)

  • Join Filter: (xfu.fund_source_type = 'SHARED'::text)
104. 0.325 1,923.928 ↓ 2.7 361 1

Nested Loop (cost=35,914.74..333,324.23 rows=132 width=198) (actual time=1,904.167..1,923.928 rows=361 loops=1)

105. 0.512 1,922.159 ↓ 2.7 361 1

Nested Loop (cost=35,914.32..333,010.68 rows=132 width=113) (actual time=1,904.130..1,922.159 rows=361 loops=1)

106. 2.601 1,920.203 ↓ 2.7 361 1

Hash Join (cost=35,913.89..332,521.33 rows=132 width=46) (actual time=1,904.105..1,920.203 rows=361 loops=1)

  • Hash Cond: ((xfj.sk_type_entry = xte.sk_type_entry) AND (xfj.sk_budget_id = xbu.sk_budget_id))
107. 1,635.394 1,917.219 ↓ 1.5 18,223 1

Bitmap Heap Scan on fact_fund_journal xfj (cost=35,903.42..332,417.12 rows=12,323 width=29) (actual time=1,898.607..1,917.219 rows=18,223 loops=1)

  • Recheck Cond: (is_transaction_visible = 'INCLUDE'::text)
  • Filter: (((src_modified_ts)::date >= '2019-05-01'::date) AND ((src_modified_ts)::date <= '2019-05-13'::date))
  • Rows Removed by Filter: 2419844
  • Heap Blocks: exact=211372
108. 281.825 281.825 ↑ 1.0 2,438,103 1

Bitmap Index Scan on x_fact_fund_journal_is_transaction_visible (cost=0.00..35,900.33 rows=2,464,520 width=0) (actual time=281.825..281.825 rows=2,438,103 loops=1)

  • Index Cond: (is_transaction_visible = 'INCLUDE'::text)
109. 0.027 0.383 ↑ 1.9 52 1

Hash (cost=8.97..8.97 rows=100 width=33) (actual time=0.383..0.383 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
110. 0.072 0.356 ↑ 1.9 52 1

Nested Loop (cost=2.17..8.97 rows=100 width=33) (actual time=0.067..0.356 rows=52 loops=1)

111. 0.011 0.076 ↓ 52.0 52 1

Nested Loop (cost=1.89..7.91 rows=1 width=36) (actual time=0.050..0.076 rows=52 loops=1)

112. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uc_dim_fund_journal_type_entry on dim_fund_journal_type_entry xte (cost=0.14..4.15 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (type_entry = 'APPLY'::text)
113. 0.037 0.054 ↑ 1.9 52 1

HashAggregate (cost=1.76..2.76 rows=100 width=32) (actual time=0.037..0.054 rows=52 loops=1)

  • Group Key: unnest('{BU18070005461443,BU18110001627699,BU18080004837817,BU18110001627905,BU18120001142583,BU18080004837179,BU18110001627831,BU18120001142444,BU18080004837137,BU18080004837832,BU18080004837223,BU18080004837887,BU18080004837166,BU18080004837868,BU18080004837484,BU18080004837914,BU18120001119033,BU18120001119260,BU18120001118963,BU18120001119220,BU18120001119166,BU18120001142189,BU18120001119207,BU18080004837877,BU18110001628000,BU18120001142627,BU19010004368465,BU19010004369816,BU19010004369633,BU19010004371046,BU19010004371823,BU19010004368855,BU19010004369900,BU19010004371772,BU19010004368359,BU19010004369514,BU19010004368496,BU19010004369021,BU19010004368424,BU19010004369454,BU19010004368572,BU19010004369276,BU19010004371234,BU19010004371579,BU19010004371199,BU19010004371427,BU19010004371315,BU19010004371706,BU19010004371350,BU19010004369323,BU19010004371155,BU19010004371849}'::text[])
114. 0.017 0.017 ↑ 1.9 52 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.005..0.017 rows=52 loops=1)

115. 0.208 0.208 ↑ 1.0 1 52

Index Scan using ix_dim_budgets_budget_id on dim_budgets xbu (cost=0.28..1.05 rows=1 width=45) (actual time=0.004..0.004 rows=1 loops=52)

  • Index Cond: (budget_id = (unnest('{BU18070005461443,BU18110001627699,BU18080004837817,BU18110001627905,BU18120001142583,BU18080004837179,BU18110001627831,BU18120001142444,BU18080004837137,BU18080004837832,BU18080004837223,BU18080004837887,BU18080004837166,BU18080004837868,BU18080004837484,BU18080004837914,BU18120001119033,BU18120001119260,BU18120001118963,BU18120001119220,BU18120001119166,BU18120001142189,BU18120001119207,BU18080004837877,BU18110001628000,BU18120001142627,BU19010004368465,BU19010004369816,BU19010004369633,BU19010004371046,BU19010004371823,BU19010004368855,BU19010004369900,BU19010004371772,BU19010004368359,BU19010004369514,BU19010004368496,BU19010004369021,BU19010004368424,BU19010004369454,BU19010004368572,BU19010004369276,BU19010004371234,BU19010004371579,BU19010004371199,BU19010004371427,BU19010004371315,BU19010004371706,BU19010004371350,BU19010004369323,BU19010004371155,BU19010004371849}'::text[])))
116. 1.444 1.444 ↑ 1.0 1 361

Index Scan using pk_dim_carts_orders on dim_carts_orders xco (cost=0.43..3.70 rows=1 width=71) (actual time=0.004..0.004 rows=1 loops=361)

  • Index Cond: (sk_cart_id = xfj.sk_cart_id)
117. 1.444 1.444 ↑ 1.0 1 361

Index Scan using pk_dim_funds on dim_funds xfu (cost=0.42..2.37 rows=1 width=89) (actual time=0.003..0.004 rows=1 loops=361)

  • Index Cond: (sk_fund = xfj.sk_fund)
118. 0.000 0.000 ↓ 0.0 0 361

Index Scan using ix_dim_funds_fund_id on dim_funds yfu (cost=0.42..0.56 rows=1 width=82) (actual time=0.000..0.000 rows=0 loops=361)

  • Index Cond: (fund_id = xfu.source_platform_fund_id)
119. 1.444 1.444 ↑ 1.0 1 361

Index Scan using pk_dim_accounts on dim_accounts xac (cost=0.42..1.85 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=361)

  • Index Cond: (sk_account_id = xfj.sk_account_id)
  • Filter: (is_row_current = 'YES'::text)
120. 1.805 1.805 ↑ 1.0 1 361

Index Scan using ix_rel_master_acct_account_id on dim_rel_masters_accounts xrm (cost=0.42..0.50 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=361)

  • Index Cond: (account_id = xac.account_id)
  • Filter: ((is_row_current = 'YES'::text) AND (master_account_id = 'AC18030272818048'::text))
121. 3.610 3.610 ↑ 2.0 1 361

Index Scan using ix_dim_contacts_account_id on dim_contacts xct (cost=0.42..0.64 rows=2 width=42) (actual time=0.006..0.010 rows=1 loops=361)

  • Index Cond: (account_id = xac.account_id)
  • Filter: ((contact_type = 'Primary'::text) AND (row_status = 'active'::text))
  • Rows Removed by Filter: 6
122. 0.002 0.340 ↑ 417.0 1 1

Hash (cost=45.43..45.43 rows=417 width=22) (actual time=0.340..0.340 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
123. 0.137 0.338 ↑ 417.0 1 1

Hash Semi Join (cost=3.03..45.43 rows=417 width=22) (actual time=0.299..0.338 rows=1 loops=1)

  • Hash Cond: (xfc.fund_class_id = (unnest('{FC18080008155507,FC18080008155507}'::text[])))
124. 0.184 0.184 ↓ 1.0 437 1

Index Scan using pk_dim_fund_classes on dim_fund_classes xfc (cost=0.27..36.94 rows=417 width=38) (actual time=0.018..0.184 rows=437 loops=1)

125. 0.005 0.017 ↑ 50.0 2 1

Hash (cost=1.51..1.51 rows=100 width=32) (actual time=0.017..0.017 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.012 0.012 ↑ 50.0 2 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.011..0.012 rows=2 loops=1)

127. 0.697 7.220 ↑ 33.0 1 361

Hash Join (cost=373.21..378.68 rows=33 width=83) (actual time=0.020..0.020 rows=1 loops=361)

  • Hash Cond: (yos.sk_order_status = xst.sk_order_status)
128. 3.971 6.498 ↑ 179.0 1 361

HashAggregate (cost=366.16..368.85 rows=179 width=80) (actual time=0.017..0.018 rows=1 loops=361)

  • Group Key: yos.sk_cart_id, yos.sk_order_status
129. 2.527 2.527 ↑ 35.8 5 361

Index Scan using ix_fact_carts_orders_items_sk_cart_id on fact_carts_orders_items yos (cost=0.43..362.58 rows=179 width=32) (actual time=0.004..0.007 rows=5 loops=361)

  • Index Cond: (sk_cart_id = xfj.sk_cart_id)
130. 0.011 0.025 ↑ 1.0 33 1

Hash (cost=6.63..6.63 rows=33 width=15) (actual time=0.025..0.025 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
131. 0.014 0.014 ↑ 1.0 33 1

Index Scan using pk_dim_order_statuses on dim_order_statuses xst (cost=0.14..6.63 rows=33 width=15) (actual time=0.007..0.014 rows=33 loops=1)

132. 1.805 1.805 ↑ 1.0 1 361

Index Scan using pk_rel_master_accts_tp_values_pivot on rel_master_accts_tp_values_pivot xtp (cost=0.42..0.46 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=361)

  • Index Cond: ((master_account_id = 'AC18030272818048'::text) AND (account_id = xac.account_id))
133. 6.843 16.606 ↑ 44.0 1 361

Hash Join (cost=4.60..15.31 rows=44 width=35) (actual time=0.006..0.046 rows=1 loops=361)

  • Hash Cond: (yfj.sk_type_entry = yte.sk_type_entry)
134. 9.747 9.747 ↑ 4.4 101 361

Index Scan using ix_fact_fund_journal_sk_fund on fact_fund_journal yfj (cost=0.43..9.06 rows=440 width=39) (actual time=0.005..0.027 rows=101 loops=361)

  • Index Cond: (sk_fund = COALESCE(yfu.sk_fund, xfu.sk_fund))
135. 0.005 0.016 ↑ 1.0 1 1

Hash (cost=4.15..4.15 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
136. 0.011 0.011 ↑ 1.0 1 1

Index Scan using uc_dim_fund_journal_type_entry on dim_fund_journal_type_entry yte (cost=0.14..4.15 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (type_entry = 'INITIAL'::text)
137. 5.776 21.299 ↑ 1.0 1 361

Aggregate (cost=809.01..809.02 rows=1 width=64) (actual time=0.059..0.059 rows=1 loops=361)

138.          

CTE cte_tags

139. 2.076 7.220 ↑ 20.0 5 361

Nested Loop (cost=2.05..404.51 rows=100 width=56) (actual time=0.008..0.020 rows=5 loops=361)

140. 0.722 1.444 ↑ 20.0 5 361

HashAggregate (cost=1.76..2.76 rows=100 width=4) (actual time=0.003..0.004 rows=5 loops=361)

  • Group Key: unnest($3)
141. 0.722 0.722 ↑ 20.0 5 361

Result (cost=0.00..0.51 rows=100 width=4) (actual time=0.001..0.002 rows=5 loops=361)

142. 3.700 3.700 ↑ 1.0 1 1,850

Index Scan using pk_dim_asset_inst_group_key on dim_asset_inst_group_key xtg (cost=0.29..4.01 rows=1 width=60) (actual time=0.002..0.002 rows=1 loops=1,850)

  • Index Cond: (sk_asset_inst_group_key = (unnest($3)))
143. 0.722 15.523 ↑ 833.3 24 361

Append (cost=0.00..304.50 rows=20,000 width=64) (actual time=0.012..0.043 rows=24 loops=361)

144. 12.274 12.274 ↑ 526.3 19 361

CTE Scan on cte_tags ctn (cost=0.00..52.25 rows=10,000 width=64) (actual time=0.012..0.034 rows=19 loops=361)

145. 2.527 2.527 ↑ 2,000.0 5 361

CTE Scan on cte_tags ctc (cost=0.00..52.25 rows=10,000 width=64) (actual time=0.002..0.007 rows=5 loops=361)

146. 0.361 1.083 ↓ 0.0 0 361

HashAggregate (cost=10.31..11.56 rows=100 width=292) (actual time=0.003..0.003 rows=0 loops=361)

  • Group Key: cte_1.sk_claim_id, cte_1.claim_id, cte_1.claim_name, cte_1.invoice_amount, cte_1.claim_status, cte_1.claimed_amount, cte_1.approved_amount, cte_1.invoice_number, cte_1.vendor_name, cte_1.submitted_date_ts, cte_1.sub_notes, cte_1.approved_date
147.          

CTE cte_claims

148. 0.722 0.722 ↓ 0.0 0 361

Index Scan using uc_dim_claims on dim_claims xcl (cost=0.29..4.81 rows=100 width=259) (actual time=0.002..0.002 rows=0 loops=361)

  • Index Cond: (claim_id = $6)
149. 0.722 0.722 ↓ 0.0 0 361

CTE Scan on cte_claims cte_1 (cost=0.00..2.00 rows=100 width=292) (actual time=0.002..0.002 rows=0 loops=361)

150. 6.013 32.366 ↓ 1.0 23,937 1

Hash (cost=1,790.34..1,790.34 rows=23,928 width=66) (actual time=32.366..32.366 rows=23,937 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1950kB
151. 10.281 26.353 ↓ 1.0 23,937 1

Hash Join (cost=0.42..1,790.34 rows=23,928 width=66) (actual time=0.030..26.353 rows=23,937 loops=1)

  • Hash Cond: (xpm.payment_type = cte.payment_type)
152. 16.060 16.060 ↓ 1.0 23,937 1

Index Scan using pk_fact_claim_payments on fact_claim_payments xpm (cost=0.29..1,461.20 rows=23,928 width=40) (actual time=0.011..16.060 rows=23,937 loops=1)

153. 0.003 0.012 ↑ 1.0 4 1

Hash (cost=0.08..0.08 rows=4 width=64) (actual time=0.012..0.012 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
154. 0.009 0.009 ↑ 1.0 4 1

CTE Scan on cte_payment_types cte (cost=0.00..0.08 rows=4 width=64) (actual time=0.003..0.009 rows=4 loops=1)

155. 0.019 0.145 ↑ 1.0 78 1

Hash (cost=1.56..1.56 rows=78 width=64) (actual time=0.145..0.145 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
156. 0.126 0.126 ↑ 1.0 78 1

CTE Scan on cte_activity_type_mapping atp (cost=0.00..1.56 rows=78 width=64) (actual time=0.003..0.126 rows=78 loops=1)