explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2mSt

Settings
# exclusive inclusive rows x rows loops node
1. 19.797 4,704.091 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,805.81..424,540.23 rows=769 width=608) (actual time=4,177.986..4,704.091 rows=6,617 loops=1)

2. 52.936 52.936 ↑ 2.0 1 6,617

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.33 rows=2 width=45) (actual time=0.007..0.008 rows=1 loops=6,617)

3. 5.564 4,631.358 ↓ 8.6 6,617 1

Hash Left Join (cost=396,805.25..415,791.18 rows=769 width=520) (actual time=4,177.930..4,631.358 rows=6,617 loops=1)

  • 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)))
  • Hash Cond: (own_ent_funds.fund_type = fund_type_map.fund_type_code)
4. 4,625.766 4,625.766 ↓ 8.6 6,617 1

Hash Left Join (cost=396,803.82..415,779.18 rows=769 width=456) (actual time=4,177.887..4,625.766 rows=6,617 loops=1)

5. 0.000 0.028 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=84) (actual time=0.028..0.028 rows=19 loops=1)

  • Hash Cond: (ent_entity_metro_areas.metro_id = metro_map.metro_code)
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
6. 0.013 0.013 ↑ 1.0 19 1

Seq Scan on fund_type_map (cost=0.00..1.19 rows=19 width=84) (actual time=0.005..0.013 rows=19 loops=1)

7. 4,620.319 4,620.319 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,797.66..415,762.45 rows=769 width=440) (actual time=4,177.665..4,620.319 rows=6,617 loops=1)

8. 0.000 0.211 ↑ 1.0 185 1

Hash (cost=3.85..3.85 rows=185 width=24) (actual time=0.211..0.211 rows=185 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
9. 0.106 0.106 ↑ 1.0 185 1

Seq Scan on metro_map (cost=0.00..3.85 rows=185 width=24) (actual time=0.007..0.106 rows=185 loops=1)

10. 59.553 59.553 ↑ 1.0 1 6,617

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.008..0.009 rows=1 loops=6,617)

11. 5.324 4,553.856 ↓ 8.6 6,617 1

Hash Left Join (cost=396,797.23..415,398.98 rows=769 width=445) (actual time=4,177.640..4,553.856 rows=6,617 loops=1)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
  • Hash Cond: (country_map.region_code = region_map.region_code)
12. 4,548.517 4,548.517 ↓ 8.6 6,617 1

Hash Left Join (cost=396,796.05..415,387.23 rows=769 width=379) (actual time=4,177.615..4,548.517 rows=6,617 loops=1)

13. 0.000 0.015 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=76) (actual time=0.015..0.015 rows=8 loops=1)

  • Hash Cond: (fund_entity.iso_country = country_map.iso_country)
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on region_map (cost=0.00..1.08 rows=8 width=76) (actual time=0.004..0.006 rows=8 loops=1)

15. 4,542.097 4,542.097 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,787.97..415,368.58 rows=769 width=367) (actual time=4,177.289..4,542.097 rows=6,617 loops=1)

16. 0.000 0.313 ↑ 1.0 270 1

Hash (cost=4.70..4.70 rows=270 width=15) (actual time=0.312..0.313 rows=270 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
17. 0.131 0.131 ↑ 1.0 270 1

Seq Scan on country_map (cost=0.00..4.70 rows=270 width=15) (actual time=0.007..0.131 rows=270 loops=1)

18. 4,478.512 4,478.512 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,787.55..410,082.37 rows=769 width=326) (actual time=4,177.266..4,478.512 rows=6,617 loops=1)

19. 0.000 52.936 ↑ 1.0 1 6,617

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..6.86 rows=1 width=51) (actual time=0.008..0.008 rows=1 loops=6,617)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
20. 4,413.532 4,413.532 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,787.12..408,410.16 rows=769 width=307) (actual time=4,177.245..4,413.532 rows=6,617 loops=1)

21. 0.000 59.553 ↑ 1.0 1 6,617

Index Scan using h_entity_pkey on h_entity (cost=0.43..2.16 rows=1 width=37) (actual time=0.008..0.009 rows=1 loops=6,617)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
22. 4,348.035 4,348.035 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=396,786.69..405,182.62 rows=769 width=276) (actual time=4,177.223..4,348.035 rows=6,617 loops=1)

23. 0.000 59.553 ↑ 1.0 1 6,617

Index Scan using sym_entity_pkey on sym_entity fund_entity (cost=0.43..4.19 rows=1 width=40) (actual time=0.008..0.009 rows=1 loops=6,617)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
24. 59.553 59.553 ↑ 1.0 1 6,617

