explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qi5p

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

Gather Motion 60:1 (slice17; segments: 60) (cost=0.00..4,060,056.43 rows=48,703 width=60) (actual rows= loops=)

  • Merge Key: eq_beta_ticker_d2.ticker, eq_beta_ticker_index_d2.is_absolute, eq_beta_ticker_index_d2.eq_beta_ticker_index_id, eq_beta_vol_f.element, eq_beta_index_d2.index_type
2. 0.000 0.000 ↓ 0.0

Sort (cost=0.00..4,060,049.78 rows=812 width=60) (actual rows= loops=)

  • Sort Key: eq_beta_ticker_d2.ticker, eq_beta_ticker_index_d2.is_absolute, eq_beta_ticker_index_d2.eq_beta_ticker_index_id, eq_beta_vol_f.element, eq_beta_index_d2.index_type
3. 0.000 0.000 ↓ 0.0

Sequence (cost=0.00..4,060,047.11 rows=812 width=60) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 17:0) (cost=0.00..0.00 rows=1 width=1) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..0.00 rows=1 width=1) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

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

7. 0.000 0.000 ↓ 0.0

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

  • One-Time Filter: (gp_execution_segment() = 35)
8. 0.000 0.000 ↓ 0.0

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

9. 0.000 0.000 ↓ 0.0

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

10. 0.000 0.000 ↓ 0.0

Sequence (cost=0.00..4,060,047.06 rows=812 width=60) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 17:3) (cost=0.00..2,731,471.81 rows=11 width=1) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2,731,471.81 rows=11 width=1) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.00..2,731,471.81 rows=11 width=13) (actual rows= loops=)

  • Group Key: market_data_set_d2.md_set_cd
14. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice16; segments: 60) (cost=0.00..2,731,471.81 rows=11 width=13) (actual rows= loops=)

  • Hash Key: market_data_set_d2.md_set_cd
15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.00..2,731,471.81 rows=11 width=13) (actual rows= loops=)

  • Group Key: market_data_set_d2.md_set_cd
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..2,731,448.05 rows=189,914 width=13) (actual rows= loops=)

  • Hash Cond: (md_value_f.md_point_id = md_point_d2.md_point_id)
  • Join Filter: ((md_value_f.valid_from_dt <= timestamp_in(unknownout(share0_ref2.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref2.valid_dt), 1114::oid, (-1)) < md_value_f.valid_to_dt) AND (times
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,353,707.69 rows=62,767,350 width=40) (actual rows= loops=)

  • Hash Cond: (md_value_f.dataset_id = (max(jobspec_d2.md_dataset_id)))
18. 0.000 0.000 ↓ 0.0

Dynamic Seq Scan on md_value_f (dynamic scan id: 1) (cost=0.00..3,468.94 rows=62,767,350 width=42) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=1,324,033.51..1,324,033.51 rows=1 width=2) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Broadcast Motion 1:60 (slice12) (cost=0.00..1,324,033.51 rows=60 width=2) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.00..1,324,033.51 rows=1 width=2) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Gather Motion 60:1 (slice11; segments: 60) (cost=0.00..1,324,033.51 rows=1 width=2) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1,324,033.51 rows=1 width=2) (actual rows= loops=)

  • Join Filter: ((jobspec_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref3.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref3.valid_dt), 1114::oid, (-1))
24. 0.000 0.000 ↓ 0.0

Seq Scan on jobspec_d2 (cost=0.00..431.00 rows=1 width=34) (actual rows= loops=)

  • Filter: ((jobspec_nm)::text = 'EquityHistoBetaVolCalc'::text)
25. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice10; segments: 60) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 10:0) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=100.00..100.00 rows=2 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Partition Selector for md_value_f (dynamic scan id: 1) (cost=10.00..100.00 rows=2 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice15; segments: 60) (cost=0.00..1,332,558.92 rows=6,199 width=37) (actual rows= loops=)

  • Hash Key: md_point_d2.md_point_id
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,332,558.20 rows=6,199 width=37) (actual rows= loops=)

  • Hash Cond: (md_point_d2.md_set_id = market_data_set_d2.md_set_id)
  • Join Filter: ((md_point_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref2.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref2.valid_dt), 1114::oid, (-1)) < md_point_
32. 0.000 0.000 ↓ 0.0

Seq Scan on md_point_d2 (cost=0.00..481.52 rows=596,457 width=44) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=1,331,595.73..1,331,595.73 rows=609 width=33) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice14; segments: 60) (cost=0.00..1,331,595.73 rows=609 width=33) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1,331,595.69 rows=11 width=33) (actual rows= loops=)

  • Join Filter: ((market_data_set_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref2.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref2.valid_dt), 111
36. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice13; segments: 60) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 13:0) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on market_data_set_d2 (cost=0.00..438.38 rows=38 width=49) (actual rows= loops=)

  • Filter: (((md_type)::text = 'VOL'::text) AND ((md_sub_type)::text = 'MKT'::text) AND ((query_type)::text = 'Equity.EquityVolQuery'::text))
39. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,328,575.21 rows=812 width=60) (actual rows= loops=)

  • Hash Cond: (replace((eq_beta_ticker_d2.ticker)::text, '_SYNTHETIC'::text, ''::text) = (share3_ref2.md_set_cd)::text)
40. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice8; segments: 60) (cost=0.00..1,327,712.70 rows=1,566 width=60) (actual rows= loops=)

  • Hash Key: replace((eq_beta_ticker_d2.ticker)::text, '_SYNTHETIC'::text, ''::text)
41. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,327,712.40 rows=1,566 width=60) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,327,712.40 rows=1,566 width=60) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,327,712.31 rows=1,242,951 width=64) (actual rows= loops=)

  • Hash Cond: ((eq_beta_ticker_d2.currency_id = (currency_d2_1.currency_id)::bigint) AND (eq_beta_ticker_d2.currency_id = (currency_d2_1.currency_id)::bigint))
  • Join Filter: ((currency_d2_1.valid_from_dt <= timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1)) < currency_d2_1.valid_to_dt) AND (curre
44. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,326,998.29 rows=5,805 width=84) (actual rows= loops=)

  • Hash Cond: (eq_beta_ticker_index_d2.eq_beta_ticker_id = eq_beta_ticker_d2.eq_beta_ticker_id)
  • Join Filter: ((eq_beta_ticker_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1)) < eq_beta_ticker_d2.valid_to_
45. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice5; segments: 60) (cost=0.00..1,326,549.04 rows=21,523 width=77) (actual rows= loops=)

  • Hash Key: eq_beta_ticker_index_d2.eq_beta_ticker_id
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,326,543.85 rows=21,523 width=77) (actual rows= loops=)

  • Hash Cond: ((eq_beta_vol_f.eq_beta_ticker_index_id = eq_beta_ticker_index_d2.eq_beta_ticker_index_id) AND (eq_beta_vol_f.valid_dt = pg_catalog.date_in(unknownout(share0_ref4.valid_dt), 1082::oid, (-1))
  • Join Filter: ((eq_beta_vol_f.audit_from_ts <= timestamp_in(unknownout(share0_ref4.audit_ts), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref4.audit_ts), 1114::oid, (-1)) < eq_beta_vol_f.audit
47. 0.000 0.000 ↓ 0.0

Seq Scan on eq_beta_vol_f (cost=0.00..480.99 rows=946,834 width=42) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=1,325,336.57..1,325,336.57 rows=26,037 width=63) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice4; segments: 60) (cost=0.00..1,325,336.57 rows=26,037 width=63) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,325,332.99 rows=434 width=63) (actual rows= loops=)

  • Hash Cond: (eq_beta_ticker_index_d2.eq_beta_index_id = eq_beta_index_d2.eq_beta_index_id)
  • Join Filter: ((eq_beta_ticker_index_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid,
51. 0.000 0.000 ↓ 0.0

Seq Scan on eq_beta_ticker_index_d2 (cost=0.00..537.74 rows=450,382 width=57) (actual rows= loops=)

  • Filter: (calib_set_id = 400004)
52. 0.000 0.000 ↓ 0.0

Hash (cost=1,324,466.41..1,324,466.41 rows=3 width=54) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice3; segments: 60) (cost=0.00..1,324,466.41 rows=3 width=54) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,324,466.41 rows=1 width=54) (actual rows= loops=)

  • Hash Cond: (((currency_d2.currency_id)::bigint = eq_beta_index_d2.currency_id) AND ((currency_d2.currency_id)::bigint = eq_beta_index_d2.currency_id))
  • Join Filter: ((currency_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(share0_ref4.valid_dt), 1114
55. 0.000 0.000 ↓ 0.0

Seq Scan on currency_d2 (cost=0.00..431.00 rows=4 width=38) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=1,324,035.39..1,324,035.39 rows=11 width=58) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice2; segments: 60) (cost=0.00..1,324,035.39 rows=11 width=58) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1,324,035.39 rows=1 width=58) (actual rows= loops=)

  • Join Filter: ((eq_beta_index_d2.valid_from_dt <= timestamp_in(unknownout(share0_ref4.valid_dt), 1114::oid, (-1))) AND (timestamp_in(unknownout(shar
59. 0.000 0.000 ↓ 0.0

Seq Scan on eq_beta_index_d2 (cost=0.00..431.00 rows=1 width=74) (actual rows= loops=)

  • Filter: ((index_type)::text = 'VOL'::text)
60. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice1; segments: 60) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 1:0) (cost=0.00..431.00 rows=1 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=431.10..431.10 rows=292 width=55) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice6; segments: 60) (cost=0.00..431.10 rows=292 width=55) (actual rows= loops=)

  • Hash Key: eq_beta_ticker_d2.eq_beta_ticker_id
65. 0.000 0.000 ↓ 0.0

Seq Scan on eq_beta_ticker_d2 (cost=0.00..431.01 rows=292 width=55) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=431.02..431.02 rows=195 width=38) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Broadcast Motion 60:60 (slice7; segments: 60) (cost=0.00..431.02 rows=195 width=38) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on currency_d2 currency_d2_1 (cost=0.00..431.00 rows=4 width=38) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=862.00..862.00 rows=21 width=13) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.00..862.00 rows=21 width=13) (actual rows= loops=)

  • Group Key: share3_ref2.md_set_cd
71. 0.000 0.000 ↓ 0.0

Append (cost=0.00..862.00 rows=21 width=13) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 17:3) (cost=0.00..431.00 rows=11 width=13) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Redistribute Motion 60:60 (slice9; segments: 60) (cost=0.00..431.00 rows=11 width=8) (actual rows= loops=)

  • Hash Key: (((CASE WHEN ((share3_ref3.md_set_cd)::text ~~ '%.OQ'::text) THEN replace((share3_ref3.md_set_cd)::text, '.OQ'::text, '.O'::text) WHEN ((share3_ref3.md_set_cd)::text ~~ '%.O'::text) THEN replace((share3_ref3.m
74. 0.000 0.000 ↓ 0.0

Result (cost=0.00..431.00 rows=11 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Result (cost=0.00..431.00 rows=11 width=8) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Shared Scan (share slice:id 9:3) (cost=0.00..431.00 rows=11 width=13) (actual rows= loops=)