explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vu6h

Settings
# exclusive inclusive rows x rows loops node
1. 11.809 28,928.112 ↓ 380.5 761 1

Nested Loop Left Join (cost=403,203.28..404,846.50 rows=2 width=640) (actual time=9,790.243..28,928.112 rows=761 loops=1)

2. 2.263 28,640.060 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,202.71..404,806.23 rows=1 width=495) (actual time=9,788.320..28,640.060 rows=761 loops=1)

3. 2.117 28,633.992 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,202.58..404,806.07 rows=1 width=431) (actual time=9,787.836..28,633.992 rows=761 loops=1)

4. 2.229 28,628.831 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,202.43..404,805.90 rows=1 width=415) (actual time=9,787.826..28,628.831 rows=761 loops=1)

5. 1.842 28,613.665 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,202.00..404,805.42 rows=1 width=420) (actual time=9,787.793..28,613.665 rows=761 loops=1)

6. 2.581 28,608.779 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,201.87..404,805.22 rows=1 width=354) (actual time=9,787.774..28,608.779 rows=761 loops=1)

7. 2.199 28,602.393 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,201.72..404,805.04 rows=1 width=342) (actual time=9,787.761..28,602.393 rows=761 loops=1)

8. 1.892 28,581.169 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,201.30..404,796.60 rows=1 width=326) (actual time=9,787.718..28,581.169 rows=761 loops=1)

9. 7.081 28,564.818 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,200.87..404,794.42 rows=1 width=307) (actual time=9,787.675..28,564.818 rows=761 loops=1)

10. 2.405 28,543.278 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,200.44..404,790.23 rows=1 width=276) (actual time=9,787.646..28,543.278 rows=761 loops=1)

11. 1.960 28,523.370 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,200.01..404,786.03 rows=1 width=239) (actual time=9,787.608..28,523.370 rows=761 loops=1)

12. 3.077 28,275.607 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,199.59..404,777.58 rows=1 width=223) (actual time=9,785.510..28,275.607 rows=761 loops=1)

13. 1,336.262 28,200.235 ↓ 761.0 761 1

