explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VdfS

Settings
# exclusive inclusive rows x rows loops node
1. 10.608 24,942.915 ↓ 380.5 761 1

Nested Loop Left Join (cost=342,001.33..346,104.62 rows=2 width=640) (actual time=5,071.798..24,942.915 rows=761 loops=1)

2. 2.470 24,647.693 ↓ 761.0 761 1

Nested Loop Left Join (cost=342,000.76..346,064.35 rows=1 width=495) (actual time=5,071.162..24,647.693 rows=761 loops=1)

3. 1.810 24,642.179 ↓ 761.0 761 1

Nested Loop Left Join (cost=342,000.62..346,064.19 rows=1 width=431) (actual time=5,071.148..24,642.179 rows=761 loops=1)

4. 1.817 24,637.325 ↓ 761.0 761 1

Nested Loop Left Join (cost=342,000.48..346,064.02 rows=1 width=415) (actual time=5,071.140..24,637.325 rows=761 loops=1)

5. 1.878 24,618.766 ↓ 761.0 761 1

Nested Loop Left Join (cost=342,000.05..346,063.54 rows=1 width=420) (actual time=5,071.106..24,618.766 rows=761 loops=1)

6. 1.942 24,613.844 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,999.92..346,063.34 rows=1 width=354) (actual time=5,071.088..24,613.844 rows=761 loops=1)

7. 2.663 24,603.531 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,999.77..346,063.16 rows=1 width=342) (actual time=5,071.077..24,603.531 rows=761 loops=1)

8. 2.255 24,580.321 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,999.35..346,054.72 rows=1 width=326) (actual time=5,071.046..24,580.321 rows=761 loops=1)

9. 1.932 24,564.368 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,998.92..346,052.54 rows=1 width=307) (actual time=5,071.003..24,564.368 rows=761 loops=1)

10. 2.425 24,547.216 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,998.49..346,048.35 rows=1 width=276) (actual time=5,070.976..24,547.216 rows=761 loops=1)

11. 2.116 24,527.288 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,998.06..346,044.15 rows=1 width=239) (actual time=5,070.942..24,527.288 rows=761 loops=1)

12. 3.337 24,508.430 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,997.64..346,035.71 rows=1 width=223) (actual time=5,070.905..24,508.430 rows=761 loops=1)

13. 2,315.872 24,486.068 ↓ 761.0 761 1

Nested Loop Left Join (cost=341,997.22..346,027.25 rows=1 width=192) (actual time=5,070.878..24,486.068 rows=761 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))
  • Rows Removed by Join Filter: 4994508
14.          

CTE factset_fund_holdings

15. 2.325 2,167.781 ↓ 16.7 1,267 1

WindowAgg (cost=145,884.53..145,886.05 rows=76 width=46) (actual time=2,164.923..2,167.781 rows=1,267 loops=1)

16. 5.600 2,165.456 ↓ 16.7 1,267 1

