explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GhWo

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 633.330 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,675.18..291,749.35 rows=2 width=640) (actual time=633.330..633.330 rows=0 loops=1)

2. 0.000 633.328 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,674.61..291,709.08 rows=1 width=495) (actual time=633.328..633.328 rows=0 loops=1)

3. 0.002 633.329 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,674.47..291,708.92 rows=1 width=431) (actual time=633.328..633.329 rows=0 loops=1)

4. 0.001 633.327 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,674.33..291,708.75 rows=1 width=415) (actual time=633.327..633.327 rows=0 loops=1)

5. 0.001 633.326 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,673.90..291,708.27 rows=1 width=420) (actual time=633.326..633.326 rows=0 loops=1)

6. 0.001 633.325 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,673.77..291,708.07 rows=1 width=354) (actual time=633.325..633.325 rows=0 loops=1)

7. 0.000 633.324 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,673.62..291,707.89 rows=1 width=342) (actual time=633.324..633.324 rows=0 loops=1)

8. 0.001 633.324 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,673.20..291,699.45 rows=1 width=326) (actual time=633.324..633.324 rows=0 loops=1)

9. 0.001 633.323 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,672.77..291,697.27 rows=1 width=307) (actual time=633.323..633.323 rows=0 loops=1)

10. 0.001 633.322 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,672.34..291,693.08 rows=1 width=276) (actual time=633.322..633.322 rows=0 loops=1)

11. 0.000 633.321 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,671.91..291,688.88 rows=1 width=239) (actual time=633.321..633.321 rows=0 loops=1)

12. 0.002 633.322 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,671.49..291,680.44 rows=1 width=223) (actual time=633.321..633.322 rows=0 loops=1)

13. 0.000 633.320 ↓ 0.0 0 1

Nested Loop Left Join (cost=291,671.07..291,671.98 rows=1 width=192) (actual time=633.320..633.320 rows=0 loops=1)

  • Join Filter: ((current_factset_fund_holdings.factset_fund_id = factset_prev_fund_holdings.factset_fund_id) AND (factset_prev_fund_holdings.fsym_id = current_factset_fund_holdings.fsym_id))
14.          

CTE factset_fund_holdings

15. 0.001 633.317 ↓ 0.0 0 1

WindowAgg (cost=145,835.28..145,835.54 rows=13 width=73) (actual time=633.317..633.317 rows=0 loops=1)

16.          

CTE distinct_funds

17. 38.023 482.766 ↑ 2.4 7,978 1

Unique (cost=71,911.06..72,004.89 rows=18,760 width=54) (actual time=403.505..482.766 rows=7,978 loops=1)

18. 235.499 444.743 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=403.503..444.743 rows=143,507 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 26319kB
19. 187.159 209.244 ↓ 7.6 143,507 1

