explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QAmr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on expo_fact (cost=45,709.68..45,709.70 rows=1 width=484) (actual rows= loops=)

2.          

CTE min_price_bucket

3. 0.000 0.000 ↓ 0.0

Unique (cost=501.83..501.85 rows=1 width=40) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=501.83..501.84 rows=1 width=40) (actual rows= loops=)

  • Sort Key: p.run_event_key, r.curve_key, (min(r.bucket_number))
5. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=501.80..501.82 rows=1 width=40) (actual rows= loops=)

  • Group Key: r.curve_key, p.run_event_key
6. 0.000 0.000 ↓ 0.0

Sort (cost=501.80..501.81 rows=1 width=12) (actual rows= loops=)

  • Sort Key: r.curve_key, p.run_event_key
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.89..501.79 rows=1 width=12) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.45..493.42 rows=1 width=12) (actual rows= loops=)

  • Hash Cond: (p.run_event_key = rbu.run_event_key)
9. 0.000 0.000 ↓ 0.0

Append (cost=0.00..482.41 rows=1,910 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on price_fact_p_20200514 p (cost=0.00..472.86 rows=1,910 width=16) (actual rows= loops=)

  • Filter: ((price_adjusted_value IS NOT NULL) AND (as_of_date_key = 20200514) AND (source_system_key = 2))
11. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=2 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu (cost=0.00..1.42 rows=2 width=8) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'RF_PRICE_FACT'::text))
14. 0.000 0.000 ↓ 0.0

Index Scan using risk_factor_bucket_dim_pkey on risk_factor_bucket_dim r (cost=0.43..8.36 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (risk_factor_bucket_key = p.risk_factor_bucket_key)
  • Filter: (p.curve_key = curve_key)
15.          

CTE expo_fact

16. 0.000 0.000 ↓ 0.0

Unique (cost=45,207.74..45,207.83 rows=1 width=452) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=45,207.74..45,207.75 rows=1 width=452) (actual rows= loops=)

  • Sort Key: f.run_event_key, f.source_system_key, f.exp_subbook_portfolio_key, f.exposure_header_key, f.risk_factor_bucket_key, f.counterparty_key, f.bp_legal_entity_key, f.std_uom_key, f.base_uom_key, f.uom_conversion_key, (trunc(f.exposure_delta, 8)), (trunc(f.exposure_gamma, 8)), (trunc(f.exposure_theta, 8)), (trunc(f.exposure_vega, 8)), (trunc(f.std_exposure_delta, 8)), (trunc(f.std_exposure_gamma, 8)), (trunc(f.std_exposure_theta, 8)), (trunc(f.std_exposure_vega, 8)), rf.rf_max_term, rf.curve_key, (trunc(cd.conversion_factor, 8)), f.exposure_attribs_key, t.exposure_header_key, t.exposure_attribs_key, t.risk_factor_bucket_key, t.counterparty_key, t.bp_legal_entity_key, f.vol_risk_factor_bucket_key, foo.max_term_key, rf.bucket_number, foo.max_bucket_number, rf3.rf_min_term, rf3.min_term_key, (COALESCE(f.exposure_period_key, 19000101))
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,101.57..45,207.73 rows=1 width=452) (actual rows= loops=)

  • Join Filter: ((f.exp_subbook_portfolio_key = t.portfolio_parent_key) AND (CASE WHEN (t.filter_pass_flag = 'Y'::bpchar) THEN f.exposure_header_key ELSE t.exposure_header_key END = t.exposure_header_key) AND (CASE WHEN (t.filter_pass_flag = 'Y'::bpchar) THEN f.counterparty_key ELSE t.counterparty_key END = t.counterparty_key) AND (CASE WHEN (t.filter_pass_flag = 'Y'::bpchar) THEN f.bp_legal_entity_key ELSE t.bp_legal_entity_key END = t.bp_legal_entity_key) AND (CASE WHEN (t.filter_pass_flag = 'Y'::bpchar) THEN f.risk_factor_bucket_key ELSE t.risk_factor_bucket_key END = t.risk_factor_bucket_key) AND (CASE WHEN (t.filter_pass_flag = 'Y'::bpchar) THEN f.exposure_attribs_key ELSE t.exposure_attribs_key END = t.exposure_attribs_key))
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,101.57..1,289.28 rows=1 width=200) (actual rows= loops=)

  • Join Filter: ((b.business_unit_name)::text = (p_1.business_unit_name)::text)
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,101.15..1,280.83 rows=1 width=211) (actual rows= loops=)

  • Join Filter: (rbu_1.business_unit_key = b.business_unit_key)
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,101.15..1,279.72 rows=1 width=204) (actual rows= loops=)

  • Join Filter: (f.run_event_key = rbu_1.run_event_key)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,101.15..1,278.26 rows=1 width=200) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,100.73..1,269.82 rows=1 width=192) (actual rows= loops=)

  • Join Filter: ((f.as_of_date_key = rbu1_1.as_of_date_key) AND (rb1.business_unit_key = rbu1_1.business_unit_key) AND (rbu1_1.run_event_key = f.run_event_key))