Sort (cost=145,884.53..145,884.72 rows=76 width=54) (actual time=2,164.916..2,165.456 rows=1,267 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 226kB
17. 102.253 2,159.856 ↓ 16.7 1,267 1

Merge Left Join (cost=144,685.63..145,882.16 rows=76 width=54) (actual time=1,363.976..2,159.856 rows=1,267 loops=1)

  • Merge Cond: (own_fund_detail.factset_fund_id = own_fund_detail_1.factset_fund_id)
  • Join Filter: ((own_fund_detail_1.fsym_id = sar.fsym_id) AND (own_fund_detail_1.report_date = q4_fund_dates.report_date))
  • Rows Removed by Join Filter: 43625
18. 1.728 882.560 ↓ 16.7 1,267 1

Nested Loop (cost=72,774.57..73,254.15 rows=76 width=46) (actual time=579.494..882.560 rows=1,267 loops=1)

19. 2.916 879.565 ↓ 16.7 1,267 1

Nested Loop (cost=72,774.15..73,244.76 rows=76 width=46) (actual time=579.460..879.565 rows=1,267 loops=1)

  • Join Filter: (q4_fund_dates.factset_fund_id = ent.factset_fund_id)
20. 21.410 758.818 ↓ 9.5 1,267 1

Merge Join (cost=72,773.73..73,104.60 rows=134 width=55) (actual time=578.899..758.818 rows=1,267 loops=1)

  • Merge Cond: (own_fund_detail.factset_fund_id = q4_fund_dates.factset_fund_id)
21. 68.217 667.532 ↑ 2.4 7,978 1

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

22. 410.421 599.315 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 17356kB
23. 160.304 188.894 ↓ 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=31.530..188.894 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
24. 28.590 28.590 ↓ 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=28.589..28.590 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
25. 49.708 69.876 ↓ 33.2 7,929 1

Sort (cost=862.68..863.27 rows=239 width=13) (actual time=66.759..69.876 rows=7,929 loops=1)

  • Sort Key: q4_fund_dates.factset_fund_id
  • Sort Method: quicksort Memory: 564kB
26. 4.776 20.168 ↓ 33.2 7,930 1

Bitmap Heap Scan on q4_fund_dates (cost=10.88..853.23 rows=239 width=13) (actual time=15.414..20.168 rows=7,930 loops=1)

  • Recheck Cond: ((report_date >= '2019-05-30'::date) AND (report_date <= '2019-06-06'::date))
  • Heap Blocks: exact=91
27. 15.392 15.392 ↓ 33.2 7,930 1

Bitmap Index Scan on q4_fund_dates_report_date_idx (cost=0.00..10.82 rows=239 width=0) (actual time=15.392..15.392 rows=7,930 loops=1)

  • Index Cond: ((report_date >= '2019-05-30'::date) AND (report_date <= '2019-06-06'::date))
28. 117.831 117.831 ↑ 1.0 1 1,267

Index Only Scan using own_ent_funds_factset_fund_id_current_report_date_active_flag_i on own_ent_funds ent (cost=0.42..1.03 rows=1 width=9) (actual time=0.086..0.093 rows=1 loops=1,267)

  • Index Cond: ((factset_fund_id = own_fund_detail.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 1760
29. 1.235 1.267 ↑ 1.0 1 1,267

Materialize (cost=0.42..8.44 rows=1 width=9) (actual time=0.000..0.001 rows=1 loops=1,267)

30. 0.032 0.032 ↑ 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.030..0.032 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
31. 137.926 1,175.043 ↓ 8.7 163,862 1

Materialize (cost=71,911.06..72,473.98 rows=18,760 width=30) (actual time=784.268..1,175.043 rows=163,862 loops=1)

32. 169.939 1,037.117 ↓ 7.6 143,498 1

Unique (cost=71,911.06..72,239.48 rows=18,760 width=54) (actual time=784.266..1,037.117 rows=143,498 loops=1)

33. 748.388 867.178 ↓ 7.6 143,498 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=784.264..867.178 rows=143,498 loops=1)

  • 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
  • Sort Method: quicksort Memory: 26319kB
34. 92.258 118.790 ↓ 7.6 143,507 1

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

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
35. 26.532 26.532 ↓ 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=26.532..26.532 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
36.          

CTE factset_prev_fund_holdings

37. 197.939 3,445.804 ↑ 2.1 83,932 1

WindowAgg (cost=191,234.31..196,111.17 rows=178,365 width=46) (actual time=2,900.990..3,445.804 rows=83,932 loops=1)

38. 217.486 3,247.865 ↑ 2.1 83,932 1

Merge Left Join (cost=191,234.31..192,989.79 rows=178,365 width=54) (actual time=2,900.981..3,247.865 rows=83,932 loops=1)

  • Merge Cond: ((own_fund_detail_2.factset_fund_id = adjh.factset_fund_id) AND (q4_fund_dates_1.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar_1.fsym_id)
39. 238.190 1,318.494 ↑ 2.1 83,932 1

Sort (cost=117,475.70..117,921.61 rows=178,365 width=46) (actual time=1,264.601..1,318.494 rows=83,932 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id, q4_fund_dates_1.report_date DESC
  • Sort Method: quicksort Memory: 14873kB
40. 77.829 1,080.304 ↑ 2.1 83,932 1

Nested Loop (cost=76,608.56..101,918.29 rows=178,365 width=46) (actual time=690.865..1,080.304 rows=83,932 loops=1)

  • Join Filter: (own_fund_detail_2.factset_fund_id = q4_fund_dates_1.factset_fund_id)
41. 4.133 834.175 ↑ 1.6 6,732 1

Nested Loop (cost=76,608.13..77,181.65 rows=10,686 width=51) (actual time=690.783..834.175 rows=6,732 loops=1)

42. 0.019 0.019 ↑ 1.0 1 1

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) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
43. 10.184 830.023 ↑ 1.6 6,732 1

Hash Join (cost=76,607.71..77,066.35 rows=10,686 width=51) (actual time=690.762..830.023 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail_2.factset_fund_id = ent_1.factset_fund_id)
44. 57.821 710.167 ↑ 2.4 7,978 1

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

45. 489.128 652.346 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 17356kB
46. 135.963 163.218 ↓ 7.6 143,507 1

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

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
47. 27.255 27.255 ↓ 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=27.255..27.255 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
48. 38.328 109.672 ↓ 1.0 68,142 1

Hash (cost=3,847.24..3,847.24 rows=67,953 width=9) (actual time=109.672..109.672 rows=68,142 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
49. 71.344 71.344 ↓ 1.0 68,142 1

Seq Scan on own_ent_funds ent_1 (cost=0.00..3,847.24 rows=67,953 width=9) (actual time=0.012..71.344 rows=68,142 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51182
50. 168.300 168.300 ↑ 1.4 12 6,732

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates q4_fund_dates_1 (cost=0.43..2.10 rows=17 width=13) (actual time=0.017..0.025 rows=12 loops=6,732)

  • Index Cond: ((factset_fund_id = ent_1.factset_fund_id) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Heap Fetches: 83932
51. 537.430 1,711.885 ↓ 7.6 143,507 1

Sort (cost=73,758.61..73,805.51 rows=18,760 width=30) (actual time=1,636.362..1,711.885 rows=143,507 loops=1)

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17356kB
52. 129.224 1,174.455 ↓ 7.6 143,507 1

Subquery Scan on adjh (cost=71,911.06..72,427.08 rows=18,760 width=30) (actual time=831.719..1,174.455 rows=143,507 loops=1)

53. 138.781 1,045.231 ↓ 7.6 143,507 1

Unique (cost=71,911.06..72,239.48 rows=18,760 width=54) (actual time=831.717..1,045.231 rows=143,507 loops=1)

54. 788.111 906.450 ↓ 7.6 143,507 1

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

  • 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
  • Sort Method: quicksort Memory: 26319kB
55. 90.784 118.339 ↓ 7.6 143,507 1

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

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
56. 27.555 27.555 ↓ 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=27.554..27.555 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
57. 168.857 2,648.263 ↓ 761.0 761 1

Nested Loop Left Join (cost=0.00..3.44 rows=1 width=112) (actual time=2,169.477..2,648.263 rows=761 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))
  • Rows Removed by Join Filter: 306280
58. 2,165.874 2,165.874 ↓ 761.0 761 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..1.71 rows=1 width=92) (actual time=2,164.933..2,165.874 rows=761 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 506
59. 313.532 313.532 ↓ 403.0 403 761

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..1.71 rows=1 width=92) (actual time=0.002..0.412 rows=403 loops=761)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 864
60. 19,521.933 19,521.933 ↓ 7.4 6,564 761

CTE Scan on factset_prev_fund_holdings (cost=0.00..4,013.21 rows=892 width=88) (actual time=3.813..25.653 rows=6,564 loops=761)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 77368
61. 19.025 19.025 ↑ 1.0 1 761

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) (actual time=0.025..0.025 rows=1 loops=761)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
62. 16.742 16.742 ↑ 1.0 1 761

Index Scan using q4_fund_values_pkey on q4_fund_values (cost=0.41..8.43 rows=1 width=26) (actual time=0.021..0.022 rows=1 loops=761)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
63. 17.503 17.503 ↑ 1.0 1 761

Index Scan using sym_entity_pkey on sym_entity inst_entity (cost=0.43..4.19 rows=1 width=37) (actual time=0.023..0.023 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
64. 15.220 15.220 ↑ 1.0 1 761

Index Scan using sym_entity_pkey on sym_entity fund_entity (cost=0.43..4.19 rows=1 width=40) (actual time=0.019..0.020 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
65. 13.698 13.698 ↑ 1.0 1 761

Index Scan using h_entity_pkey on h_entity (cost=0.43..2.16 rows=1 width=37) (actual time=0.017..0.018 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
66. 20.547 20.547 ↑ 1.0 1 761

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..8.44 rows=1 width=26) (actual time=0.027..0.027 rows=1 loops=761)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
67. 8.371 8.371 ↑ 1.0 1 761

Index Scan using country_map_pkey on country_map (cost=0.15..0.17 rows=1 width=15) (actual time=0.010..0.011 rows=1 loops=761)

  • Index Cond: (iso_country = fund_entity.iso_country)
68. 3.044 3.044 ↑ 1.0 1 761

Index Scan using region_map_pkey on region_map (cost=0.13..0.19 rows=1 width=76) (actual time=0.003..0.004 rows=1 loops=761)

  • Index Cond: (region_code = country_map.region_code)
69. 16.742 16.742 ↑ 1.0 1 761

Index Scan using ent_entity_metro_areas_pkey on ent_entity_metro_areas (cost=0.43..0.46 rows=1 width=13) (actual time=0.021..0.022 rows=1 loops=761)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
70. 3.044 3.044 ↑ 1.0 1 761

Index Scan using metro_map_pkey on metro_map (cost=0.14..0.16 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=761)

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
71. 3.044 3.044 ↑ 1.0 1 761

Index Scan using fund_type_map_pkey on fund_type_map (cost=0.14..0.16 rows=1 width=84) (actual time=0.004..0.004 rows=1 loops=761)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
72. 12.176 12.176 ↑ 2.0 1 761

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.38 rows=2 width=45) (actual time=0.016..0.016 rows=1 loops=761)

  • 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)))
73.          

SubPlan (forNested Loop Left Join)

74. 37.289 272.438 ↑ 1.0 1 761

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.357..0.358 rows=1 loops=761)

75. 7.011 235.149 ↓ 2.0 2 761

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.196..0.309 rows=2 loops=761)

76. 115.906 223.734 ↓ 2.0 2 761

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.187..0.294 rows=2 loops=761)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
77. 88.803 88.803 ↑ 1.0 270 759

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (actual time=0.003..0.117 rows=270 loops=759)

78. 3.044 19.025 ↓ 2.0 2 761

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.025..0.025 rows=2 loops=761)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 15.981 15.981 ↓ 2.0 2 761

Index Scan using ent_entity_address_factset_entity_id_idx on ent_entity_address (cost=0.43..8.45 rows=1 width=60) (actual time=0.018..0.021 rows=2 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
80. 4.404 4.404 ↑ 1.0 1 1,468

Index Scan using region_map_pkey on region_map region_map_1 (cost=0.13..0.19 rows=1 width=76) (actual time=0.003..0.003 rows=1 loops=1,468)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 35.759 ms
Execution time : 24,951.570 ms