explain.depesz.com

A tool for finding a real cause for slow queries.

Result: vE6

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

Hash Left Join (cost=295783.21..346231.52 rows=86665 width=1364) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = lkp.entity_id)
2. 0.000 0.000 ↓ 0.0
→

Hash Right Join (cost=116833.39..157639.68 rows=86665 width=1332) (actual time=.. rows= loops=)

  • Hash Cond: ((company_txtinfo_tr.repno)::text = (cgt.repno)::text)
3. 0.000 0.000 ↓ 0.0
→

Seq Scan on company_txtinfo_tr (cost=0.00..19280.24 rows=88348 width=433) (actual time=.. rows= loops=)

  • Filter: ((info_type)::text = 'Business Summary'::text)
4. 0.000 0.000 ↓ 0.0
→

Hash (cost=105847.08..105847.08 rows=86665 width=911) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=95382.86..105847.08 rows=86665 width=911) (actual time=.. rows= loops=)

  • Hash Cond: ((cgt.repno)::text = (public.company_class_tr.repno)::text)
6. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=75895.65..86033.30 rows=86665 width=875) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ici.entity_id)::numeric)
7. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=75866.83..85571.14 rows=86665 width=843) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ici.entity_id)::numeric)
8. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=75842.06..85113.03 rows=86665 width=811) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ici.entity_id)::numeric)
9. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=75819.31..84656.95 rows=86665 width=779) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ici.entity_id)::numeric)
10. 0.000 0.000 ↓ 0.0
→

Hash Right Join (cost=75797.24..84201.54 rows=86665 width=747) (actual time=.. rows= loops=)

  • Hash Cond: ((public.company_class_tr.repno)::text = (cgt.repno)::text)
11. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=17997.32..18095.94 rows=9862 width=33) (actual time=.. rows= loops=)

12. 0.000 0.000 ↓ 0.0
→

Seq Scan on company_class_tr (cost=0.00..17544.03 rows=90659 width=33) (actual time=.. rows= loops=)

  • Filter: ((taxonomy)::text = 'MGINDUSTRY'::text)
13. 0.000 0.000 ↓ 0.0
→

Hash (cost=48845.61..48845.61 rows=86665 width=715) (actual time=.. rows= loops=)

14. 0.000 0.000 ↓ 0.0
→

Hash Right Join (cost=40786.13..48845.61 rows=86665 width=715) (actual time=.. rows= loops=)

  • Hash Cond: ((public.company_class_tr.repno)::text = (cgt.repno)::text)
15. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=17991.54..18088.90 rows=9736 width=33) (actual time=.. rows= loops=)

16. 0.000 0.000 ↓ 0.0
→

Seq Scan on company_class_tr (cost=0.00..17544.03 rows=89503 width=33) (actual time=.. rows= loops=)

  • Filter: ((taxonomy)::text = 'MGSECTOR'::text)
17. 0.000 0.000 ↓ 0.0
→

Hash (cost=14178.27..14178.27 rows=86665 width=683) (actual time=.. rows= loops=)

18. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=12187.47..14178.27 rows=86665 width=683) (actual time=.. rows= loops=)

  • Hash Cond: ((cgt.currency_reported)::bpchar = dim.code)
19. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=12181.37..13112.41 rows=86665 width=669) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ph.entity_id)::numeric)
20. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=12073.25..12570.90 rows=86665 width=649) (actual time=.. rows= loops=)

  • Hash Cond: (cgt.entity_id = (ndp.entity_id)::numeric)
21. 0.000 0.000 ↓ 0.0
→

Hash Right Join (cost=11941.29..12005.55 rows=86665 width=629) (actual time=.. rows= loops=)

  • Hash Cond: ((eil.new_entity_id)::numeric = cgt.entity_id)
22. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=10.22..40.17 rows=1400 width=426) (actual time=.. rows= loops=)

  • Hash Cond: (eil.old_entity_id = gd.entity_id)
23. 0.000 0.000 ↓ 0.0
→