24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,100.73..1,268.36 rows=1 width=196) (actual rows= loops=)

  • Join Filter: ((f.as_of_date_key = rb1.as_of_date_key) AND (rb1.run_event_key = foo.run_event_key))
25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,100.73..1,266.90 rows=1 width=196) (actual rows= loops=)

  • Join Filter: ((f.as_of_date_key = foo.as_of_date_key) AND (rf.curve_key = foo.curve_key))
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=19.19..185.28 rows=1 width=156) (actual rows= loops=)

  • Join Filter: (rf3.curve_key = rf.curve_key)
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=18.75..168.35 rows=2 width=148) (actual rows= loops=)

  • Join Filter: ((SubPlan 2) = rb.business_unit_key)
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=18.75..78.80 rows=30 width=152) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Merge Append (cost=0.43..60.08 rows=30 width=108) (actual rows= loops=)

  • Sort Key: f.exp_subbook_portfolio_key
30. 0.000 0.000 ↓ 0.0

Index Scan using exposure_fact_p_20200514_as_of_date_key_chain_portfolio_key_idx on exposure_fact_p_20200514 f (cost=0.42..59.77 rows=30 width=108) (actual rows= loops=)

  • Index Cond: ((as_of_date_key = 20200514) AND (chain_portfolio_key = 3083822))
31. 0.000 0.000 ↓ 0.0

Materialize (cost=18.32..18.35 rows=1 width=48) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Subquery Scan on rf3 (cost=18.32..18.35 rows=1 width=48) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Unique (cost=18.32..18.34 rows=1 width=48) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=18.32..18.33 rows=1 width=48) (actual rows= loops=)

  • Sort Key: r_1.risk_factor_bucket_key, r_1.curve_key, mpb.rf_min_term, p_2.run_event_key
35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.72..18.31 rows=1 width=48) (actual rows= loops=)

  • Join Filter: (mpb.rf_min_term = r_1.bucket_number)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..10.14 rows=1 width=52) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on min_price_bucket mpb (cost=0.00..0.02 rows=1 width=40) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Append (cost=0.29..10.10 rows=2 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using price_fact_p_20200514_pkey on price_fact_p_20200514 p_2 (cost=0.29..10.09 rows=2 width=16) (actual rows= loops=)

  • Index Cond: ((as_of_date_key = 20200514) AND (run_event_key = mpb.run_event_key) AND (source_system_key = 2) AND (curve_key = mpb.curve_key))
40. 0.000 0.000 ↓ 0.0

Index Scan using risk_factor_bucket_dim_pkey on risk_factor_bucket_dim r_1 (cost=0.43..8.16 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (risk_factor_bucket_key = p_2.risk_factor_bucket_key)
  • Filter: (p_2.curve_key = curve_key)
41. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.48 rows=1 width=12) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rb (cost=0.00..1.48 rows=1 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'RF_PRICE_FACT'::text) AND (rf3.run_event_key = run_event_key))
43.          

SubPlan (for Nested Loop)

44. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.49 rows=1 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1.48 rows=1 width=4) (actual rows= loops=)

  • One-Time Filter: (f.as_of_date_key = 20200514)
46. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu1 (cost=0.00..1.48 rows=1 width=4) (actual rows= loops=)

  • Filter: ((run_event_key = f.run_event_key) AND (as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'EXPOSURE_FACT'::text))
47. 0.000 0.000 ↓ 0.0

Index Scan using risk_factor_bucket_dim_pkey on risk_factor_bucket_dim rf (cost=0.43..8.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (risk_factor_bucket_key = f.risk_factor_bucket_key)
48. 0.000 0.000 ↓ 0.0

Subquery Scan on foo (cost=1,081.54..1,081.60 rows=1 width=48) (actual rows= loops=)

  • Filter: (foo.rnk = 1)
49. 0.000 0.000 ↓ 0.0

Unique (cost=1,081.54..1,081.58 rows=2 width=56) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=1,081.54..1,081.55 rows=2 width=56) (actual rows= loops=)

  • Sort Key: r_2.bucket_number, (row_number() OVER (?)), r_2.risk_factor_bucket_key, r_2.curve_key, p_3.as_of_date_key, p_3.run_event_key
