explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f28D : new

Settings
# exclusive inclusive rows x rows loops node
1. 6.079 11,734.598 ↓ 361.0 722 1

Nested Loop Left Join (cost=402,770.71..404,412.34 rows=2 width=640) (actual time=3,607.394..11,734.598 rows=722 loops=1)

2. 0.969 11,589.895 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,770.14..404,372.07 rows=1 width=520) (actual time=3,607.055..11,589.895 rows=722 loops=1)

3. 1.507 11,586.760 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,770.01..404,371.91 rows=1 width=456) (actual time=3,607.046..11,586.760 rows=722 loops=1)

4. 1.501 11,583.809 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,769.86..404,371.73 rows=1 width=440) (actual time=3,607.039..11,583.809 rows=722 loops=1)

5. 1.411 11,575.088 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,769.43..404,371.26 rows=1 width=445) (actual time=3,607.022..11,575.088 rows=722 loops=1)

6. 1.580 11,572.233 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,769.30..404,371.06 rows=1 width=379) (actual time=3,607.014..11,572.233 rows=722 loops=1)

7. 1.341 11,568.487 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,769.15..404,370.88 rows=1 width=367) (actual time=3,607.003..11,568.487 rows=722 loops=1)

8. 1.326 11,559.926 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,768.73..404,362.44 rows=1 width=326) (actual time=3,606.984..11,559.926 rows=722 loops=1)

9. 1.183 11,551.380 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,768.30..404,360.26 rows=1 width=307) (actual time=3,606.967..11,551.380 rows=722 loops=1)

10. 1.474 11,542.977 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,767.87..404,356.06 rows=1 width=276) (actual time=3,606.952..11,542.977 rows=722 loops=1)

11. 1.486 11,533.561 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,767.44..404,351.87 rows=1 width=239) (actual time=3,606.890..11,533.561 rows=722 loops=1)

12. 2.118 11,525.577 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,767.02..404,343.42 rows=1 width=223) (actual time=3,606.874..11,525.577 rows=722 loops=1)

13. 536.575 11,514.795 ↓ 722.0 722 1

