explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bcxj : new

Settings
# exclusive inclusive rows x rows loops node
1. 8.163 18,027.061 ↓ 379.0 758 1

Nested Loop Left Join (cost=294,439.81..294,521.01 rows=2 width=640) (actual time=5,355.149..18,027.061 rows=758 loops=1)

2. 1.946 17,811.206 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,439.24..294,480.75 rows=1 width=495) (actual time=5,354.821..17,811.206 rows=758 loops=1)

3. 1.761 17,806.986 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,439.10..294,480.58 rows=1 width=431) (actual time=5,354.813..17,806.986 rows=758 loops=1)

4. 1.728 17,802.951 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,438.96..294,480.41 rows=1 width=415) (actual time=5,354.805..17,802.951 rows=758 loops=1)

5. 1.581 17,791.369 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,438.53..294,479.94 rows=1 width=420) (actual time=5,354.790..17,791.369 rows=758 loops=1)

6. 1.999 17,787.514 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,438.40..294,479.73 rows=1 width=354) (actual time=5,354.782..17,787.514 rows=758 loops=1)

7. 1.830 17,782.483 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,438.25..294,479.56 rows=1 width=342) (actual time=5,354.773..17,782.483 rows=758 loops=1)

8. 1.705 17,771.557 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,437.83..294,471.11 rows=1 width=326) (actual time=5,354.757..17,771.557 rows=758 loops=1)

9. 2.095 17,752.418 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,437.40..294,468.94 rows=1 width=307) (actual time=5,354.740..17,752.418 rows=758 loops=1)

10. 1.628 17,740.469 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,436.97..294,464.74 rows=1 width=276) (actual time=5,354.724..17,740.469 rows=758 loops=1)

11. 1.355 17,728.229 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,436.54..294,460.54 rows=1 width=239) (actual time=5,354.707..17,728.229 rows=758 loops=1)

12. 2.615 17,717.778 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,436.12..294,452.10 rows=1 width=223) (actual time=5,354.691..17,717.778 rows=758 loops=1)

13. 789.011 17,703.793 ↓ 758.0 758 1