Nested Loop Left Join (cost=403,199.18..404,769.13 rows=1 width=192) (actual time=9,785.488..28,200.235 rows=761 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: 2266258
14.          

CTE factset_fund_holdings

15. 45.002 6,666.573 ↓ 1,267.0 1,267 1

Subquery Scan on current_qtr (cost=193,294.15..201,599.59 rows=1 width=46) (actual time=6,149.513..6,666.573 rows=1,267 loops=1)

  • Filter: ((current_qtr.report_date >= '2019-05-30'::date) AND (current_qtr.report_date <= '2019-06-06'::date))
  • Rows Removed by Filter: 95711
16. 170.254 6,621.571 ↑ 2.0 96,978 1

WindowAgg (cost=193,294.15..198,657.32 rows=196,151 width=46) (actual time=6,149.100..6,621.571 rows=96,978 loops=1)

17. 189.884 6,451.317 ↑ 2.0 96,978 1

Merge Left Join (cost=193,294.15..195,224.68 rows=196,151 width=54) (actual time=6,149.091..6,451.317 rows=96,978 loops=1)

  • Merge Cond: ((own_fund_detail.factset_fund_id = adjh.factset_fund_id) AND (q4_fund_dates.report_date = adjh.report_date))
  • Join Filter: (adjh.fsym_id = sar.fsym_id)
18. 235.344 5,199.839 ↑ 2.0 96,978 1

Sort (cost=119,535.54..120,025.92 rows=196,151 width=46) (actual time=5,152.269..5,199.839 rows=96,978 loops=1)

  • Sort Key: own_fund_detail.factset_fund_id, q4_fund_dates.report_date DESC
  • Sort Method: quicksort Memory: 16703kB
19. 62.037 4,964.495 ↑ 2.0 96,978 1

Nested Loop (cost=76,608.56..102,292.30 rows=196,151 width=46) (actual time=4,669.542..4,964.495 rows=96,978 loops=1)

  • Join Filter: (own_fund_detail.factset_fund_id = q4_fund_dates.factset_fund_id)
20. 3.520 4,767.818 ↑ 1.6 6,732 1

Nested Loop (cost=76,608.13..77,181.65 rows=10,686 width=51) (actual time=4,669.489..4,767.818 rows=6,732 loops=1)

21. 1.952 1.952 ↑ 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=1.951..1.952 rows=1 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
22. 8.729 4,762.346 ↑ 1.6 6,732 1

Hash Join (cost=76,607.71..77,066.35 rows=10,686 width=51) (actual time=4,667.535..4,762.346 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail.factset_fund_id = ent.factset_fund_id)
23. 42.963 4,644.276 ↑ 2.4 7,978 1

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

24. 280.174 4,601.313 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail.factset_fund_id
  • Sort Method: quicksort Memory: 17356kB
25. 4,269.042 4,321.139 ↓ 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=56.003..4,321.139 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
26. 52.097 52.097 ↓ 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=52.096..52.097 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
27. 36.763 109.341 ↓ 1.0 68,142 1

Hash (cost=3,847.24..3,847.24 rows=67,953 width=9) (actual time=109.341..109.341 rows=68,142 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
28. 72.578 72.578 ↓ 1.0 68,142 1

Seq Scan on own_ent_funds ent (cost=0.00..3,847.24 rows=67,953 width=9) (actual time=0.012..72.578 rows=68,142 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51182
29. 134.640 134.640 ↑ 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.014..0.020 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-06'::date))
  • Heap Fetches: 96978
30. 426.470 1,061.594 ↓ 7.6 143,507 1

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

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

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

32. 105.824 549.515 ↓ 7.6 143,507 1

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

33. 359.347 443.691 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=392.356..443.691 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. 61.313 84.344 ↓ 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=25.280..84.344 rows=143,507 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
35. 23.031 23.031 ↓ 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=23.030..23.031 rows=145,011 loops=1)

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

CTE factset_prev_fund_holdings

37. 53.283 3,058.988 ↓ 1.2 83,932 1

Subquery Scan on prev_qtr (cost=193,294.15..201,599.59 rows=69,541 width=46) (actual time=2,658.598..3,058.988 rows=83,932 loops=1)

  • Filter: ((prev_qtr.report_date >= '2016-12-31'::date) AND (prev_qtr.report_date <= '2018-12-31'::date))
  • Rows Removed by Filter: 13046
38. 124.258 3,005.705 ↑ 2.0 96,978 1

WindowAgg (cost=193,294.15..198,657.32 rows=196,151 width=46) (actual time=2,658.568..3,005.705 rows=96,978 loops=1)

39. 138.791 2,881.447 ↑ 2.0 96,978 1

Merge Left Join (cost=193,294.15..195,224.68 rows=196,151 width=54) (actual time=2,658.560..2,881.447 rows=96,978 loops=1)

  • Merge Cond: ((own_fund_detail_2.factset_fund_id = adjh_1.factset_fund_id) AND (q4_fund_dates_1.report_date = adjh_1.report_date))
  • Join Filter: (adjh_1.fsym_id = sar_1.fsym_id)
40. 332.945 1,347.740 ↑ 2.0 96,978 1

Sort (cost=119,535.54..120,025.92 rows=196,151 width=46) (actual time=1,311.014..1,347.740 rows=96,978 loops=1)

  • Sort Key: own_fund_detail_2.factset_fund_id, q4_fund_dates_1.report_date DESC
  • Sort Method: quicksort Memory: 16703kB
41. 105.370 1,014.795 ↑ 2.0 96,978 1

Nested Loop (cost=76,608.56..102,292.30 rows=196,151 width=46) (actual time=555.952..1,014.795 rows=96,978 loops=1)

  • Join Filter: (own_fund_detail_2.factset_fund_id = q4_fund_dates_1.factset_fund_id)
42. 5.055 727.661 ↑ 1.6 6,732 1

Nested Loop (cost=76,608.13..77,181.65 rows=10,686 width=51) (actual time=555.915..727.661 rows=6,732 loops=1)

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

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Fetches: 1
44. 15.966 722.586 ↑ 1.6 6,732 1

Hash Join (cost=76,607.71..77,066.35 rows=10,686 width=51) (actual time=555.893..722.586 rows=6,732 loops=1)

  • Hash Cond: (own_fund_detail_2.factset_fund_id = ent_1.factset_fund_id)
45. 66.702 640.685 ↑ 2.4 7,978 1

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

46. 443.452 573.983 ↓ 7.6 143,507 1

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

  • Sort Key: own_fund_detail_2.factset_fund_id
  • Sort Method: quicksort Memory: 17356kB
47. 111.279 130.531 ↓ 7.6 143,507 1

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

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
48. 19.252 19.252 ↓ 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=19.252..19.252 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
49. 24.077 65.935 ↓ 1.0 68,142 1

Hash (cost=3,847.24..3,847.24 rows=67,953 width=9) (actual time=65.934..65.935 rows=68,142 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3753kB
50. 41.858 41.858 ↓ 1.0 68,142 1

Seq Scan on own_ent_funds ent_1 (cost=0.00..3,847.24 rows=67,953 width=9) (actual time=0.013..41.858 rows=68,142 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 51182
51. 181.764 181.764 ↑ 1.3 14 6,732

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

  • Index Cond: ((factset_fund_id = ent_1.factset_fund_id) AND (report_date >= '2016-12-31'::date) AND (report_date <= '2019-06-06'::date))
  • Heap Fetches: 96978
52. 369.314 1,394.916 ↓ 7.6 143,507 1

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

  • Sort Key: adjh_1.factset_fund_id, adjh_1.report_date DESC
  • Sort Method: quicksort Memory: 17356kB
53. 112.608 1,025.602 ↓ 7.6 143,507 1

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

54. 133.488 912.994 ↓ 7.6 143,507 1

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

55. 659.532 779.506 ↓ 7.6 143,507 1

Sort (cost=71,911.06..71,957.98 rows=18,767 width=54) (actual time=709.907..779.506 rows=143,507 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: 26319kB
56. 92.559 119.974 ↓ 7.6 143,507 1

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

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Heap Blocks: exact=13533
57. 27.415 27.415 ↓ 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=27.415..27.415 rows=145,011 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
58. 174.057 7,170.054 ↓ 761.0 761 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=112) (actual time=6,668.169..7,170.054 rows=761 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: 306280
59. 6,150.526 6,150.526 ↓ 761.0 761 1

CTE Scan on factset_fund_holdings current_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=6,149.519..6,150.526 rows=761 loops=1)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 506
60. 845.471 845.471 ↓ 403.0 403 761

CTE Scan on factset_fund_holdings previous_factset_fund_holdings (cost=0.00..0.02 rows=1 width=92) (actual time=0.027..1.111 rows=403 loops=761)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 864
61. 19,693.919 19,693.919 ↓ 8.6 2,978 761

CTE Scan on factset_prev_fund_holdings (cost=0.00..1,564.67 rows=348 width=88) (actual time=3.514..25.879 rows=2,978 loops=761)

  • Filter: (pos = 1)
  • Rows Removed by Filter: 80954
62. 72.295 72.295 ↑ 1.0 1 761

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.092..0.095 rows=1 loops=761)

  • Index Cond: (current_factset_fund_holdings.factset_fund_id = factset_fund_id)
63. 245.803 245.803 ↑ 1.0 1 761

Index Scan using q4_fund_values_pkey on q4_fund_values (cost=0.41..8.43 rows=1 width=26) (actual time=0.314..0.323 rows=1 loops=761)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
64. 17.503 17.503 ↑ 1.0 1 761

Index Scan using sym_entity_pkey on sym_entity inst_entity (cost=0.43..4.19 rows=1 width=37) (actual time=0.023..0.023 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
65. 14.459 14.459 ↑ 1.0 1 761

Index Scan using sym_entity_pkey on sym_entity fund_entity (cost=0.43..4.19 rows=1 width=40) (actual time=0.018..0.019 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
66. 14.459 14.459 ↑ 1.0 1 761

Index Scan using h_entity_pkey on h_entity (cost=0.43..2.16 rows=1 width=37) (actual time=0.018..0.019 rows=1 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_fund_id)
67. 19.025 19.025 ↑ 1.0 1 761

Index Scan using cyclops_fund_overwrites_pkey on cyclops_fund_overwrites cyclops (cost=0.42..8.44 rows=1 width=26) (actual time=0.024..0.025 rows=1 loops=761)

  • Index Cond: (factset_fund_id = current_factset_fund_holdings.factset_fund_id)
68. 3.805 3.805 ↑ 1.0 1 761

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

  • Index Cond: (iso_country = fund_entity.iso_country)
69. 3.044 3.044 ↑ 1.0 1 761

Index Scan using region_map_pkey on region_map (cost=0.13..0.19 rows=1 width=76) (actual time=0.003..0.004 rows=1 loops=761)

  • Index Cond: (region_code = country_map.region_code)
70. 12.937 12.937 ↑ 1.0 1 761

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.016..0.017 rows=1 loops=761)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
71. 3.044 3.044 ↑ 1.0 1 761

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

  • Index Cond: (metro_code = ent_entity_metro_areas.metro_id)
72. 3.805 3.805 ↑ 1.0 1 761

Index Scan using fund_type_map_pkey on fund_type_map (cost=0.14..0.16 rows=1 width=84) (actual time=0.005..0.005 rows=1 loops=761)

  • Index Cond: (fund_type_code = own_ent_funds.fund_type)
73. 17.503 17.503 ↑ 2.0 1 761

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.38 rows=2 width=45) (actual time=0.023..0.023 rows=1 loops=761)

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

SubPlan (forNested Loop Left Join)

75. 41.094 258.740 ↑ 1.0 1 761

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.340..0.340 rows=1 loops=761)

76. 6.412 217.646 ↓ 2.0 2 761

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.180..0.286 rows=2 loops=761)

77. 96.867 202.426 ↓ 2.0 2 761

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.166..0.266 rows=2 loops=761)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
78. 83.490 83.490 ↑ 1.0 270 759

Seq Scan on country_map country_map_1 (cost=0.00..4.70 rows=270 width=15) (actual time=0.003..0.110 rows=270 loops=759)

79. 3.805 22.069 ↓ 2.0 2 761

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.029..0.029 rows=2 loops=761)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 18.264 18.264 ↓ 2.0 2 761

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.021..0.024 rows=2 loops=761)

  • Index Cond: (factset_entity_id = own_ent_funds.factset_inst_entity_id)
81. 8.808 8.808 ↑ 1.0 1 1,468

Index Scan using region_map_pkey on region_map region_map_1 (cost=0.13..0.19 rows=1 width=76) (actual time=0.005..0.006 rows=1 loops=1,468)

  • Index Cond: (region_code = country_map_1.region_code)