Index Scan using sym_entity_pkey on sym_entity inst_entity (cost=0.43..4.19 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=6,617)

25. 8.279 4,278.443 ↓ 8.6 6,617 1

Hash Left Join (cost=396,786.26..401,955.07 rows=769 width=239) (actual time=4,177.194..4,278.443 rows=6,617 loops=1)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
  • Hash Cond: (current_factset_fund_holdings.factset_fund_id = q4_fund_values.factset_fund_id)
26. 4,194.276 4,194.276 ↓ 8.6 6,617 1

Nested Loop Left Join (cost=394,430.00..399,588.24 rows=769 width=223) (actual time=4,101.213..4,194.276 rows=6,617 loops=1)

27. 0.000 75.888 ↑ 1.0 65,078 1

Hash (cost=1,542.78..1,542.78 rows=65,078 width=26) (actual time=75.888..75.888 rows=65,078 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3926kB
28. 35.540 35.540 ↑ 1.0 65,078 1

Seq Scan on q4_fund_values (cost=0.00..1,542.78 rows=65,078 width=26) (actual time=0.009..35.540 rows=65,078 loops=1)

29. 4,133.901 4,133.901 ↓ 8.6 6,617 1

Merge Left Join (cost=394,429.59..394,450.35 rows=769 width=192) (actual time=4,101.185..4,133.901 rows=6,617 loops=1)

30. 0.000 52.936 ↑ 1.0 1 6,617

Index Scan using own_ent_funds_pkey on own_ent_funds (cost=0.42..6.66 rows=1 width=31) (actual time=0.007..0.008 rows=1 loops=6,617)

  • Merge Cond: ((current_factset_fund_holdings.fsym_id = factset_prev_fund_holdings.fsym_id) AND (current_factset_fund_holdings.factset_fund_id = factset_prev_fund_holdings.factset_fund_id))
  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
31.          

CTE factset_fund_holdings

32. 2,149.392 2,149.392 ↓ 9.6 6,524 1

Sort (cost=3,090.03..3,091.73 rows=680 width=88) (actual time=2,146.990..2,149.392 rows=6,524 loops=1)

33. 0.000 1,970.924 ↓ 8.6 6,617 1

Merge Left Join (cost=6,994.18..7,005.87 rows=769 width=112) (actual time=1,954.187..1,970.924 rows=6,617 loops=1)

  • Sort Method: quicksort Memory: 702kB
  • Sort Key: factset_prev_fund_holdings.fsym_id, factset_prev_fund_holdings.factset_fund_id
  • Merge Cond: ((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))
34. 1,883.139 1,883.139 ↑ 2.0 77,625 1

WindowAgg (cost=189,243.11..193,454.58 rows=153,788 width=46) (actual time=1,587.717..1,883.139 rows=77,625 loops=1)

35. 0.000 2,077.890 ↑ 2.1 64,435 1

WindowAgg (cost=187,152.43..190,890.79 rows=135,913 width=46) (actual time=1,673.911..2,077.890 rows=64,435 loops=1)

36. 1,941.740 1,941.740 ↓ 8.6 6,617 1

Sort (cost=3,497.09..3,499.01 rows=769 width=92) (actual time=1,939.589..1,941.740 rows=6,617 loops=1)

37. 16.731 16.731 ↓ 8.2 6,338 1

Sort (cost=3,497.09..3,499.01 rows=769 width=92) (actual time=14.593..16.731 rows=6,338 loops=1)

38. 0.000 2,138.701 ↓ 9.6 6,524 1

CTE Scan on factset_prev_fund_holdings (cost=0.00..3,058.04 rows=680 width=88) (actual time=1,673.916..2,138.701 rows=6,524 loops=1)

  • Sort Method: quicksort Memory: 709kB
  • Sort Method: quicksort Memory: 688kB
  • Sort Key: previous_factset_fund_holdings.fsym_id, previous_factset_fund_holdings.factset_fund_id
  • Sort Key: current_factset_fund_holdings.fsym_id, current_factset_fund_holdings.factset_fund_id
  • Rows Removed by Filter: 57911
  • Filter: (pos = 1)
39. 1,787.225 1,787.225 ↑ 2.0 77,625 1

Merge Left Join (cost=189,243.11..190,763.29 rows=153,788 width=54) (actual time=1,587.706..1,787.225 rows=77,625 loops=1)

40. 16.518 1,961.436 ↑ 2.1 64,435 1

Merge Left Join (cost=187,152.43..188,512.31 rows=135,913 width=54) (actual time=1,673.898..1,961.436 rows=64,435 loops=1)

  • Merge Cond: ((own_fund_detail_2.factset_fund_id = adjh_1.factset_fund_id) AND (ad_1.report_date = adjh_1.report_date))
  • Merge Cond: ((own_fund_detail.factset_fund_id = adjh.factset_fund_id) AND (ad.report_date = adjh.report_date))
  • Join Filter: (adjh_1.fsym_id = sar_1.fsym_id)
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
41. 10.389 10.389 ↓ 8.2 6,338 1

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..3,460.23 rows=769 width=92) (actual time=0.005..10.389 rows=6,338 loops=1)

