explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BdD4 : using with for fund dates

Settings
# exclusive inclusive rows x rows loops node
1. 34.327 12,047.123 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,837.75..473,281.48 rows=824 width=640) (actual time=10,145.413..12,047.123 rows=6,617 loops=1)

2. 7.110 10,682.779 ↓ 8.0 6,617 1

Hash Left Join (cost=431,837.18..452,037.92 rows=824 width=520) (actual time=10,145.119..10,682.779 rows=6,617 loops=1)

  • Hash Cond: (own_ent_funds.fund_type = fund_type_map.fund_type_code)
3. 6.345 10,675.651 ↓ 8.0 6,617 1

Hash Left Join (cost=431,835.76..452,025.16 rows=824 width=456) (actual time=10,145.094..10,675.651 rows=6,617 loops=1)

  • Hash Cond: (ent_entity_metro_areas.metro_id = metro_map.metro_code)
4. 10.626 10,669.190 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,829.59..452,007.67 rows=824 width=440) (actual time=10,144.969..10,669.190 rows=6,617 loops=1)

5. 6.043 10,585.777 ↓ 8.0 6,617 1

Hash Left Join (cost=431,829.16..451,618.20 rows=824 width=445) (actual time=10,144.950..10,585.777 rows=6,617 loops=1)

  • Hash Cond: (country_map.region_code = region_map.region_code)
6. 6.784 10,579.722 ↓ 8.0 6,617 1

Hash Left Join (cost=431,827.98..451,605.69 rows=824 width=379) (actual time=10,144.928..10,579.722 rows=6,617 loops=1)

  • Hash Cond: (fund_entity.iso_country = country_map.iso_country)
7. 13.423 10,572.750 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,819.91..451,586.29 rows=824 width=367) (actual time=10,144.732..10,572.750 rows=6,617 loops=1)

8. 10.691 10,493.157 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,819.49..445,987.61 rows=824 width=326) (actual time=10,144.716..10,493.157 rows=6,617 loops=1)

9. 11.353 10,416.296 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,819.06..444,195.80 rows=824 width=307) (actual time=10,144.701..10,416.296 rows=6,617 loops=1)

10. 11.926 10,338.773 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=431,818.63..440,737.41 rows=824 width=276) (actual time=10,144.686..10,338.773 rows=6,617 loops=1)

11. 9.316 10,260.677 ↓ 8.0 6,617 1

Hash Left Join (cost=431,818.20..437,279.03 rows=824 width=239) (actual time=10,144.656..10,260.677 rows=6,617 loops=1)

  • Hash Cond: (current_factset_fund_holdings.factset_fund_id = q4_fund_values.factset_fund_id)
12. 8.771 10,208.293 ↓ 8.0 6,617 1

Nested Loop Left Join (cost=429,461.94..434,911.44 rows=824 width=223) (actual time=10,101.502..10,208.293 rows=6,617 loops=1)

13. 16.429 10,139.969 ↓ 8.0 6,617 1

Merge Left Join (cost=429,461.52..429,484.52 rows=824 width=192) (actual time=10,101.470..10,139.969 rows=6,617 loops=1)

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

CTE report_dates

15. 380.945 380.945 ↓ 1.1 673,725 1