Nested Loop Left Join (cost=294,435.70..294,443.65 rows=1 width=192) (actual time=5,354.668..17,703.793 rows=758 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: 2257324
14.          

CTE factset_fund_holdings

15. 33.679 2,832.119 ↓ 863.0 863 1

Subquery Scan on current_qtr (cost=147,036.39..147,217.85 rows=1 width=73) (actual time=2,383.563..2,832.119 rows=863 loops=1)

  • Filter: ((current_qtr.report_date >= '2019-05-31'::date) AND (current_qtr.report_date <= '2019-06-07'::date))
  • Rows Removed by Filter: 96115
16. 148.897 2,798.440 ↓ 98.7 96,978 1

WindowAgg (cost=147,036.39..147,203.11 rows=983 width=73) (actual time=2,383.170..2,798.440 rows=96,978 loops=1)

17.          

CTE distinct_funds

18. 54.423 409.462 ↑ 2.4 7,978 1

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

19. 274.616 355.039 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 26319kB
20. 62.789 80.423 ↓ 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=19.806..80.423 rows=143,507 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
22. 170.543 2,649.543 ↓ 98.7 96,978 1

Merge Left Join (cost=75,031.50..75,181.01 rows=983 width=81) (actual time=2,383.161..2,649.543 rows=96,978 loops=1)

  • Merge Cond: ((s.factset_fund_id = adjh.factset_fund_id) AND (q4_fund_dates.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
23. 346.881 1,086.335 ↓ 98.7 96,978 1

Sort (cost=1,272.90..1,275.35 rows=983 width=73) (actual time=1,045.292..1,086.335 rows=96,978 loops=1)

  • Sort Key: s.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 16703kB
24. 78.063 739.454 ↓ 98.7 96,978 1

Nested Loop (cost=1.26..1,224.04 rows=983 width=73) (actual time=298.139..739.454 rows=96,978 loops=1)

  • Join Filter: (s.factset_fund_id = q4_fund_dates.factset_fund_id)
25. 4.622 520.019 ↓ 124.7 6,732 1

Nested Loop (cost=0.84..1,097.14 rows=54 width=78) (actual time=298.115..520.019 rows=6,732 loops=1)

26. 0.016 0.016 ↑ 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.015..0.016 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
27. 10.125 515.381 ↓ 124.7 6,732 1

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

28. 417.498 417.498 ↓ 84.9 7,978 1

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

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
29. 87.758 87.758 ↑ 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.010..0.011 rows=1 loops=7,978)

  • Index Cond: ((factset_fund_id = s.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 5463
30. 141.372 141.372 ↑ 1.3 14 6,732

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates (cost=0.43..2.12 rows=18 width=13) (actual time=0.013..0.021 rows=14 loops=6,732)

  • Index Cond: ((factset_fund_id = ent.factset_fund_id) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Heap Fetches: 96978
31. 417.185 1,392.665 ↓ 7.6 143,507 1

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

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17356kB
32. 111.234 975.480 ↓ 7.6 143,507 1

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

33. 128.546 864.246 ↓ 7.6 143,507 1

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

34. 619.353 735.700 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=669.717..735.700 rows=143,507 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
35. 89.745 116.347 ↓ 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.398..116.347 rows=143,507 loops=1)

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

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

CTE factset_prev_fund_holdings

38. 46.268 2,470.928 ↓ 240.5 83,932 1

Subquery Scan on prev_qtr (cost=147,036.39..147,217.85 rows=349 width=73) (actual time=2,123.255..2,470.928 rows=83,932 loops=1)

  • Filter: ((prev_qtr.report_date >= '2016-12-31'::date) AND (prev_qtr.report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 13046
39. 105.882 2,424.660 ↓ 98.7 96,978 1

WindowAgg (cost=147,036.39..147,203.11 rows=983 width=73) (actual time=2,123.235..2,424.660 rows=96,978 loops=1)

40.          

CTE distinct_funds

41. 51.247 434.860 ↑ 2.4 7,978 1

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

42. 282.883 383.613 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 26319kB
43. 77.501 100.730 ↓ 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=25.942..100.730 rows=143,507 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
45. 121.803 2,318.778 ↓ 98.7 96,978 1

Merge Left Join (cost=75,031.50..75,181.01 rows=983 width=81) (actual time=2,123.227..2,318.778 rows=96,978 loops=1)

  • Merge Cond: ((s_1.factset_fund_id = adjh_1.factset_fund_id) AND (q4_fund_dates_1.report_date = adjh_1.report_date))
  • Join Filter: (adjh_1.fsym_id = sar_1.fsym_id)
46. 250.463 999.335 ↓ 98.7 96,978 1

Sort (cost=1,272.90..1,275.35 rows=983 width=73) (actual time=968.396..999.335 rows=96,978 loops=1)

  • Sort Key: s_1.factset_fund_id, q4_fund_dates_1.report_date DESC
  • Sort Method: quicksort Memory: 16703kB
47. 73.661 748.872 ↓ 98.7 96,978 1

Nested Loop (cost=1.26..1,224.04 rows=983 width=73) (actual time=330.671..748.872 rows=96,978 loops=1)

  • Join Filter: (s_1.factset_fund_id = q4_fund_dates_1.factset_fund_id)
48. 4.263 540.571 ↓ 124.7 6,732 1

Nested Loop (cost=0.84..1,097.14 rows=54 width=78) (actual time=330.649..540.571 rows=6,732 loops=1)

49. 0.021 0.021 ↑ 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.019..0.021 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
50. 14.076 536.287 ↓ 124.7 6,732 1

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

51. 442.431 442.431 ↓ 84.9 7,978 1

CTE Scan on distinct_funds s_1 (cost=0.00..422.10 rows=94 width=96) (actual time=330.600..442.431 rows=7,978 loops=1)

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
52. 79.780 79.780 ↑ 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_1 (cost=0.42..7.08 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=7,978)

  • Index Cond: ((factset_fund_id = s_1.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 5463
53. 134.640 134.640 ↑ 1.3 14 6,732

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates q4_fund_dates_1 (cost=0.43..2.12 rows=18 width=13) (actual time=0.012..0.020 rows=14 loops=6,732)

  • Index Cond: ((factset_fund_id = ent_1.factset_fund_id) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Heap Fetches: 96978
54. 324.930 1,197.640 ↓ 7.6 143,507 1

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

  • Sort Key: adjh_1.factset_fund_id, adjh_1.report_date DESC
  • Sort Method: quicksort Memory: 17356kB
55. 98.174 872.710 ↓ 7.6 143,507 1

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

56. 116.874 774.536 ↓ 7.6 143,507 1

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

57. 539.895 657.662 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=598.574..657.662 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
58. 86.660 117.767 ↓ 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=34.069..117.767 rows=143,507 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
60. 11.593 2,959.244 ↓ 758.0 758 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=112) (actual time=2,832.877..2,959.244 rows=758 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: 25738
61. 2,384.457 2,384.457 ↓ 758.0 758 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=2,383.568..2,384.457 rows=758 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 105
62. 563.194 563.194 ↓ 34.0 34 758

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=0.025..0.743 rows=34 loops=758)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 829
63. 13,955.538 13,955.538 ↓ 1,489.0 2,978 758

CTE Scan on factset_prev_fund_holdings (cost=0.00..7.85 rows=2 width=88) (actual time=2.817..18.411 rows=2,978 loops=758)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 80954
64. 11.370 11.370 ↑ 1.0 1 758

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.015..0.015 rows=1 loops=758)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
65. 9.096 9.096 ↑ 1.0 1 758

Index Scan using q4_fund_values_pkey on q4_fund_values (cost=0.41..8.43 rows=1 width=26) (actual time=0.011..0.012 rows=1 loops=758)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
66. 10.612 10.612 ↑ 1.0 1 758

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
67. 9.854 9.854 ↑ 1.0 1 758

Index Scan using sym_entity_pkey on sym_entity fund_entity (cost=0.43..4.19 rows=1 width=40) (actual time=0.012..0.013 rows=1 loops=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
68. 17.434 17.434 ↑ 1.0 1 758

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
69. 9.096 9.096 ↑ 1.0 1 758

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

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
70. 3.032 3.032 ↑ 1.0 1 758

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

  • Index Cond: (iso_country = fund_entity.iso_country)
71. 2.274 2.274 ↑ 1.0 1 758

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

  • Index Cond: (region_code = country_map.region_code)
72. 9.854 9.854 ↑ 1.0 1 758

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.013..0.013 rows=1 loops=758)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
73. 2.274 2.274 ↑ 1.0 1 758

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

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
74. 2.274 2.274 ↑ 1.0 1 758

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

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
75. 9.854 9.854 ↑ 2.0 1 758

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

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

SubPlan (forNested Loop Left Join)

77. 27.288 197.838 ↑ 1.0 1 758

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.260..0.261 rows=1 loops=758)

78. 6.182 170.550 ↓ 2.0 2 758

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.134..0.225 rows=2 loops=758)

79. 86.570 161.454 ↓ 2.0 2 758

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.127..0.213 rows=2 loops=758)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
80. 59.724 59.724 ↑ 1.0 270 756

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (actual time=0.002..0.079 rows=270 loops=756)

81. 3.032 15.160 ↓ 2.0 2 758

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.020..0.020 rows=2 loops=758)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 12.128 12.128 ↓ 2.0 2 758

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.014..0.016 rows=2 loops=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
83. 2.914 2.914 ↑ 1.0 1 1,457

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

  • Index Cond: (region_code = country_map_1.region_code)