Nested Loop Left Join (cost=402,766.61..404,334.97 rows=1 width=192) (actual time=3,606.847..11,514.795 rows=722 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: 2160946
14.          

CTE factset_fund_holdings

15. 23.039 1,747.253 ↓ 1,188.0 1,188 1

Subquery Scan on current_qtr (cost=193,086.70..201,383.30 rows=1 width=46) (actual time=1,457.519..1,747.253 rows=1,188 loops=1)

  • Filter: ((current_qtr.report_date >= '2019-05-30'::date) AND (current_qtr.report_date <= '2019-06-06'::date))
  • Rows Removed by Filter: 95648
16. 94.849 1,724.214 ↑ 2.0 96,836 1

WindowAgg (cost=193,086.70..198,444.01 rows=195,953 width=46) (actual time=1,457.298..1,724.214 rows=96,836 loops=1)

17. 108.446 1,629.365 ↑ 2.0 96,836 1

Merge Left Join (cost=193,086.70..195,014.83 rows=195,953 width=54) (actual time=1,457.290..1,629.365 rows=96,836 loops=1)

  • Merge Cond: ((own_fund_detail.factset_fund_id = adjh.factset_fund_id) AND (q4_fund_dates.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
18. 182.849 754.296 ↑ 2.0 96,836 1

Sort (cost=119,407.05..119,896.94 rows=195,953 width=46) (actual time=725.503..754.296 rows=96,836 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 16688kB
19. 50.783 571.447 ↑ 2.0 96,836 1

Nested Loop (cost=76,531.61..102,182.64 rows=195,953 width=46) (actual time=346.377..571.447 rows=96,836 loops=1)

  • Join Filter: (own_fund_detail.factset_fund_id = q4_fund_dates.factset_fund_id)
20. 2.879 426.416 ↑ 1.6 6,732 1

Nested Loop (cost=76,531.18..77,104.13 rows=10,675 width=51) (actual time=346.343..426.416 rows=6,732 loops=1)

21. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
22. 6.705 423.520 ↑ 1.6 6,732 1

Hash Join (cost=76,530.76..76,988.94 rows=10,675 width=51) (actual time=346.325..423.520 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail.factset_fund_id = ent.factset_fund_id)
23. 35.651 354.787 ↑ 2.3 7,977 1

Unique (cost=71,834.11..71,927.85 rows=18,741 width=54) (actual time=284.142..354.787 rows=7,977 loops=1)

24. 239.213 319.136 ↓ 7.7 143,428 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=284.140..319.136 rows=143,428 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 17350kB
25. 67.873 79.923 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail (cost=437.87..70,503.52 rows=18,748 width=54) (actual time=14.340..79.923 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
26. 12.050 12.050 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=12.050..12.050 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
27. 22.156 62.028 ↓ 1.0 68,127 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3752kB
28. 39.872 39.872 ↓ 1.0 68,127 1

Seq Scan on own_ent_funds ent (cost=0.00..3,847.24 rows=67,953 width=9) (actual time=0.010..39.872 rows=68,127 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51172
29. 94.248 94.248 ↑ 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.009..0.014 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-06'::date))
  • Heap Fetches: 96836
30. 227.968 766.623 ↓ 7.7 143,428 1

Sort (cost=73,679.65..73,726.50 rows=18,741 width=30) (actual time=731.770..766.623 rows=143,428 loops=1)

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17350kB
31. 62.185 538.655 ↓ 7.7 143,428 1

Subquery Scan on adjh (cost=71,834.11..72,349.61 rows=18,741 width=30) (actual time=364.638..538.655 rows=143,428 loops=1)

32. 75.245 476.470 ↓ 7.7 143,428 1

Unique (cost=71,834.11..72,162.20 rows=18,741 width=54) (actual time=364.634..476.470 rows=143,428 loops=1)

33. 334.444 401.225 ↓ 7.7 143,428 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=364.633..401.225 rows=143,428 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: 26310kB
34. 54.884 66.781 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=437.87..70,503.52 rows=18,748 width=54) (actual time=14.189..66.781 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
35. 11.897 11.897 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=11.896..11.897 rows=145,949 loops=1)

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

CTE factset_prev_fund_holdings

37. 47.104 1,812.272 ↓ 1.2 83,920 1

Subquery Scan on prev_qtr (cost=193,086.70..201,383.30 rows=69,471 width=46) (actual time=1,488.393..1,812.272 rows=83,920 loops=1)

  • Filter: ((prev_qtr.report_date >= '2016-12-31'::date) AND (prev_qtr.report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 12916
38. 98.287 1,765.168 ↑ 2.0 96,836 1

WindowAgg (cost=193,086.70..198,444.01 rows=195,953 width=46) (actual time=1,488.372..1,765.168 rows=96,836 loops=1)

39. 110.827 1,666.881 ↑ 2.0 96,836 1

Merge Left Join (cost=193,086.70..195,014.83 rows=195,953 width=54) (actual time=1,488.364..1,666.881 rows=96,836 loops=1)

  • Merge Cond: ((own_fund_detail_2.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)
40. 184.293 777.211 ↑ 2.0 96,836 1

Sort (cost=119,407.05..119,896.94 rows=195,953 width=46) (actual time=747.658..777.211 rows=96,836 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id, q4_fund_dates_1.report_date DESC
  • Sort Method: quicksort Memory: 16688kB
41. 54.546 592.918 ↑ 2.0 96,836 1

Nested Loop (cost=76,531.61..102,182.64 rows=195,953 width=46) (actual time=360.641..592.918 rows=96,836 loops=1)

  • Join Filter: (own_fund_detail_2.factset_fund_id = q4_fund_dates_1.factset_fund_id)
42. 2.989 444.124 ↑ 1.6 6,732 1

Nested Loop (cost=76,531.18..77,104.13 rows=10,675 width=51) (actual time=360.605..444.124 rows=6,732 loops=1)

43. 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.020..0.021 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
44. 7.026 441.114 ↑ 1.6 6,732 1

Hash Join (cost=76,530.76..76,988.94 rows=10,675 width=51) (actual time=360.583..441.114 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail_2.factset_fund_id = ent_1.factset_fund_id)
45. 36.606 371.068 ↑ 2.3 7,977 1

Unique (cost=71,834.11..71,927.85 rows=18,741 width=54) (actual time=297.402..371.068 rows=7,977 loops=1)

46. 255.518 334.462 ↓ 7.7 143,428 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=297.400..334.462 rows=143,428 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 17350kB
47. 67.040 78.944 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_2 (cost=437.87..70,503.52 rows=18,748 width=54) (actual time=14.190..78.944 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
48. 11.904 11.904 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=11.904..11.904 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
49. 22.661 63.020 ↓ 1.0 68,127 1

Hash (cost=3,847.24..3,847.24 rows=67,953 width=9) (actual time=63.019..63.020 rows=68,127 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3752kB
50. 40.359 40.359 ↓ 1.0 68,127 1

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

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51172
51. 94.248 94.248 ↑ 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.009..0.014 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-06'::date))
  • Heap Fetches: 96836
52. 237.696 778.843 ↓ 7.7 143,428 1

Sort (cost=73,679.65..73,726.50 rows=18,741 width=30) (actual time=740.693..778.843 rows=143,428 loops=1)

  • Sort Key: adjh_1.factset_fund_id, adjh_1.report_date DESC
  • Sort Method: quicksort Memory: 17350kB
53. 61.080 541.147 ↓ 7.7 143,428 1

Subquery Scan on adjh_1 (cost=71,834.11..72,349.61 rows=18,741 width=30) (actual time=369.243..541.147 rows=143,428 loops=1)

54. 72.830 480.067 ↓ 7.7 143,428 1

Unique (cost=71,834.11..72,162.20 rows=18,741 width=54) (actual time=369.240..480.067 rows=143,428 loops=1)

55. 340.630 407.237 ↓ 7.7 143,428 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=369.237..407.237 rows=143,428 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: 26310kB
56. 54.676 66.607 ↓ 7.7 143,428 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_3 (cost=437.87..70,503.52 rows=18,748 width=54) (actual time=14.168..66.607 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
57. 11.931 11.931 ↓ 7.8 145,949 1

Bitmap Index Scan on own_fund_detail_fsym_id_idx (cost=0.00..433.18 rows=18,748 width=0) (actual time=11.931..11.931 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
58. 83.598 1,992.930 ↓ 722.0 722 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=112) (actual time=1,748.149..1,992.930 rows=722 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: 284795
59. 1,458.082 1,458.082 ↓ 722.0 722 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=1,457.525..1,458.082 rows=722 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 466
60. 451.250 451.250 ↓ 395.0 395 722

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=0.015..0.625 rows=395 loops=722)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 793
61. 8,985.290 8,985.290 ↓ 8.6 2,993 722

CTE Scan on factset_prev_fund_holdings (cost=0.00..1,563.10 rows=347 width=88) (actual time=2.072..12.445 rows=2,993 loops=722)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 80927
62. 8.664 8.664 ↑ 1.0 1 722

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.012..0.012 rows=1 loops=722)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
63. 6.498 6.498 ↑ 1.0 1 722

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

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
64. 7.942 7.942 ↑ 1.0 1 722

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
65. 7.220 7.220 ↑ 1.0 1 722

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
66. 7.220 7.220 ↑ 1.0 1 722

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
67. 7.220 7.220 ↑ 1.0 1 722

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..8.44 rows=1 width=51) (actual time=0.010..0.010 rows=1 loops=722)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
68. 2.166 2.166 ↑ 1.0 1 722

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

  • Index Cond: (iso_country = fund_entity.iso_country)
69. 1.444 1.444 ↑ 1.0 1 722

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

  • Index Cond: (region_code = country_map.region_code)
70. 7.220 7.220 ↑ 1.0 1 722

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.010..0.010 rows=1 loops=722)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
71. 1.444 1.444 ↑ 1.0 1 722

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

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
72. 2.166 2.166 ↑ 1.0 1 722

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

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
73. 7.220 7.220 ↑ 2.0 1 722

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

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

SubPlan (forNested Loop Left Join)

75. 18.050 131.404 ↑ 1.0 1 722

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.182..0.182 rows=1 loops=722)

76. 3.764 113.354 ↓ 2.0 2 722

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.099..0.157 rows=2 loops=722)

77. 52.824 106.856 ↓ 2.0 2 722

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.095..0.148 rows=2 loops=722)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
78. 42.480 42.480 ↑ 1.0 270 720

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

79. 2.166 11.552 ↓ 2.0 2 722

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.016..0.016 rows=2 loops=722)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 9.386 9.386 ↓ 2.0 2 722

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.011..0.013 rows=2 loops=722)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
81. 2.734 2.734 ↑ 1.0 1 1,367

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

  • Index Cond: (region_code = country_map_1.region_code)