explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t3Sh

Settings
# exclusive inclusive rows x rows loops node
1. 40.662 30,632.723 ↓ 1,228.8 6,144 1

Nested Loop Left Join (cost=112,964.49..113,192.83 rows=5 width=640) (actual time=2,700.436..30,632.723 rows=6,144 loops=1)

2. 6.735 29,400.125 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,963.92..113,086.47 rows=3 width=495) (actual time=2,700.091..29,400.125 rows=6,144 loops=1)

3. 9.625 29,374.958 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,963.78..113,085.98 rows=3 width=431) (actual time=2,700.080..29,374.958 rows=6,144 loops=1)

4. 11.848 29,353.045 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,963.64..113,085.46 rows=3 width=415) (actual time=2,700.072..29,353.045 rows=6,144 loops=1)

5. 9.356 29,279.757 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,963.21..113,084.04 rows=3 width=420) (actual time=2,700.035..29,279.757 rows=6,144 loops=1)

6. 9.933 29,258.113 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,963.08..113,083.43 rows=3 width=354) (actual time=2,700.021..29,258.113 rows=6,144 loops=1)

7. 9.520 29,229.748 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,962.93..113,082.90 rows=3 width=342) (actual time=2,700.011..29,229.748 rows=6,144 loops=1)

8. 6.830 29,158.788 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,962.51..113,057.57 rows=3 width=326) (actual time=2,699.990..29,158.788 rows=6,144 loops=1)

9. 8.948 29,090.518 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,962.08..113,051.04 rows=3 width=307) (actual time=2,699.960..29,090.518 rows=6,144 loops=1)

10. 10.731 29,013.986 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,961.65..113,038.45 rows=3 width=276) (actual time=2,699.928..29,013.986 rows=6,144 loops=1)

11. 10.076 28,929.527 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,961.22..113,025.86 rows=3 width=239) (actual time=2,699.894..28,929.527 rows=6,144 loops=1)

12. 10.383 28,864.155 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,960.80..113,000.53 rows=3 width=223) (actual time=2,699.863..28,864.155 rows=6,144 loops=1)

13. 4,224.458 28,786.188 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=112,960.38..112,975.16 rows=3 width=192) (actual time=2,699.833..28,786.188 rows=6,144 loops=1)

  • Join Filter: (current_factset_fund_holdings.factset_fund_id = factset_prev_fund_holdings.factset_fund_id)
  • Rows Removed by Join Filter: 16721246
14.          

CTE distinct_funds

15. 16.940 189.890 ↓ 1.1 6,612 1

Unique (cost=23,313.91..23,343.36 rows=5,889 width=54) (actual time=155.122..189.890 rows=6,612 loops=1)

16. 120.092 172.950 ↓ 11.2 66,238 1