Seq Scan on entity_id_lut eil (cost=0.00..24.00 rows=1400 width=16) (actual time=.. rows= loops=)

24. 0.000 0.000 ↓ 0.0
→

Hash (cost=10.10..10.10 rows=10 width=426) (actual time=.. rows= loops=)

25. 0.000 0.000 ↓ 0.0
→

Seq Scan on general_dump gd (cost=0.00..10.10 rows=10 width=426) (actual time=.. rows= loops=)

26. 0.000 0.000 ↓ 0.0
→

Hash (cost=10847.75..10847.75 rows=86665 width=211) (actual time=.. rows= loops=)

27. 0.000 0.000 ↓ 0.0
→

Seq Scan on company_geninfo_tr cgt (cost=0.00..10847.75 rows=86665 width=211) (actual time=.. rows= loops=)

  • Filter: ((lower((company_name)::text) !~~ '% fund%'::text) AND (lower((company_name)::text) !~~ '% etf%'::text) AND (lower((company_name)::text) !~~ '% fd%'::text))
28. 0.000 0.000 ↓ 0.0
→

Hash (cost=131.87..131.87 rows=7 width=28) (actual time=.. rows= loops=)

29. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=36.65..131.87 rows=7 width=28) (actual time=.. rows= loops=)

  • Hash Cond: ((ndp.entity_id = max_ndp.entity_id) AND (ndp.period_end_date = max_ndp.max_date))
30. 0.000 0.000 ↓ 0.0
→

Seq Scan on ninety_day_price ndp (cost=0.00..23.10 rows=1310 width=32) (actual time=.. rows= loops=)

31. 0.000 0.000 ↓ 0.0
→

Hash (cost=33.65..33.65 rows=200 width=12) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0
→

Subquery Scan on max_ndp (cost=29.65..33.65 rows=200 width=12) (actual time=.. rows= loops=)

33. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=29.65..31.65 rows=200 width=12) (actual time=.. rows= loops=)

34. 0.000 0.000 ↓ 0.0
→

Seq Scan on ninety_day_price (cost=0.00..23.10 rows=1310 width=12) (actual time=.. rows= loops=)

35. 0.000 0.000 ↓ 0.0
→

Hash (cost=108.05..108.05 rows=6 width=28) (actual time=.. rows= loops=)

36. 0.000 0.000 ↓ 0.0
→

Merge Join (cost=39.04..108.05 rows=6 width=28) (actual time=.. rows= loops=)

  • Merge Cond: ((ph.entity_id = max_ph.entity_id) AND (ph.price_date = max_ph.max_date))
37. 0.000 0.000 ↓ 0.0
→

Index Scan using price_hist_entity_id_price_date_idx on price_hist ph (cost=0.00..61.65 rows=1160 width=32) (actual time=.. rows= loops=)

38. 0.000 0.000 ↓ 0.0
→

Sort (cost=39.04..39.54 rows=200 width=12) (actual time=.. rows= loops=)

  • Sort Key: max_ph.entity_id, max_ph.max_date
39. 0.000 0.000 ↓ 0.0
→

Subquery Scan on max_ph (cost=27.40..31.40 rows=200 width=12) (actual time=.. rows= loops=)

40. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=27.40..29.40 rows=200 width=12) (actual time=.. rows= loops=)

41. 0.000 0.000 ↓ 0.0
→

Seq Scan on price_hist (cost=0.00..21.60 rows=1160 width=12) (actual time=.. rows= loops=)

42. 0.000 0.000 ↓ 0.0
→

Hash (cost=3.82..3.82 rows=182 width=18) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0
→

Seq Scan on dim_currencies dim (cost=0.00..3.82 rows=182 width=18) (actual time=.. rows= loops=)

44. 0.000 0.000 ↓ 0.0
→

Hash (cost=22.06..22.06 rows=1 width=40) (actual time=.. rows= loops=)

45. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=22.04..22.05 rows=1 width=524) (actual time=.. rows= loops=)

46. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=11.50..22.04 rows=1 width=524) (actual time=.. rows= loops=)

  • Hash Cond: ((ici.entity_id = t1.entity_id) AND (ici.revenue_share = t1.revenue_share))
47. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_industry ici (cost=0.00..10.30 rows=30 width=544) (actual time=.. rows= loops=)

48. 0.000 0.000 ↓ 0.0
→

Hash (cost=11.05..11.05 rows=30 width=40) (actual time=.. rows= loops=)

49. 0.000 0.000 ↓ 0.0
→

Subquery Scan on t1 (cost=10.45..11.05 rows=30 width=40) (actual time=.. rows= loops=)

50. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=10.45..10.75 rows=30 width=28) (actual time=.. rows= loops=)

51. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_industry (cost=0.00..10.30 rows=30 width=28) (actual time=.. rows= loops=)

52. 0.000 0.000 ↓ 0.0
→

Hash (cost=22.74..22.74 rows=1 width=40) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=22.72..22.73 rows=1 width=524) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=12.00..22.71 rows=1 width=524) (actual time=.. rows= loops=)

  • Hash Cond: ((ici.entity_id = t1.entity_id) AND (ici.revenue_share = t1.revenue_share))
55. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_group ici (cost=0.00..10.40 rows=40 width=544) (actual time=.. rows= loops=)

56. 0.000 0.000 ↓ 0.0
→

Hash (cost=11.40..11.40 rows=40 width=40) (actual time=.. rows= loops=)

57. 0.000 0.000 ↓ 0.0
→

Subquery Scan on t1 (cost=10.60..11.40 rows=40 width=40) (actual time=.. rows= loops=)

58. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=10.60..11.00 rows=40 width=28) (actual time=.. rows= loops=)

59. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_group (cost=0.00..10.40 rows=40 width=28) (actual time=.. rows= loops=)

60. 0.000 0.000 ↓ 0.0
→

Hash (cost=24.76..24.76 rows=1 width=40) (actual time=.. rows= loops=)

61. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=24.74..24.75 rows=1 width=524) (actual time=.. rows= loops=)

62. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=13.50..24.73 rows=1 width=524) (actual time=.. rows= loops=)

  • Hash Cond: ((ici.entity_id = t1.entity_id) AND (ici.revenue_share = t1.revenue_share))
63. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_subsector ici (cost=0.00..10.70 rows=70 width=544) (actual time=.. rows= loops=)

64. 0.000 0.000 ↓ 0.0
→

Hash (cost=12.45..12.45 rows=70 width=40) (actual time=.. rows= loops=)

65. 0.000 0.000 ↓ 0.0
→

Subquery Scan on t1 (cost=11.05..12.45 rows=70 width=40) (actual time=.. rows= loops=)

66. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=11.05..11.75 rows=70 width=28) (actual time=.. rows= loops=)

67. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_subsector (cost=0.00..10.70 rows=70 width=28) (actual time=.. rows= loops=)

68. 0.000 0.000 ↓ 0.0
→

Hash (cost=28.81..28.81 rows=1 width=40) (actual time=.. rows= loops=)

69. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=28.79..28.80 rows=1 width=524) (actual time=.. rows= loops=)

70. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=16.50..28.79 rows=1 width=524) (actual time=.. rows= loops=)

  • Hash Cond: ((ici.entity_id = t1.entity_id) AND (ici.revenue_share = t1.revenue_share))
71. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_sector ici (cost=0.00..11.30 rows=130 width=544) (actual time=.. rows= loops=)

72. 0.000 0.000 ↓ 0.0
→

Hash (cost=14.55..14.55 rows=130 width=40) (actual time=.. rows= loops=)

73. 0.000 0.000 ↓ 0.0
→

Subquery Scan on t1 (cost=11.95..14.55 rows=130 width=40) (actual time=.. rows= loops=)

74. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=11.95..13.25 rows=130 width=28) (actual time=.. rows= loops=)