42. 0.000 1,934.529 ↓ 8.6 6,617 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..3,460.23 rows=769 width=92) (actual time=1,587.723..1,934.529 rows=6,617 loops=1)

  • Rows Removed by Filter: 71287
  • Rows Removed by Filter: 71008
  • Filter: (pos = 2)
  • Filter: (pos = 1)
43. 767.687 767.687 ↑ 2.0 77,625 1

Sort (cost=115,291.30..115,675.77 rows=153,788 width=46) (actual time=739.111..767.687 rows=77,625 loops=1)

44. 705.346 705.346 ↑ 2.1 64,435 1

Sort (cost=113,200.62..113,540.40 rows=135,913 width=46) (actual time=667.130..705.346 rows=64,435 loops=1)

45. 1,090.516 1,090.516 ↓ 7.6 143,529 1

Sort (cost=73,951.81..73,998.84 rows=18,809 width=30) (actual time=1,006.752..1,090.516 rows=143,529 loops=1)

46. 0.000 897.829 ↓ 7.6 143,529 1

Sort (cost=73,951.81..73,998.84 rows=18,809 width=30) (actual time=848.582..897.829 rows=143,529 loops=1)

  • Sort Method: quicksort Memory: 17358kB
  • Sort Method: quicksort Memory: 17358kB
  • Sort Method: quicksort Memory: 13973kB
  • Sort Method: quicksort Memory: 10592kB
  • Sort Key: own_fund_detail_2.factset_fund_id, ad_1.report_date DESC
  • Sort Key: own_fund_detail.factset_fund_id, ad.report_date DESC
  • Sort Key: adjh_1.factset_fund_id, adjh_1.report_date DESC
  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
47. 676.668 676.668 ↓ 7.6 143,529 1

Subquery Scan on adjh_1 (cost=72,099.09..72,616.46 rows=18,809 width=30) (actual time=448.665..676.668 rows=143,529 loops=1)

48. 601.179 601.179 ↓ 7.6 143,529 1

Subquery Scan on adjh (cost=72,099.09..72,616.46 rows=18,809 width=30) (actual time=428.789..601.179 rows=143,529 loops=1)

49. 611.708 611.708 ↑ 2.0 77,625 1

Nested Loop (cost=77,017.22..102,042.02 rows=153,788 width=46) (actual time=402.045..611.708 rows=77,625 loops=1)

50. 0.000 562.022 ↑ 2.1 64,435 1

Nested Loop (cost=77,017.22..101,612.46 rows=135,913 width=46) (actual time=364.700..562.022 rows=64,435 loops=1)

  • Join Filter: (own_fund_detail_2.factset_fund_id = ad_1.factset_fund_id)
  • Join Filter: (own_fund_detail.factset_fund_id = ad.factset_fund_id)
51. 596.380 596.380 ↓ 7.6 143,529 1

Unique (cost=72,099.09..72,428.37 rows=18,809 width=54) (actual time=448.663..596.380 rows=143,529 loops=1)

52. 540.232 540.232 ↓ 7.6 143,529 1

Unique (cost=72,099.09..72,428.37 rows=18,809 width=54) (actual time=428.786..540.232 rows=143,529 loops=1)

53. 484.881 484.881 ↑ 1.6 6,730 1

Nested Loop (cost=77,016.79..77,592.30 rows=10,739 width=51) (actual time=402.016..484.881 rows=6,730 loops=1)

54. 447.305 447.305 ↑ 1.6 6,730 1

Nested Loop (cost=77,016.79..77,592.30 rows=10,739 width=51) (actual time=364.670..447.305 rows=6,730 loops=1)

55. 80.760 80.760 ↑ 1.3 10 6,730

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates ad_1 (cost=0.43..2.07 rows=13 width=13) (actual time=0.008..0.012 rows=10 loops=6,730)

