explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eSXe : old

Settings
# exclusive inclusive rows x rows loops node
1. 6.080 18,879.146 ↓ 361.0 722 1

Nested Loop Left Join (cost=341,506.76..345,605.55 rows=2 width=640) (actual time=7,943.514..18,879.146 rows=722 loops=1)

2. 1.612 18,714.948 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,506.19..345,565.29 rows=1 width=520) (actual time=7,942.382..18,714.948 rows=722 loops=1)

3. 1.509 18,711.170 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,506.05..345,565.12 rows=1 width=456) (actual time=7,941.928..18,711.170 rows=722 loops=1)

4. 1.000 18,708.217 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,505.91..345,564.95 rows=1 width=440) (actual time=7,941.920..18,708.217 rows=722 loops=1)

5. 1.506 18,674.005 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,505.48..345,564.48 rows=1 width=445) (actual time=7,941.894..18,674.005 rows=722 loops=1)

6. 1.896 18,671.055 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,505.34..345,564.27 rows=1 width=379) (actual time=7,941.883..18,671.055 rows=722 loops=1)

7. 1.424 18,666.271 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,505.20..345,564.10 rows=1 width=367) (actual time=7,941.871..18,666.271 rows=722 loops=1)

8. 1.014 18,130.567 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,504.78..345,555.65 rows=1 width=326) (actual time=7,940.495..18,130.567 rows=722 loops=1)

9. 1.000 17,869.633 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,504.35..345,553.48 rows=1 width=307) (actual time=7,938.015..17,869.633 rows=722 loops=1)

10. 1.617 17,812.317 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,503.92..345,549.28 rows=1 width=276) (actual time=7,937.957..17,812.317 rows=722 loops=1)

11. 1.343 17,767.380 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,503.48..345,545.08 rows=1 width=239) (actual time=7,937.911..17,767.380 rows=722 loops=1)

12. 2.199 17,396.373 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,503.07..345,536.64 rows=1 width=223) (actual time=7,936.339..17,396.373 rows=722 loops=1)

13. 1,179.017 17,385.510 ↓ 722.0 722 1