Bitmap Heap Scan on own_fund_detail (cost=442.01..70,578.98 rows=18,767 width=54) (actual time=24.245..209.244 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
20. 22.085 22.085 ↓ 7.7 145,011 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (actual time=22.084..22.085 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
21. 0.025 633.316 ↓ 0.0 0 1

Sort (cost=73,830.38..73,830.42 rows=13 width=81) (actual time=633.315..633.316 rows=0 loops=1)

  • Sort Key: s.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.012 633.291 ↓ 0.0 0 1

Hash Right Join (cost=73,103.05..73,830.14 rows=13 width=81) (actual time=633.291..633.291 rows=0 loops=1)

  • Hash Cond: ((own_fund_detail_1.fsym_id = sar.fsym_id) AND (own_fund_detail_1.factset_fund_id = s.factset_fund_id) AND (own_fund_detail_1.report_date = q4_fund_dates.report_date))
23. 0.000 0.000 ↓ 0.0 0

Unique (cost=71,911.06..72,239.48 rows=18,760 width=54) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (never executed)

  • Sort Key: own_fund_detail_1.factset_fund_id, own_fund_detail_1.adj_holding, own_fund_detail_1.report_date, own_fund_detail_1.reported_holding, own_fund_detail_1.adj_mv, own_fund_detail_1.reported_mv
25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=442.01..70,578.98 rows=18,767 width=54) (never executed)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (never executed)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
27. 0.000 633.279 ↓ 0.0 0 1

Hash (cost=1,191.77..1,191.77 rows=13 width=73) (actual time=633.279..633.279 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.003 633.279 ↓ 0.0 0 1

Nested Loop (cost=1.26..1,191.77 rows=13 width=73) (actual time=633.278..633.279 rows=0 loops=1)

29. 0.094 0.094 ↑ 1.0 1 1

Index Only Scan using own_sec_coverage_fsym_id_idx on own_sec_coverage sar (cost=0.42..8.44 rows=1 width=9) (actual time=0.093..0.094 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
30. 6.896 633.182 ↓ 0.0 0 1

Nested Loop (cost=0.84..1,183.20 rows=13 width=100) (actual time=633.182..633.182 rows=0 loops=1)

  • Join Filter: (s.factset_fund_id = q4_fund_dates.factset_fund_id)
31. 5.522 565.698 ↓ 124.7 6,732 1

Nested Loop (cost=0.42..1,088.16 rows=54 width=105) (actual time=403.586..565.698 rows=6,732 loops=1)

32. 488.374 488.374 ↓ 84.9 7,978 1

CTE Scan on distinct_funds s (cost=0.00..422.10 rows=94 width=96) (actual time=403.517..488.374 rows=7,978 loops=1)

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
33. 71.802 71.802 ↑ 1.0 1 7,978

Index Only Scan using own_ent_funds_factset_fund_id_current_report_date_active_flag_i on own_ent_funds ent (cost=0.42..7.08 rows=1 width=9) (actual time=0.008..0.009 rows=1 loops=7,978)

  • Index Cond: ((factset_fund_id = s.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 5463
34. 60.588 60.588 ↓ 0.0 0 6,732

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates (cost=0.43..1.75 rows=1 width=13) (actual time=0.009..0.009 rows=0 loops=6,732)

  • Index Cond: ((factset_fund_id = ent.factset_fund_id) AND (report_date >= '2019-05-31'::date) AND (report_date <= '2019-06-07'::date) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Heap Fetches: 0
35.          

CTE factset_prev_fund_holdings

36. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=145,835.28..145,835.54 rows=13 width=73) (never executed)

37.          

CTE distinct_funds

38. 0.000 0.000 ↓ 0.0 0

Unique (cost=71,911.06..72,004.89 rows=18,760 width=54) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (never executed)

  • Sort Key: own_fund_detail_2.factset_fund_id
40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on own_fund_detail own_fund_detail_2 (cost=442.01..70,578.98 rows=18,767 width=54) (never executed)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (never executed)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
42. 0.000 0.000 ↓ 0.0 0

Sort (cost=73,830.38..73,830.42 rows=13 width=81) (never executed)

  • Sort Key: s_1.factset_fund_id, q4_fund_dates_1.report_date DESC
43. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=73,103.05..73,830.14 rows=13 width=81) (never executed)

  • Hash Cond: ((own_fund_detail_3.fsym_id = sar_1.fsym_id) AND (own_fund_detail_3.factset_fund_id = s_1.factset_fund_id) AND (own_fund_detail_3.report_date = q4_fund_dates_1.report_date))
44. 0.000 0.000 ↓ 0.0 0

Unique (cost=71,911.06..72,239.48 rows=18,760 width=54) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (never executed)

  • Sort Key: own_fund_detail_3.factset_fund_id, own_fund_detail_3.adj_holding, own_fund_detail_3.report_date, own_fund_detail_3.reported_holding, own_fund_detail_3.adj_mv, own_fund_detail_3.reported_mv
46. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on own_fund_detail own_fund_detail_3 (cost=442.01..70,578.98 rows=18,767 width=54) (never executed)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
47. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (never executed)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
48. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,191.77..1,191.77 rows=13 width=73) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.26..1,191.77 rows=13 width=73) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Index Only Scan using own_sec_coverage_fsym_id_idx on own_sec_coverage sar_1 (cost=0.42..8.44 rows=1 width=9) (never executed)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 0
51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..1,183.20 rows=13 width=100) (never executed)

  • Join Filter: (s_1.factset_fund_id = q4_fund_dates_1.factset_fund_id)
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..1,088.16 rows=54 width=105) (never executed)