56. 0.000 87.490 ↑ 1.2 12 6,730

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates ad (cost=0.43..2.10 rows=14 width=13) (actual time=0.008..0.013 rows=12 loops=6,730)

  • Index Cond: ((factset_fund_id = ent_1.factset_fund_id) AND (report_date >= '2017-06-10'::date) AND (report_date <= '2019-06-10'::date) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Index Cond: ((factset_fund_id = ent.factset_fund_id) AND (report_date >= '2017-06-10'::date) AND (report_date <= '2019-06-10'::date) AND (report_date >= '2017-06-10'::date) AND (report_date <= '2019-06-10'::date))
  • Heap Fetches: 77625
  • Heap Fetches: 64435
57. 500.712 500.712 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=448.662..500.712 rows=143,529 loops=1)

58. 466.798 466.798 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=428.783..466.798 rows=143,529 loops=1)

59. 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)

60. 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)

61. 481.852 481.852 ↑ 1.6 6,730 1

Hash Join (cost=77,016.37..77,476.47 rows=10,739 width=51) (actual time=401.999..481.852 rows=6,730 loops=1)

62. 0.000 444.270 ↑ 1.6 6,730 1

Hash Join (cost=77,016.37..77,476.47 rows=10,739 width=51) (actual time=364.646..444.270 rows=6,730 loops=1)

  • Sort Method: quicksort Memory: 26321kB
  • Sort Method: quicksort Memory: 26321kB
  • 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 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
  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
  • Heap Fetches: 1
  • Hash Cond: (own_fund_detail_2.factset_fund_id = ent_1.factset_fund_id)
  • Hash Cond: (own_fund_detail.factset_fund_id = ent.factset_fund_id)
63. 412.195 412.195 ↑ 2.4 7,979 1

Unique (cost=72,099.09..72,193.17 rows=18,809 width=54) (actual time=338.938..412.195 rows=7,979 loops=1)

64. 374.322 374.322 ↑ 2.4 7,979 1

Unique (cost=72,099.09..72,193.17 rows=18,809 width=54) (actual time=301.226..374.322 rows=7,979 loops=1)

65. 62.954 62.954 ↓ 1.0 68,140 1

Hash (cost=4,065.65..4,065.65 rows=68,131 width=9) (actual time=62.953..62.954 rows=68,140 loops=1)

66. 62.907 62.907 ↓ 1.0 68,140 1

Hash (cost=4,065.65..4,065.65 rows=68,131 width=9) (actual time=62.907..62.907 rows=68,140 loops=1)

67. 69.672 69.672 ↓ 7.6 143,529 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_3 (cost=442.39..70,763.18 rows=18,816 width=54) (actual time=19.007..69.672 rows=143,529 loops=1)

68. 0.000 95.128 ↓ 7.6 143,529 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=442.39..70,763.18 rows=18,816 width=54) (actual time=28.676..95.128 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
  • Heap Blocks: exact=13494
  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
69. 376.831 376.831 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=338.937..376.831 rows=143,529 loops=1)

70. 338.740 338.740 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=301.225..338.740 rows=143,529 loops=1)

71. 40.281 40.281 ↓ 1.0 68,140 1

Seq Scan on own_ent_funds ent_1 (cost=0.00..4,065.65 rows=68,131 width=9) (actual time=0.011..40.281 rows=68,140 loops=1)

72. 41.209 41.209 ↓ 1.0 68,140 1

Seq Scan on own_ent_funds ent (cost=0.00..4,065.65 rows=68,131 width=9) (actual time=0.008..41.209 rows=68,140 loops=1)

73. 25.944 25.944 ↓ 7.7 144,981 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.69 rows=18,816 width=0) (actual time=25.944..25.944 rows=144,981 loops=1)

74. 0.000 16.891 ↓ 7.7 144,981 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.69 rows=18,816 width=0) (actual time=16.891..16.891 rows=144,981 loops=1)

  • Sort Method: quicksort Memory: 17358kB
  • Sort Method: quicksort Memory: 17358kB
  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Key: own_fund_detail.factset_fund_id
  • Rows Removed by Filter: 51192
  • Rows Removed by Filter: 51192
  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Filter: (active_flag = 1)
  • Filter: (active_flag = 1)
75. 89.711 89.711 ↓ 7.6 143,529 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_2 (cost=442.39..70,763.18 rows=18,816 width=54) (actual time=19.154..89.711 rows=143,529 loops=1)

76. 47.572 81.987 ↓ 7.6 143,529 1

Bitmap Heap Scan on own_fund_detail (cost=442.39..70,763.18 rows=18,816 width=54) (actual time=19.528..81.987 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
  • Heap Blocks: exact=13494
77. 17.417 17.417 ↓ 7.7 144,981 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.69 rows=18,816 width=0) (actual time=17.417..17.417 rows=144,981 loops=1)

78. 16.998 16.998 ↓ 7.7 144,981 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.69 rows=18,816 width=0) (actual time=16.998..16.998 rows=144,981 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
Execution time : 4,707.696 ms