explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rmO3 : new

Settings
# exclusive inclusive rows x rows loops node
1. 6.168 11,771.028 ↓ 379.0 758 1

Nested Loop Left Join (cost=147,256.36..147,329.99 rows=2 width=640) (actual time=1,905.231..11,771.028 rows=758 loops=1)

2. 1.209 11,600.374 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,255.79..147,289.73 rows=1 width=495) (actual time=1,904.658..11,600.374 rows=758 loops=1)

3. 1.067 11,596.891 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,255.66..147,289.56 rows=1 width=431) (actual time=1,904.646..11,596.891 rows=758 loops=1)

4. 1.419 11,593.550 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,255.51..147,289.39 rows=1 width=415) (actual time=1,904.636..11,593.550 rows=758 loops=1)

5. 1.589 11,583.793 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,255.08..147,288.92 rows=1 width=420) (actual time=1,904.614..11,583.793 rows=758 loops=1)

6. 1.213 11,580.688 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,254.95..147,288.71 rows=1 width=354) (actual time=1,904.604..11,580.688 rows=758 loops=1)

7. 1.774 11,576.443 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,254.80..147,288.54 rows=1 width=342) (actual time=1,904.592..11,576.443 rows=758 loops=1)

8. 1.138 11,567.089 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,254.38..147,280.09 rows=1 width=326) (actual time=1,904.567..11,567.089 rows=758 loops=1)

9. 1.125 11,557.613 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,253.95..147,277.92 rows=1 width=307) (actual time=1,904.545..11,557.613 rows=758 loops=1)

10. 1.351 11,548.150 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,253.52..147,273.72 rows=1 width=276) (actual time=1,904.523..11,548.150 rows=758 loops=1)

11. 1.227 11,537.703 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,253.09..147,269.52 rows=1 width=239) (actual time=1,904.498..11,537.703 rows=758 loops=1)

12. 2.053 11,528.896 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,252.67..147,261.08 rows=1 width=223) (actual time=1,904.474..11,528.896 rows=758 loops=1)

13. 647.867 11,516.989 ↓ 758.0 758 1