53. 0.000 0.000 ↓ 0.0 0

CTE Scan on distinct_funds s_1 (cost=0.00..422.10 rows=94 width=96) (never executed)

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using own_ent_funds_factset_fund_id_current_report_date_active_flag_i on own_ent_funds ent_1 (cost=0.42..7.08 rows=1 width=9) (never executed)

  • Index Cond: ((factset_fund_id = s_1.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 0
55. 0.000 0.000 ↓ 0.0 0

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates q4_fund_dates_1 (cost=0.43..1.75 rows=1 width=13) (never executed)

  • Index Cond: ((factset_fund_id = ent_1.factset_fund_id) AND (report_date >= '2019-05-31'::date) AND (report_date <= '2019-06-07'::date) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Heap Fetches: 0
56. 0.002 633.320 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.60 rows=1 width=112) (actual time=633.320..633.320 rows=0 loops=1)

  • Join Filter: ((current_factset_fund_holdings.fsym_id = previous_factset_fund_holdings.fsym_id) AND (current_factset_fund_holdings.factset_fund_id = previous_factset_fund_holdings.factset_fund_id))
57. 633.318 633.318 ↓ 0.0 0 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.29 rows=1 width=92) (actual time=633.318..633.318 rows=0 loops=1)

  • Filter: (pos = 1)
58. 0.000 0.000 ↓ 0.0 0

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.29 rows=1 width=92) (never executed)

  • Filter: (pos = 2)
59. 0.000 0.000 ↓ 0.0 0

CTE Scan on factset_prev_fund_holdings (cost=0.00..0.29 rows=1 width=88) (never executed)

  • Filter: (pos = 1)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using own_ent_funds_factset_fund_id_current_report_date_active_flag_i on own_ent_funds (cost=0.42..8.44 rows=1 width=31) (never executed)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using q4_fund_values_pkey on q4_fund_values (cost=0.41..8.43 rows=1 width=26) (never executed)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using sym_entity_pkey on sym_entity inst_entity (cost=0.43..4.19 rows=1 width=37) (never executed)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using sym_entity_pkey on sym_entity fund_entity (cost=0.43..4.19 rows=1 width=40) (never executed)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using h_entity_pkey on h_entity (cost=0.43..2.16 rows=1 width=37) (never executed)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
65. 0.000 0.000 ↓ 0.0 0

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..8.44 rows=1 width=26) (never executed)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using country_map_pkey on country_map (cost=0.15..0.17 rows=1 width=15) (never executed)

  • Index Cond: (iso_country = fund_entity.iso_country)
67. 0.000 0.000 ↓ 0.0 0

Index Scan using region_map_pkey on region_map (cost=0.13..0.19 rows=1 width=76) (never executed)

  • Index Cond: (region_code = country_map.region_code)
68. 0.000 0.000 ↓ 0.0 0

Index Scan using ent_entity_metro_areas_pkey on ent_entity_metro_areas (cost=0.43..0.46 rows=1 width=13) (never executed)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
69. 0.000 0.000 ↓ 0.0 0

Index Scan using metro_map_pkey on metro_map (cost=0.14..0.16 rows=1 width=24) (never executed)

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using fund_type_map_pkey on fund_type_map (cost=0.14..0.16 rows=1 width=84) (never executed)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
71. 0.000 0.000 ↓ 0.0 0

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.38 rows=2 width=45) (never executed)

  • Index Cond: ((fsym_id = current_factset_fund_holdings.fsym_id) AND (price_date = (date_trunc('month'::text, (current_factset_fund_holdings.report_date)::timestamp with time zone) - '1 day'::interval)))
72.          

SubPlan (forNested Loop Left Join)

73. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=14.39..14.40 rows=1 width=32) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (never executed)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
76. 0.000 0.000 ↓ 0.0 0

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.45..8.45 rows=1 width=60) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Scan using ent_entity_address_factset_entity_id_idx on ent_entity_address (cost=0.43..8.45 rows=1 width=60) (never executed)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using region_map_pkey on region_map region_map_1 (cost=0.13..0.19 rows=1 width=76) (never executed)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 7.600 ms
Execution time : 635.542 ms