Nested Loop Left Join (cost=341,502.65..345,528.19 rows=1 width=192) (actual time=7,936.311..17,385.510 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: 4738550
14.          

CTE factset_fund_holdings

15. 1.129 6,059.194 ↓ 17.7 1,188 1

WindowAgg (cost=145,600.82..145,602.16 rows=67 width=46) (actual time=6,057.770..6,059.194 rows=1,188 loops=1)

16. 2.854 6,058.065 ↓ 17.7 1,188 1

Sort (cost=145,600.82..145,600.99 rows=67 width=54) (actual time=6,057.763..6,058.065 rows=1,188 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 215kB
17. 50.888 6,055.211 ↓ 17.7 1,188 1

Merge Left Join (cost=144,433.08..145,598.79 rows=67 width=54) (actual time=5,644.919..6,055.211 rows=1,188 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: 38199
18. 0.884 5,457.906 ↓ 17.7 1,188 1

Nested Loop (cost=72,598.97..73,061.14 rows=67 width=46) (actual time=5,276.315..5,457.906 rows=1,188 loops=1)

19. 1.976 5,454.646 ↓ 17.7 1,188 1

Nested Loop (cost=72,598.55..73,051.86 rows=67 width=46) (actual time=5,274.903..5,454.646 rows=1,188 loops=1)

  • Join Filter: (q4_fund_dates.factset_fund_id = ent.factset_fund_id)
20. 7.834 5,355.254 ↓ 10.1 1,188 1

Merge Join (cost=72,598.13..72,928.37 rows=118 width=55) (actual time=5,274.388..5,355.254 rows=1,188 loops=1)

  • Merge Cond: (own_fund_detail.factset_fund_id = q4_fund_dates.factset_fund_id)
21. 35.552 5,270.849 ↑ 2.3 7,976 1

Unique (cost=71,834.11..71,927.85 rows=18,741 width=54) (actual time=5,199.752..5,270.849 rows=7,976 loops=1)

22. 238.505 5,235.297 ↓ 7.7 143,426 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=5,199.750..5,235.297 rows=143,426 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 17350kB
23. 4,965.284 4,996.792 ↓ 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=34.226..4,996.792 rows=143,428 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
25. 24.862 76.571 ↓ 35.0 7,348 1

Sort (cost=764.02..764.55 rows=210 width=13) (actual time=74.590..76.571 rows=7,348 loops=1)

  • Sort Key: q4_fund_dates.factset_fund_id
  • Sort Method: quicksort Memory: 537kB
26. 34.331 51.709 ↓ 35.0 7,348 1

Bitmap Heap Scan on q4_fund_dates (cost=10.58..755.92 rows=210 width=13) (actual time=17.878..51.709 rows=7,348 loops=1)

  • Recheck Cond: ((report_date >= '2019-05-30'::date) AND (report_date <= '2019-06-06'::date))
  • Heap Blocks: exact=85
27. 17.378 17.378 ↓ 35.0 7,348 1

Bitmap Index Scan on q4_fund_dates_report_date_idx (cost=0.00..10.53 rows=210 width=0) (actual time=17.377..17.378 rows=7,348 loops=1)

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

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.076..0.082 rows=1 loops=1,188)

  • Index Cond: ((factset_fund_id = own_fund_detail.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 1354
29. 0.966 2.376 ↑ 1.0 1 1,188

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

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
31. 62.306 546.417 ↓ 8.5 159,307 1

Materialize (cost=71,834.11..72,396.46 rows=18,741 width=30) (actual time=368.478..546.417 rows=159,307 loops=1)

32. 76.807 484.111 ↓ 7.7 143,419 1

Unique (cost=71,834.11..72,162.20 rows=18,741 width=54) (actual time=368.476..484.111 rows=143,419 loops=1)

33. 339.729 407.304 ↓ 7.6 143,419 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=368.474..407.304 rows=143,419 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. 55.193 67.575 ↓ 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.660..67.575 rows=143,428 loops=1)

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

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

CTE factset_prev_fund_holdings

37. 82.407 2,119.738 ↑ 2.1 83,920 1

WindowAgg (cost=191,028.99..195,900.49 rows=178,184 width=46) (actual time=1,876.059..2,119.738 rows=83,920 loops=1)

38. 97.956 2,037.331 ↑ 2.1 83,920 1

Merge Left Join (cost=191,028.99..192,782.27 rows=178,184 width=54) (actual time=1,876.040..2,037.331 rows=83,920 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. 159.901 1,152.382 ↑ 2.1 83,920 1

Sort (cost=117,349.34..117,794.80 rows=178,184 width=46) (actual time=1,126.838..1,152.382 rows=83,920 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id, q4_fund_dates_1.report_date DESC
  • Sort Method: quicksort Memory: 14874kB
40. 46.176 992.481 ↑ 2.1 83,920 1

Nested Loop (cost=76,531.61..101,809.01 rows=178,184 width=46) (actual time=369.456..992.481 rows=83,920 loops=1)

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

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

42. 0.014 0.014 ↑ 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.013..0.014 rows=1 loops=1)

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

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

  • Hash Cond: (own_fund_detail_2.factset_fund_id = ent_1.factset_fund_id)
44. 36.631 380.669 ↑ 2.3 7,977 1

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

45. 263.545 344.038 ↓ 7.7 143,428 1

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

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 17350kB
46. 68.403 80.493 ↓ 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.388..80.493 rows=143,428 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
48. 22.008 63.138 ↓ 1.0 68,127 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3752kB
49. 41.130 41.130 ↓ 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.011..41.130 rows=68,127 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51172
50. 491.436 491.436 ↑ 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.063..0.073 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: 83920
51. 239.929 786.993 ↓ 7.7 143,428 1

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

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17350kB
52. 62.429 547.064 ↓ 7.7 143,428 1

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

53. 75.986 484.635 ↓ 7.7 143,428 1

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

54. 341.682 408.649 ↓ 7.7 143,428 1

Sort (cost=71,834.11..71,880.98 rows=18,748 width=54) (actual time=371.273..408.649 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
55. 54.827 66.967 ↓ 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.428..66.967 rows=143,428 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=14383
56. 12.140 12.140 ↓ 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.140..12.140 rows=145,949 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
57. 83.557 6,303.541 ↓ 722.0 722 1

Nested Loop Left Join (cost=0.00..3.03 rows=1 width=112) (actual time=6,060.023..6,303.541 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
58. 6,058.256 6,058.256 ↓ 722.0 722 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..1.51 rows=1 width=92) (actual time=6,057.778..6,058.256 rows=722 loops=1)

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

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..1.51 rows=1 width=92) (actual time=0.001..0.224 rows=395 loops=722)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 793
60. 9,902.952 9,902.952 ↓ 7.4 6,564 722

CTE Scan on factset_prev_fund_holdings (cost=0.00..4,009.14 rows=891 width=88) (actual time=2.599..13.716 rows=6,564 loops=722)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 77356
61. 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)
62. 369.664 369.664 ↑ 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.508..0.512 rows=1 loops=722)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
63. 43.320 43.320 ↑ 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.060..0.060 rows=1 loops=722)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
64. 56.316 56.316 ↑ 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.076..0.078 rows=1 loops=722)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
65. 259.920 259.920 ↑ 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.358..0.360 rows=1 loops=722)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
66. 534.280 534.280 ↑ 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.736..0.740 rows=1 loops=722)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
67. 2.888 2.888 ↑ 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.004..0.004 rows=1 loops=722)

  • Index Cond: (iso_country = fund_entity.iso_country)
68. 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)
69. 33.212 33.212 ↑ 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.045..0.046 rows=1 loops=722)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
70. 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)
71. 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.003..0.003 rows=1 loops=722)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
72. 8.664 8.664 ↑ 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.011..0.012 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)))
73.          

SubPlan (forNested Loop Left Join)

74. 18.772 149.454 ↑ 1.0 1 722

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

75. 3.764 130.682 ↓ 2.0 2 722

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

76. 53.548 124.184 ↓ 2.0 2 722

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

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
77. 43.200 43.200 ↑ 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.060 rows=270 loops=720)

78. 2.166 27.436 ↓ 2.0 2 722

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 25.270 25.270 ↓ 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.031..0.035 rows=2 loops=722)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
80. 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.002..0.002 rows=1 loops=1,367)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 19.291 ms
Execution time : 18,887.229 ms