75. 0.000 0.000 ↓ 0.0
→

Seq Scan on ibb_company_sector (cost=0.00..11.30 rows=130 width=28) (actual time=.. rows= loops=)

76. 0.000 0.000 ↓ 0.0
→

Hash (cost=19485.13..19485.13 rows=166 width=42) (actual time=.. rows= loops=)

77. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=19481.81..19483.47 rows=166 width=37) (actual time=.. rows= loops=)

78. 0.000 0.000 ↓ 0.0
→

Seq Scan on company_class_tr (cost=0.00..19470.43 rows=1518 width=37) (actual time=.. rows= loops=)

  • Filter: (((taxonomy)::text = 'SIC1987'::text) AND (code_order = 0))
79. 0.000 0.000 ↓ 0.0
→

Hash (cost=178949.12..178949.12 rows=56 width=41) (actual time=.. rows= loops=)

80. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=99852.07..178949.12 rows=56 width=41) (actual time=.. rows= loops=)

81. 0.000 0.000 ↓ 0.0
→

Nested Loop (cost=99852.07..178943.48 rows=1 width=41) (actual time=.. rows= loops=)

82. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=99852.07..178942.36 rows=1 width=29) (actual time=.. rows= loops=)

  • Hash Cond: (((bat.repno)::text = (lkp.repno)::text) AND (bat.statementdate = (max(btr.statementdate))) AND (bat.periodenddate = (max(btr.periodenddate))))
83. 0.000 0.000 ↓ 0.0
→

Seq Scan on balance_annual_tr bat (cost=0.00..51533.57 rows=918557 width=25) (actual time=.. rows= loops=)

84. 0.000 0.000 ↓ 0.0
→

Hash (cost=98256.95..98256.95 rows=91150 width=32) (actual time=.. rows= loops=)

85. 0.000 0.000 ↓ 0.0
→

Hash Join (cost=94610.95..98256.95 rows=91150 width=32) (actual time=.. rows= loops=)

  • Hash Cond: (lkp2.entity_id = lkp.entity_id)
86. 0.000 0.000 ↓ 0.0
→

HashAggregate (cost=87935.66..88847.16 rows=91150 width=17) (actual time=.. rows= loops=)

87. 0.000 0.000 ↓ 0.0
→

Hash Left Join (cost=6675.29..81080.93 rows=913964 width=17) (actual time=.. rows= loops=)

  • Hash Cond: ((btr.repno)::text = (lkp2.repno)::text)
88. 0.000 0.000 ↓ 0.0
→

Seq Scan on balance_annual_tr btr (cost=0.00..56126.35 rows=913964 width=14) (actual time=.. rows= loops=)

  • Filter: ((qtco + qtpo) <> 0::numeric)
89. 0.000 0.000 ↓ 0.0
→

Hash (cost=5535.92..5535.92 rows=91150 width=15) (actual time=.. rows= loops=)

90. 0.000 0.000 ↓ 0.0
→

Index Only Scan using company_geninfo_tr_pkey on company_geninfo_tr lkp2 (cost=0.00..5535.92 rows=91150 width=15) (actual time=.. rows= loops=)

91. 0.000 0.000 ↓ 0.0
→

Hash (cost=5535.92..5535.92 rows=91150 width=15) (actual time=.. rows= loops=)

92. 0.000 0.000 ↓ 0.0
→

Index Only Scan using company_geninfo_tr_pkey on company_geninfo_tr lkp (cost=0.00..5535.92 rows=91150 width=15) (actual time=.. rows= loops=)

93. 0.000 0.000 ↓ 0.0
→

Index Scan using company_geninfo_tr_pkey on company_geninfo_tr cgt (cost=0.00..1.11 rows=1 width=14) (actual time=.. rows= loops=)

  • Index Cond: (entity_id = lkp.entity_id)
94. 0.000 0.000 ↓ 0.0
→

Seq Scan on dim_currencies dim (cost=0.00..3.82 rows=182 width=0) (actual time=.. rows= loops=)