explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wJAa : new

Settings
# exclusive inclusive rows x rows loops node
1. 61.154 131,632.545 ↓ 3,057.5 6,115 1

Nested Loop Left Join (cost=96,726.59..96,799.95 rows=2 width=640) (actual time=2,109.951..131,632.545 rows=6,115 loops=1)

2. 14.839 130,299.471 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,726.03..96,759.69 rows=1 width=495) (actual time=2,109.667..130,299.471 rows=6,115 loops=1)

3. 11.635 130,266.287 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,725.89..96,759.52 rows=1 width=431) (actual time=2,109.658..130,266.287 rows=6,115 loops=1)

4. 12.737 130,236.307 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,725.74..96,759.35 rows=1 width=415) (actual time=2,109.651..130,236.307 rows=6,115 loops=1)

5. 11.762 130,150.190 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,725.31..96,758.88 rows=1 width=420) (actual time=2,109.633..130,150.190 rows=6,115 loops=1)

6. 13.342 130,120.083 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,725.18..96,758.67 rows=1 width=354) (actual time=2,109.625..130,120.083 rows=6,115 loops=1)

7. 11.773 130,082.281 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,725.03..96,758.50 rows=1 width=342) (actual time=2,109.616..130,082.281 rows=6,115 loops=1)

8. 10.542 130,003.243 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,724.62..96,750.05 rows=1 width=326) (actual time=2,109.597..130,003.243 rows=6,115 loops=1)

9. 14.077 129,925.436 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,724.19..96,747.88 rows=1 width=307) (actual time=2,109.582..129,925.436 rows=6,115 loops=1)

10. 10.492 129,850.209 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,723.75..96,743.68 rows=1 width=276) (actual time=2,109.566..129,850.209 rows=6,115 loops=1)

11. 14.621 129,760.222 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,723.32..96,739.48 rows=1 width=239) (actual time=2,109.549..129,760.222 rows=6,115 loops=1)

12. 19.901 129,684.451 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,722.91..96,731.04 rows=1 width=223) (actual time=2,109.532..129,684.451 rows=6,115 loops=1)

13. 4,160.366 129,578.940 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=96,722.49..96,722.59 rows=1 width=192) (actual time=2,109.499..129,578.940 rows=6,115 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: 16642337
14.          

CTE quarter_query

15. 87.607 2,012.558 ↓ 354.9 89,790 1

WindowAgg (cost=96,658.98..96,709.84 rows=253 width=73) (actual time=1,799.600..2,012.558 rows=89,790 loops=1)

16.          

CTE distinct_funds

17. 17.098 175.184 ↓ 1.1 6,612 1

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

18. 117.620 158.086 ↓ 11.2 66,238 1

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

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 12381kB
19. 29.729 40.466 ↓ 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.277..40.466 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
20. 10.737 10.737 ↓ 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.736..10.737 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))
21.          

CTE distinct_holdings

22. 35.705 238.541 ↓ 11.2 66,238 1

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

23. 162.726 202.836 ↓ 11.2 66,238 1

Sort (cost=23,313.91..23,328.63 rows=5,890 width=54) (actual time=185.277..202.836 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
24. 29.650 40.110 ↓ 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.005..40.110 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
25. 10.460 10.460 ↓ 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.459..10.460 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))
26.          

CTE fund_dates

27. 322.097 322.097 ↓ 1.1 673,136 1

Seq Scan on q4_fund_dates (cost=0.00..33,672.26 rows=614,204 width=13) (actual time=0.014..322.097 rows=673,136 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Rows Removed by Filter: 955937
28. 79.754 1,924.951 ↓ 354.9 89,790 1

Merge Left Join (cost=16,226.37..16,272.80 rows=253 width=81) (actual time=1,799.591..1,924.951 rows=89,790 loops=1)

  • Merge Cond: ((s.factset_fund_id = adjh.factset_fund_id) AND (ad.report_date = adjh.report_date))
29. 234.333 1,455.953 ↓ 354.9 89,790 1

Sort (cost=15,739.82..15,740.46 rows=253 width=73) (actual time=1,429.453..1,455.953 rows=89,790 loops=1)

  • Sort Key: s.factset_fund_id, ad.report_date DESC
  • Sort Method: quicksort Memory: 15691kB
30. 38.558 1,221.620 ↓ 354.9 89,790 1

Nested Loop (cost=346.11..15,729.73 rows=253 width=73) (actual time=234.222..1,221.620 rows=89,790 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
32. 195.289 1,183.043 ↓ 354.9 89,790 1

Hash Join (cost=345.69..15,718.76 rows=253 width=100) (actual time=234.205..1,183.043 rows=89,790 loops=1)

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

CTE Scan on fund_dates ad (cost=0.00..15,355.10 rows=3,071 width=40) (actual time=0.016..753.577 rows=673,136 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
34. 2.817 234.177 ↓ 361.4 6,144 1

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

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

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

36. 179.054 179.054 ↓ 228.0 6,612 1

CTE Scan on distinct_funds s (cost=0.00..132.50 rows=29 width=96) (actual time=141.550..179.054 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.086 389.244 ↓ 11.2 66,238 1

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

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

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

40.          

CTE factset_fund_holdings

41. 1,827.946 1,827.946 ↓ 71,848.0 71,848 1

CTE Scan on quarter_query curr (cost=0.00..6.33 rows=1 width=100) (actual time=1,799.609..1,827.946 rows=71,848 loops=1)

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

CTE factset_prev_fund_holdings

43. 275.291 275.291 ↓ 77,617.0 77,617 1

CTE Scan on quarter_query prev (cost=0.00..6.33 rows=1 width=100) (actual time=0.014..275.291 rows=77,617 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 12173
44. 10,394.584 68,732.524 ↓ 6,115.0 6,115 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=112) (actual time=1,799.631..68,732.524 rows=6,115 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: 35870838
45. 1,810.880 1,810.880 ↓ 6,115.0 6,115 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=1,799.614..1,810.880 rows=6,115 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 65733
46. 56,527.060 56,527.060 ↓ 5,867.0 5,867 6,115

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=0.001..9.244 rows=5,867 loops=6,115)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 65981
47. 56,686.050 56,686.050 ↓ 2,722.0 2,722 6,115

CTE Scan on factset_prev_fund_holdings (cost=0.00..0.02 rows=1 width=88) (actual time=0.014..9.270 rows=2,722 loops=6,115)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 74895
48. 85.610 85.610 ↑ 1.0 1 6,115

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.014..0.014 rows=1 loops=6,115)

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

Index Scan using q4_fund_values_pkey on q4_fund_values (cost=0.41..8.43 rows=1 width=26) (actual time=0.010..0.010 rows=1 loops=6,115)

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

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

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

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,115)

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

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

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

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

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

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

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

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

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

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

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

Index Scan using metro_map_pkey on metro_map (cost=0.14..0.16 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=6,115)

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

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

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

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

  • 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. 195.680 1,198.540 ↑ 1.0 1 6,115

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.195..0.196 rows=1 loops=6,115)

62. 33.616 1,002.860 ↓ 2.0 2 6,115

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.101..0.164 rows=2 loops=6,115)

63. 458.985 941.710 ↓ 2.0 2 6,115

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

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

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

65. 24.460 116.185 ↓ 2.0 2 6,115

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

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

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.013..0.015 rows=2 loops=6,115)

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

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,767)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 3.843 ms
Execution time : 131,648.639 ms