51. 0.000 0.000 ↓ 0.0

Append (cost=540.67..1,081.53 rows=2 width=56) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

WindowAgg (cost=540.67..540.70 rows=1 width=28) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Sort (cost=540.67..540.67 rows=1 width=20) (actual rows= loops=)

  • Sort Key: r_2.curve_key, p_3.run_event_key, r_2.bucket_number DESC
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.18..540.66 rows=1 width=20) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.89..540.35 rows=1 width=20) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.45..532.19 rows=1 width=12) (actual rows= loops=)

  • Hash Cond: (p_3.run_event_key = rbu_2.run_event_key)
57. 0.000 0.000 ↓ 0.0

Append (cost=0.00..501.79 rows=5,786 width=12) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on price_fact_p_20200514 p_3 (cost=0.00..472.86 rows=5,786 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (source_system_key = 2))
59. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=2 width=8) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu_2 (cost=0.00..1.42 rows=2 width=8) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'RF_PRICE_FACT'::text))
62. 0.000 0.000 ↓ 0.0

Index Scan using risk_factor_bucket_dim_pkey on risk_factor_bucket_dim r_2 (cost=0.43..8.16 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (risk_factor_bucket_key = p_3.risk_factor_bucket_key)
63. 0.000 0.000 ↓ 0.0

Index Scan using curve_dim_pkey on curve_dim cd_1 (cost=0.29..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (curve_key = r_2.curve_key)
  • Filter: ((curve_type_cd)::text = 'FX'::text)
64. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=540.77..540.81 rows=1 width=56) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

WindowAgg (cost=540.77..540.80 rows=1 width=32) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=540.77..540.78 rows=1 width=24) (actual rows= loops=)

  • Sort Key: r_3.curve_key, p_4.run_event_key, r_3.bucket_number
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.18..540.76 rows=1 width=24) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.89..540.35 rows=1 width=24) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.45..532.19 rows=1 width=12) (actual rows= loops=)

  • Hash Cond: (p_4.run_event_key = rbu_3.run_event_key)
70. 0.000 0.000 ↓ 0.0

Append (cost=0.00..501.79 rows=5,786 width=12) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Seq Scan on price_fact_p_20200514 p_4 (cost=0.00..472.86 rows=5,786 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (source_system_key = 2))
72. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=2 width=8) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu_3 (cost=0.00..1.42 rows=2 width=8) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'RF_PRICE_FACT'::text))
75. 0.000 0.000 ↓ 0.0

Index Scan using risk_factor_bucket_dim_pkey on risk_factor_bucket_dim r_3 (cost=0.43..8.17 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (risk_factor_bucket_key = p_4.risk_factor_bucket_key)
  • Filter: (bucket_number = (rf_max_term)::numeric)
76. 0.000 0.000 ↓ 0.0

Index Scan using curve_dim_pkey on curve_dim cd_2 (cost=0.29..0.41 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (curve_key = r_3.curve_key)
  • Filter: ((curve_type_cd)::text <> 'FX'::text)
77. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=12) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rb1 (cost=0.00..1.42 rows=2 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'RF_PRICE_FACT'::text))
79. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=12) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu1_1 (cost=0.00..1.42 rows=2 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'EXPOSURE_FACT'::text))
81. 0.000 0.000 ↓ 0.0

Index Scan using uom_conversion_dim_pkey on uom_conversion_dim cd (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (uom_conversion_key = f.uom_conversion_key)
82. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1.43 rows=2 width=12) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Seq Scan on run_event_bu_bridge_p_20200514 rbu_1 (cost=0.00..1.42 rows=2 width=12) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (bu_current_flag = 'Y'::bpchar) AND ((fact_name)::text = 'EXPOSURE_FACT'::text))
84. 0.000 0.000 ↓ 0.0

Seq Scan on business_unit_dim b (cost=0.00..1.05 rows=5 width=15) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Index Scan using portfolio_dim_x01 on portfolio_dim p_1 (cost=0.42..8.44 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (portfolio_key = 3083822)
86. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..43,886.25 rows=858 width=34) (actual rows= loops=)

  • Workers Planned: 2
87. 0.000 0.000 ↓ 0.0

Parallel Append (cost=0.00..42,800.45 rows=358 width=34) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on hier_final_output_sp_p_20200514 t (cost=0.00..42,798.66 rows=358 width=34) (actual rows= loops=)

  • Filter: ((as_of_date_key = 20200514) AND (portfolio_key = 3083822))