Seq Scan on q4_fund_dates (cost=0.00..33,672.26 rows=614,204 width=13) (actual time=0.015..380.945 rows=673,725 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-10'::date))
  • Rows Removed by Filter: 955937
16.          

CTE factset_fund_holdings

17. 148.972 5,830.350 ↑ 2.1 77,625 1

WindowAgg (cost=186,596.56..192,269.63 rows=164,893 width=46) (actual time=5,395.308..5,830.350 rows=77,625 loops=1)

18. 181.705 5,681.378 ↑ 2.1 77,625 1

Merge Left Join (cost=186,596.56..189,384.00 rows=164,893 width=54) (actual time=5,395.300..5,681.378 rows=77,625 loops=1)

  • Merge Cond: ((own_fund_detail.factset_fund_id = adjh.factset_fund_id) AND (ad.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
19. 168.562 4,155.235 ↑ 2.1 77,625 1

Sort (cost=112,644.75..113,056.98 rows=164,893 width=46) (actual time=4,118.082..4,155.235 rows=77,625 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, ad.report_date DESC
  • Sort Method: quicksort Memory: 13973kB
20. 292.778 3,986.673 ↑ 2.1 77,625 1

Merge Join (cost=95,591.96..98,355.81 rows=164,893 width=46) (actual time=3,296.185..3,986.673 rows=77,625 loops=1)

  • Merge Cond: (ad.factset_fund_id = own_fund_detail.factset_fund_id)
21. 1,764.537 3,199.151 ↓ 308.4 540,695 1

Sort (cost=23,492.87..23,497.25 rows=1,753 width=58) (actual time=2,918.268..3,199.151 rows=540,695 loops=1)

  • Sort Key: ad.factset_fund_id
  • Sort Method: external merge Disk: 23256kB
22. 231.907 1,434.614 ↓ 308.4 540,695 1

Nested Loop (cost=4,917.71..23,398.42 rows=1,753 width=58) (actual time=62.745..1,434.614 rows=540,695 loops=1)

23. 0.033 0.033 ↑ 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.033 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
24. 312.680 1,202.674 ↓ 308.4 540,695 1

Hash Join (cost=4,917.29..23,372.45 rows=1,753 width=49) (actual time=62.713..1,202.674 rows=540,695 loops=1)

  • Hash Cond: (ad.factset_fund_id = ent.factset_fund_id)
25. 827.752 827.752 ↓ 176.3 541,443 1

CTE Scan on report_dates ad (cost=0.00..18,426.12 rows=3,071 width=40) (actual time=0.026..827.752 rows=541,443 loops=1)

  • Filter: ((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))
  • Rows Removed by Filter: 132282
26. 21.873 62.242 ↓ 1.0 68,140 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
27. 40.369 40.369 ↓ 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.006..40.369 rows=68,140 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51192
28. 24.799 494.744 ↓ 4.2 78,987 1

Materialize (cost=72,099.09..72,428.28 rows=18,809 width=42) (actual time=377.910..494.744 rows=78,987 loops=1)

29. 47.451 469.945 ↑ 2.4 7,979 1

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

30. 246.557 422.494 ↓ 7.6 143,529 1

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

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 17358kB
31. 155.458 175.937 ↓ 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=22.611..175.937 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
32. 20.479 20.479 ↓ 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=20.479..20.479 rows=144,981 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
33. 457.768 1,344.438 ↓ 7.6 143,529 1

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

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17358kB
34. 136.027 886.670 ↓ 7.6 143,529 1

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

35. 160.851 750.643 ↓ 7.6 143,529 1

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

36. 519.070 589.792 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=519.084..589.792 rows=143,529 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: 26321kB
37. 54.087 70.722 ↓ 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=18.750..70.722 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
38. 16.635 16.635 ↓ 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.635..16.635 rows=144,981 loops=1)

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

CTE factset_prev_fund_holdings

40. 95.107 4,096.287 ↑ 2.0 83,931 1

WindowAgg (cost=186,596.56..192,269.63 rows=164,893 width=46) (actual time=3,809.319..4,096.287 rows=83,931 loops=1)

41. 117.227 4,001.180 ↑ 2.0 83,931 1

Merge Left Join (cost=186,596.56..189,384.00 rows=164,893 width=54) (actual time=3,809.309..4,001.180 rows=83,931 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))
  • Join Filter: (adjh_1.fsym_id = sar_1.fsym_id)
42. 160.688 3,030.993 ↑ 2.0 83,931 1

Sort (cost=112,644.75..113,056.98 rows=164,893 width=46) (actual time=3,002.489..3,030.993 rows=83,931 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id, ad_1.report_date DESC
  • Sort Method: quicksort Memory: 14867kB
43. 212.902 2,870.305 ↑ 2.0 83,931 1

Merge Join (cost=95,591.96..98,355.81 rows=164,893 width=46) (actual time=2,381.012..2,870.305 rows=83,931 loops=1)

  • Merge Cond: (ad_1.factset_fund_id = own_fund_detail_2.factset_fund_id)
44. 786.570 2,235.719 ↓ 329.0 576,752 1

Sort (cost=23,492.87..23,497.25 rows=1,753 width=58) (actual time=2,048.924..2,235.719 rows=576,752 loops=1)

  • Sort Key: ad_1.factset_fund_id
  • Sort Method: external merge Disk: 24808kB
45. 404.817 1,449.149 ↓ 329.0 576,752 1

Nested Loop (cost=4,917.71..23,398.42 rows=1,753 width=58) (actual time=112.057..1,449.149 rows=576,752 loops=1)

46. 0.029 0.029 ↑ 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.026..0.029 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
47. 530.644 1,044.303 ↓ 329.0 576,752 1

Hash Join (cost=4,917.29..23,372.45 rows=1,753 width=49) (actual time=112.026..1,044.303 rows=576,752 loops=1)

  • Hash Cond: (ad_1.factset_fund_id = ent_1.factset_fund_id)
48. 401.798 401.798 ↓ 188.1 577,648 1

CTE Scan on report_dates ad_1 (cost=0.00..18,426.12 rows=3,071 width=40) (actual time=0.009..401.798 rows=577,648 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 96077
49. 40.664 111.861 ↓ 1.0 68,140 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
50. 71.197 71.197 ↓ 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.016..71.197 rows=68,140 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51192
51. 19.854 421.684 ↓ 4.5 85,348 1

Materialize (cost=72,099.09..72,428.28 rows=18,809 width=42) (actual time=332.081..421.684 rows=85,348 loops=1)

52. 35.295 401.830 ↑ 2.4 7,979 1

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

53. 253.147 366.535 ↓ 7.6 143,529 1

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

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 17358kB
54. 86.362 113.388 ↓ 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=31.041..113.388 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
55. 27.026 27.026 ↓ 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=27.026..27.026 rows=144,981 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
56. 314.820 852.960 ↓ 7.6 143,529 1

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

  • Sort Key: adjh_1.factset_fund_id, adjh_1.report_date DESC
  • Sort Method: quicksort Memory: 17358kB
57. 60.332 538.140 ↓ 7.6 143,529 1

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

58. 73.312 477.808 ↓ 7.6 143,529 1

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

59. 333.738 404.496 ↓ 7.6 143,529 1

Sort (cost=72,099.09..72,146.13 rows=18,816 width=54) (actual time=368.091..404.496 rows=143,529 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: 26321kB
60. 53.778 70.758 ↓ 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.105..70.758 rows=143,529 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13494
61. 16.980 16.980 ↓ 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.979..16.980 rows=144,981 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
62. 13.966 5,968.202 ↓ 8.0 6,617 1

Merge Left Join (cost=7,500.00..7,512.53 rows=824 width=112) (actual time=5,949.188..5,968.202 rows=6,617 loops=1)

  • 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))
63. 11.049 5,920.795 ↓ 8.0 6,617 1

Sort (cost=3,750.00..3,752.06 rows=824 width=92) (actual time=5,918.281..5,920.795 rows=6,617 loops=1)

  • Sort Key: current_factset_fund_holdings.fsym_id, current_factset_fund_holdings.factset_fund_id
  • Sort Method: quicksort Memory: 709kB
64. 5,909.746 5,909.746 ↓ 8.0 6,617 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..3,710.09 rows=824 width=92) (actual time=5,395.315..5,909.746 rows=6,617 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 71008
65. 10.796 33.441 ↓ 7.7 6,338 1

Sort (cost=3,750.00..3,752.06 rows=824 width=92) (actual time=30.901..33.441 rows=6,338 loops=1)

  • Sort Key: previous_factset_fund_holdings.fsym_id, previous_factset_fund_holdings.factset_fund_id
  • Sort Method: quicksort Memory: 688kB
66. 22.645 22.645 ↓ 7.7 6,338 1

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..3,710.09 rows=824 width=92) (actual time=0.008..22.645 rows=6,338 loops=1)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 71287
67. 7.859 4,155.338 ↓ 8.0 6,562 1

Sort (cost=3,750.00..3,752.06 rows=824 width=88) (actual time=4,152.276..4,155.338 rows=6,562 loops=1)

  • Sort Key: factset_prev_fund_holdings.fsym_id, factset_prev_fund_holdings.factset_fund_id
  • Sort Method: quicksort Memory: 705kB
68. 4,147.479 4,147.479 ↓ 8.0 6,562 1

CTE Scan on factset_prev_fund_holdings (cost=0.00..3,710.09 rows=824 width=88) (actual time=3,809.325..4,147.479 rows=6,562 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 77369
69. 59.553 59.553 ↑ 1.0 1 6,617

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

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
70. 22.193 43.068 ↑ 1.0 65,078 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3926kB
71. 20.875 20.875 ↑ 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..20.875 rows=65,078 loops=1)

72. 66.170 66.170 ↑ 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.010..0.010 rows=1 loops=6,617)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
73. 66.170 66.170 ↑ 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.010..0.010 rows=1 loops=6,617)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
74. 66.170 66.170 ↑ 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.010..0.010 rows=1 loops=6,617)

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

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

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
76. 0.109 0.188 ↑ 1.0 270 1