Nested Loop Left Join (cost=147,252.26..147,252.62 rows=1 width=192) (actual time=1,904.441..11,516.989 rows=758 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: 2257324
14.          

CTE quarter_query

15. 102.445 1,736.014 ↓ 98.7 96,978 1

WindowAgg (cost=147,036.39..147,203.11 rows=983 width=73) (actual time=1,447.458..1,736.014 rows=96,978 loops=1)

16.          

CTE distinct_funds

17. 35.401 335.040 ↑ 2.4 7,978 1

Unique (cost=71,911.06..72,004.89 rows=18,760 width=54) (actual time=260.477..335.040 rows=7,978 loops=1)

18. 229.929 299.639 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=260.475..299.639 rows=143,507 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 26319kB
19. 52.603 69.710 ↓ 7.6 143,507 1

Bitmap Heap Scan on own_fund_detail (cost=442.01..70,578.98 rows=18,767 width=54) (actual time=19.229..69.710 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
20. 17.107 17.107 ↓ 7.7 145,011 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (actual time=17.107..17.107 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
21. 115.309 1,633.569 ↓ 98.7 96,978 1

Merge Left Join (cost=75,031.50..75,181.01 rows=983 width=81) (actual time=1,447.446..1,633.569 rows=96,978 loops=1)

  • Merge Cond: ((s.factset_fund_id = adjh.factset_fund_id) AND (q4_fund_dates.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
22. 184.092 741.833 ↓ 98.7 96,978 1

Sort (cost=1,272.90..1,275.35 rows=983 width=73) (actual time=710.680..741.833 rows=96,978 loops=1)

  • Sort Key: s.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 16703kB
23. 48.836 557.741 ↓ 98.7 96,978 1

Nested Loop (cost=1.26..1,224.04 rows=983 width=73) (actual time=260.588..557.741 rows=96,978 loops=1)

  • Join Filter: (s.factset_fund_id = q4_fund_dates.factset_fund_id)
24. 2.961 407.925 ↓ 124.7 6,732 1

Nested Loop (cost=0.84..1,097.14 rows=54 width=78) (actual time=260.530..407.925 rows=6,732 loops=1)

25. 0.017 0.017 ↑ 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.017 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
26. 8.930 404.947 ↓ 124.7 6,732 1

Nested Loop (cost=0.42..1,088.16 rows=54 width=105) (actual time=260.513..404.947 rows=6,732 loops=1)

27. 340.171 340.171 ↓ 84.9 7,978 1

CTE Scan on distinct_funds s (cost=0.00..422.10 rows=94 width=96) (actual time=260.487..340.171 rows=7,978 loops=1)

  • Filter: (fsym_id = 'BHM9TN-S'::bpchar)
28. 55.846 55.846 ↑ 1.0 1 7,978

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

  • Index Cond: ((factset_fund_id = s.factset_fund_id) AND (active_flag = 1))
  • Heap Fetches: 5463
29. 100.980 100.980 ↑ 1.3 14 6,732

Index Only Scan using q4_fund_dates_pkey on q4_fund_dates (cost=0.43..2.12 rows=18 width=13) (actual time=0.009..0.015 rows=14 loops=6,732)

  • Index Cond: ((factset_fund_id = ent.factset_fund_id) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-07'::date))
  • Heap Fetches: 96978
30. 233.005 776.427 ↓ 7.6 143,507 1

Sort (cost=73,758.61..73,805.51 rows=18,760 width=30) (actual time=736.755..776.427 rows=143,507 loops=1)

  • Sort Key: adjh.factset_fund_id, adjh.report_date DESC
  • Sort Method: quicksort Memory: 17356kB
31. 61.416 543.422 ↓ 7.6 143,507 1

Subquery Scan on adjh (cost=71,911.06..72,427.08 rows=18,760 width=30) (actual time=371.927..543.422 rows=143,507 loops=1)

32. 74.186 482.006 ↓ 7.6 143,507 1

Unique (cost=71,911.06..72,239.48 rows=18,760 width=54) (actual time=371.925..482.006 rows=143,507 loops=1)

33. 337.356 407.820 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=371.923..407.820 rows=143,507 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: 26319kB
34. 53.847 70.464 ↓ 7.6 143,507 1

Bitmap Heap Scan on own_fund_detail own_fund_detail_1 (cost=442.01..70,578.98 rows=18,767 width=54) (actual time=18.723..70.464 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
35. 16.617 16.617 ↓ 7.7 145,011 1

Bitmap Index Scan on own_fund_detail_fsym_id_report_date_idx (cost=0.00..437.32 rows=18,767 width=0) (actual time=16.617..16.617 rows=145,011 loops=1)

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

CTE factset_fund_holdings

37. 1,787.224 1,787.224 ↓ 172.6 863 1

CTE Scan on quarter_query curr (cost=0.00..24.58 rows=5 width=100) (actual time=1,447.702..1,787.224 rows=863 loops=1)

  • Filter: ((report_date >= '2019-05-31'::date) AND (report_date <= '2019-06-07'::date))
  • Rows Removed by Filter: 96115
38.          

CTE factset_prev_fund_holdings

39. 61.294 61.294 ↓ 16,786.4 83,932 1

CTE Scan on quarter_query prev (cost=0.00..24.58 rows=5 width=100) (actual time=0.010..61.294 rows=83,932 loops=1)

  • Filter: ((report_date >= '2016-12-31'::date) AND (report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 13046
40. 10.153 1,906.530 ↓ 758.0 758 1

Nested Loop Left Join (cost=0.00..0.24 rows=1 width=112) (actual time=1,787.743..1,906.530 rows=758 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: 25738
41. 1,448.399 1,448.399 ↓ 758.0 758 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.11 rows=1 width=92) (actual time=1,447.705..1,448.399 rows=758 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 105
42. 447.978 447.978 ↓ 34.0 34 758

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.11 rows=1 width=92) (actual time=0.016..0.591 rows=34 loops=758)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 829
43. 8,962.592 8,962.592 ↓ 2,978.0 2,978 758

CTE Scan on factset_prev_fund_holdings (cost=0.00..0.11 rows=1 width=88) (actual time=0.012..11.824 rows=2,978 loops=758)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 80954
44. 9.854 9.854 ↑ 1.0 1 758

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.013..0.013 rows=1 loops=758)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
45. 7.580 7.580 ↑ 1.0 1 758

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.010 rows=1 loops=758)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
46. 9.096 9.096 ↑ 1.0 1 758

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=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
47. 8.338 8.338 ↑ 1.0 1 758

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=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
48. 8.338 8.338 ↑ 1.0 1 758

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=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
49. 7.580 7.580 ↑ 1.0 1 758

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.010 rows=1 loops=758)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
50. 3.032 3.032 ↑ 1.0 1 758

Index Scan using country_map_pkey on country_map (cost=0.15..0.17 rows=1 width=15) (actual time=0.003..0.004 rows=1 loops=758)

  • Index Cond: (iso_country = fund_entity.iso_country)
51. 1.516 1.516 ↑ 1.0 1 758

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=758)

  • Index Cond: (region_code = country_map.region_code)
52. 8.338 8.338 ↑ 1.0 1 758

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=758)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
53. 2.274 2.274 ↑ 1.0 1 758

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

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
54. 2.274 2.274 ↑ 1.0 1 758

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=758)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
55. 8.338 8.338 ↑ 2.0 1 758

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.011 rows=1 loops=758)

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

SubPlan (forNested Loop Left Join)

57. 22.740 156.148 ↑ 1.0 1 758

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.205..0.206 rows=1 loops=758)

58. 3.908 133.408 ↓ 2.0 2 758

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.112..0.176 rows=2 loops=758)

59. 63.048 126.586 ↓ 2.0 2 758

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.107..0.167 rows=2 loops=758)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
60. 50.652 50.652 ↑ 1.0 270 756

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (actual time=0.002..0.067 rows=270 loops=756)

61. 2.274 12.886 ↓ 2.0 2 758

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.017..0.017 rows=2 loops=758)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 10.612 10.612 ↓ 2.0 2 758

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.012..0.014 rows=2 loops=758)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
63. 2.914 2.914 ↑ 1.0 1 1,457

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

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 3.642 ms
Execution time : 11,777.948 ms