Sort (cost=23,313.91..23,328.63 rows=5,890 width=54) (actual time=155.121..172.950 rows=66,238 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 12381kB
17. 41.503 52.858 ↓ 11.2 66,238 1

Bitmap Heap Scan on own_fund_detail (cost=171.67..22,945.08 rows=5,890 width=54) (actual time=12.925..52.858 rows=66,238 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Heap Blocks: exact=10293
18. 11.355 11.355 ↓ 11.5 67,669 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..170.19 rows=5,890 width=0) (actual time=11.355..11.355 rows=67,669 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
19.          

CTE distinct_holdings

20. 35.995 239.560 ↓ 11.2 66,238 1

Unique (cost=23,313.91..23,416.98 rows=5,889 width=54) (actual time=186.468..239.560 rows=66,238 loops=1)

21. 164.335 203.565 ↓ 11.2 66,238 1

Sort (cost=23,313.91..23,328.63 rows=5,890 width=54) (actual time=186.465..203.565 rows=66,238 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: 12381kB
22. 28.472 39.230 ↓ 11.2 66,238 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=171.67..22,945.08 rows=5,890 width=54) (actual time=12.323..39.230 rows=66,238 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Heap Blocks: exact=10293
23. 10.758 10.758 ↓ 11.5 67,669 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..170.19 rows=5,890 width=0) (actual time=10.758..10.758 rows=67,669 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
24.          

CTE fund_dates

25. 464.743 464.743 ↓ 1.0 1,629,073 1

Seq Scan on q4_fund_dates (cost=0.00..25,795.51 rows=1,575,351 width=13) (actual time=0.009..464.743 rows=1,629,073 loops=1)

26.          

CTE holders_query

27. 88.539 2,646.211 ↓ 138.1 89,790 1

WindowAgg (cost=40,307.40..40,368.78 rows=650 width=73) (actual time=2,433.793..2,646.211 rows=89,790 loops=1)

28. 77.872 2,557.672 ↓ 138.1 89,790 1

Merge Left Join (cost=40,307.40..40,357.40 rows=650 width=81) (actual time=2,433.786..2,557.672 rows=89,790 loops=1)

  • Merge Cond: ((s.factset_fund_id = adjh.factset_fund_id) AND (ad.report_date = adjh.report_date))
29. 235.303 2,089.330 ↓ 138.1 89,790 1

Sort (cost=39,820.86..39,822.48 rows=650 width=73) (actual time=2,062.466..2,089.330 rows=89,790 loops=1)

  • Sort Key: s.factset_fund_id, ad.report_date DESC
  • Sort Method: quicksort Memory: 15691kB
30. 39.165 1,854.027 ↓ 138.1 89,790 1

Nested Loop (cost=346.11..39,790.49 rows=650 width=73) (actual time=252.479..1,854.027 rows=89,790 loops=1)

31. 0.042 0.042 ↑ 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.038..0.042 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
32. 198.264 1,814.820 ↓ 138.1 89,790 1

Hash Join (cost=345.69..39,775.55 rows=650 width=100) (actual time=252.439..1,814.820 rows=89,790 loops=1)

  • Hash Cond: (ad.factset_fund_id = s.factset_fund_id)
33. 1,364.184 1,364.184 ↓ 85.5 673,136 1

CTE Scan on fund_dates ad (cost=0.00..39,383.78 rows=7,877 width=40) (actual time=0.048..1,364.184 rows=673,136 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Rows Removed by Filter: 955937
34. 2.813 252.372 ↓ 361.4 6,144 1

Hash (cost=345.48..345.48 rows=17 width=105) (actual time=252.372..252.372 rows=6,144 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 604kB
35. 9.081 249.559 ↓ 361.4 6,144 1

Nested Loop (cost=0.42..345.48 rows=17 width=105) (actual time=155.186..249.559 rows=6,144 loops=1)

36. 194.194 194.194 ↓ 228.0 6,612 1

CTE Scan on distinct_funds s (cost=0.00..132.50 rows=29 width=96) (actual time=155.134..194.194 rows=6,612 loops=1)

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
37. 46.284 46.284 ↑ 1.0 1 6,612

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.33 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=6,612)

  • Index Cond: ((factset_fund_id = s.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 4973
38. 113.183 390.470 ↓ 11.2 66,238 1

Sort (cost=486.54..501.27 rows=5,889 width=48) (actual time=371.313..390.470 rows=66,238 loops=1)

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 8244kB
39. 277.287 277.287 ↓ 11.2 66,238 1

CTE Scan on distinct_holdings adjh (cost=0.00..117.78 rows=5,889 width=48) (actual time=186.474..277.287 rows=66,238 loops=1)

40.          

CTE factset_fund_holdings

41. 15.435 15.435 ↓ 5,867.0 5,867 1

CTE Scan on holders_query curr (cost=0.00..17.88 rows=1 width=100) (actual time=0.012..15.435 rows=5,867 loops=1)

  • Filter: ((report_date >= '2017-06-07'::date) AND (report_date <= '2019-06-07'::date) AND (pos = 2))
  • Rows Removed by Filter: 83923
42.          

CTE factset_prev_fund_holdings

43. 263.672 263.672 ↓ 2,722.0 2,722 1

CTE Scan on holders_query prev (cost=0.00..17.88 rows=1 width=100) (actual time=0.406..263.672 rows=2,722 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date) AND (pos = 1))
  • Rows Removed by Filter: 87068
44. 9,118.964 20,021.314 ↓ 2,048.0 6,144 1

Nested Loop Left Join (cost=0.00..14.70 rows=3 width=112) (actual time=2,433.817..20,021.314 rows=6,144 loops=1)

  • Join Filter: (current_factset_fund_holdings.factset_fund_id = previous_factset_fund_holdings.factset_fund_id)
  • Rows Removed by Join Filter: 36040981
45. 2,448.206 2,448.206 ↓ 2,048.0 6,144 1

CTE Scan on holders_query current_factset_fund_holdings (cost=0.00..14.62 rows=3 width=92) (actual time=2,433.800..2,448.206 rows=6,144 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 83646
46. 8,454.144 8,454.144 ↓ 5,867.0 5,867 6,144

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.02 rows=1 width=56) (actual time=0.000..1.376 rows=5,867 loops=6,144)

47. 4,540.416 4,540.416 ↓ 2,722.0 2,722 6,144

CTE Scan on factset_prev_fund_holdings (cost=0.00..0.02 rows=1 width=52) (actual time=0.000..0.739 rows=2,722 loops=6,144)

48. 67.584 67.584 ↑ 1.0 1 6,144

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.010..0.011 rows=1 loops=6,144)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
49. 55.296 55.296 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
50. 73.728 73.728 ↑ 1.0 1 6,144

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
51. 67.584 67.584 ↑ 1.0 1 6,144

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.011 rows=1 loops=6,144)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
52. 61.440 61.440 ↑ 1.0 1 6,144

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

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
53. 61.440 61.440 ↑ 1.0 1 6,144

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..8.44 rows=1 width=26) (actual time=0.009..0.010 rows=1 loops=6,144)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
54. 18.432 18.432 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (iso_country = fund_entity.iso_country)
55. 12.288 12.288 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (region_code = country_map.region_code)
56. 61.440 61.440 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
57. 12.288 12.288 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
58. 18.432 18.432 ↑ 1.0 1 6,144

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=6,144)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
59. 55.296 55.296 ↑ 2.0 1 6,144

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.38 rows=2 width=45) (actual time=0.009..0.009 rows=1 loops=6,144)

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

SubPlan (forNested Loop Left Join)

61. 178.176 1,136.640 ↑ 1.0 1 6,144

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.184..0.185 rows=1 loops=6,144)

62. 21.440 958.464 ↓ 2.0 2 6,144

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.094..0.156 rows=2 loops=6,144)

63. 448.866 909.312 ↓ 2.0 2 6,144

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.089..0.148 rows=2 loops=6,144)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
64. 362.142 362.142 ↑ 1.0 270 6,138

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=6,138)

65. 12.288 98.304 ↓ 2.0 2 6,144

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
66. 86.016 86.016 ↓ 2.0 2 6,144

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.014 rows=2 loops=6,144)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
67. 27.712 27.712 ↑ 1.0 1 13,856

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=13,856)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 6.043 ms
Execution time : 30,650.289 ms