Hash (cost=4.70..4.70 rows=270 width=15) (actual time=0.187..0.188 rows=270 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
77. 0.079 0.079 ↑ 1.0 270 1

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

78. 0.006 0.012 ↑ 1.0 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 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)

80. 72.787 72.787 ↑ 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.011..0.011 rows=1 loops=6,617)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
81. 0.057 0.116 ↑ 1.0 185 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
82. 0.059 0.059 ↑ 1.0 185 1

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

83. 0.007 0.018 ↑ 1.0 19 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
84. 0.011 0.011 ↑ 1.0 19 1

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

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

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

SubPlan (forNested Loop Left Join)

87. 191.893 1,270.464 ↑ 1.0 1 6,617

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.192..0.192 rows=1 loops=6,617)

88. 29.959 1,078.571 ↓ 2.0 2 6,617

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.096..0.163 rows=2 loops=6,617)

89. 509.887 1,019.018 ↓ 2.0 2 6,617

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.092..0.154 rows=2 loops=6,617)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
90. 416.493 416.493 ↑ 1.0 270 6,611

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (actual time=0.002..0.063 rows=270 loops=6,611)

91. 13.234 92.638 ↓ 2.0 2 6,617

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.014..0.014 rows=2 loops=6,617)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 79.404 79.404 ↓ 2.0 2 6,617

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.010..0.012 rows=2 loops=6,617)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
93. 29.594 29.594 ↑ 1.0 1 14,797

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=14,797)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 3.867 ms
Execution time : 12,064.064 ms