explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XYli

Settings
# exclusive inclusive rows x rows loops node
1. 21.267 1,214.847 ↓ 17.0 3,273 1

Nested Loop Left Join (cost=145,709.05..158,947.49 rows=193 width=668) (actual time=280.304..1,214.847 rows=3,273 loops=1)

2. 5.345 604.440 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,708.48..154,223.01 rows=170 width=589) (actual time=280.010..604.440 rows=3,273 loops=1)

3. 3.309 576.184 ↓ 19.3 3,273 1

Hash Left Join (cost=145,708.06..154,147.66 rows=170 width=586) (actual time=279.996..576.184 rows=3,273 loops=1)

  • Hash Cond: (own_ent_institutions.entity_sub_type = entity_sub_type_map.entity_sub_type_code)
4. 4.864 572.849 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,706.43..154,143.69 rows=170 width=511) (actual time=279.958..572.849 rows=3,273 loops=1)

5. 3.397 545.074 ↓ 19.3 3,273 1

Hash Left Join (cost=145,706.02..154,068.38 rows=170 width=486) (actual time=279.942..545.074 rows=3,273 loops=1)

  • Hash Cond: (ent_entity_metro_areas.metro_id = metro_map.metro_code)
6. 6.114 541.558 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,699.86..154,059.88 rows=170 width=470) (actual time=279.798..541.558 rows=3,273 loops=1)

7. 4.753 509.260 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,699.44..152,764.66 rows=170 width=432) (actual time=279.778..509.260 rows=3,273 loops=1)

8. 10.876 471.777 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,699.01..151,330.88 rows=170 width=428) (actual time=279.756..471.777 rows=3,273 loops=1)

  • Join Filter: (region_map.region_code = country_map.region_code)
  • Rows Removed by Join Filter: 22934
9. 3.447 454.355 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,699.01..151,309.38 rows=170 width=362) (actual time=279.734..454.355 rows=3,273 loops=1)

10. 3.698 441.089 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,698.86..151,279.63 rows=170 width=353) (actual time=279.723..441.089 rows=3,273 loops=1)

11. 4.886 411.207 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,698.45..150,012.41 rows=170 width=349) (actual time=279.695..411.207 rows=3,273 loops=1)

12. 4.276 370.318 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,698.02..148,574.63 rows=170 width=321) (actual time=279.666..370.318 rows=3,273 loops=1)

13. 4.093 323.493 ↓ 19.3 3,273 1

Nested Loop Left Join (cost=145,697.59..147,136.43 rows=170 width=281) (actual time=279.641..323.493 rows=3,273 loops=1)

14. 8.712 289.943 ↓ 19.3 3,273 1

GroupAggregate (cost=145,697.16..145,705.38 rows=170 width=256) (actual time=279.619..289.943 rows=3,273 loops=1)

  • Group Key: positions.factset_entity_id, positions.fsym_id, positions.origin
15. 4.033 281.231 ↓ 18.8 3,274 1

Sort (cost=145,697.16..145,697.59 rows=174 width=256) (actual time=279.608..281.231 rows=3,274 loops=1)

  • Sort Key: positions.factset_entity_id, positions.fsym_id, positions.origin
  • Sort Method: quicksort Memory: 799kB
16. 1.544 277.198 ↓ 18.8 3,274 1

Subquery Scan on positions (cost=144,469.85..145,690.68 rows=174 width=256) (actual time=271.623..277.198 rows=3,274 loops=1)

  • Filter: (positions.priority = 1)
  • Rows Removed by Filter: 28
17. 3.233 275.654 ↑ 10.6 3,302 1

WindowAgg (cost=144,469.85..145,254.67 rows=34,881 width=296) (actual time=271.618..275.654 rows=3,302 loops=1)

18. 15.194 272.421 ↑ 10.6 3,302 1

Sort (cost=144,469.85..144,557.05 rows=34,881 width=288) (actual time=271.609..272.421 rows=3,302 loops=1)

  • Sort Key: (COALESCE(own_ent_13f_combined_inst.factset_rollup_entity_id, (COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id)))), current.report_date DESC NULLS LAST, ('4'::text) DESC
  • Sort Method: quicksort Memory: 823kB
19. 2.245 257.227 ↑ 10.6 3,302 1

Hash Left Join (cost=135,143.77..141,838.05 rows=34,881 width=288) (actual time=222.574..257.227 rows=3,302 loops=1)

  • Hash Cond: ((COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id)) = own_ent_13f_combined_inst.factset_filer_entity_id)
20. 2.205 251.004 ↑ 10.6 3,302 1

Hash Left Join (cost=134,895.16..140,906.33 rows=34,881 width=288) (actual time=218.561..251.004 rows=3,302 loops=1)

  • Hash Cond: (((COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id)) = prev_holdings.factset_entity_id) AND (own_inst_13f_detail.fsym_id = prev_holdings.fsym_id))
21.          

CTE prev_holdings

22. 1.915 28.228 ↓ 341.4 2,390 1

GroupAggregate (cost=8,096.60..8,096.77 rows=7 width=65) (actual time=25.695..28.228 rows=2,390 loops=1)

  • Group Key: filtered.factset_entity_id, filtered.fsym_id
23. 2.062 26.313 ↓ 351.9 2,463 1

Sort (cost=8,096.60..8,096.62 rows=7 width=65) (actual time=25.690..26.313 rows=2,463 loops=1)

  • Sort Key: filtered.factset_entity_id, filtered.fsym_id
  • Sort Method: quicksort Memory: 289kB
24. 1.118 24.251 ↓ 351.9 2,463 1

Subquery Scan on filtered (cost=8,045.40..8,096.50 rows=7 width=65) (actual time=20.350..24.251 rows=2,463 loops=1)

  • Filter: (filtered.pos = 1)
  • Rows Removed by Filter: 7
25. 2.198 23.133 ↓ 1.7 2,470 1

WindowAgg (cost=8,045.40..8,078.25 rows=1,460 width=105) (actual time=20.346..23.133 rows=2,470 loops=1)

26. 7.157 20.935 ↓ 1.7 2,470 1

Sort (cost=8,045.40..8,049.05 rows=1,460 width=97) (actual time=20.341..20.935 rows=2,470 loops=1)

  • Sort Key: (COALESCE(own_ent_13f_combined_inst_4.factset_rollup_entity_id, own_inst_13f_detail_3.factset_entity_id)), own_inst_13f_detail_3.report_date DESC, ('4'::text) DESC
  • Sort Method: quicksort Memory: 289kB
27. 1.005 13.778 ↓ 1.7 2,470 1

Result (cost=288.10..7,968.67 rows=1,460 width=97) (actual time=6.399..13.778 rows=2,470 loops=1)

28. 0.918 12.773 ↓ 1.7 2,470 1

Append (cost=288.10..7,954.07 rows=1,460 width=97) (actual time=6.399..12.773 rows=2,470 loops=1)

29. 1.760 10.320 ↓ 1.7 2,457 1

Hash Left Join (cost=288.10..5,887.17 rows=1,456 width=97) (actual time=6.397..10.320 rows=2,457 loops=1)

  • Hash Cond: (own_inst_13f_detail_3.factset_entity_id = own_ent_13f_combined_inst_4.factset_filer_entity_id)
30. 2.607 3.157 ↓ 1.7 2,457 1

Bitmap Heap Scan on own_inst_13f_detail own_inst_13f_detail_3 (cost=39.49..5,624.60 rows=1,456 width=46) (actual time=0.948..3.157 rows=2,457 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2018-12-31'::date))
  • Heap Blocks: exact=2465
31. 0.550 0.550 ↓ 1.7 2,469 1

Bitmap Index Scan on own_inst_13f_detail_fsym_id_report_date_idx (cost=0.00..39.12 rows=1,456 width=0) (actual time=0.550..0.550 rows=2,469 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2018-12-31'::date))
32. 2.397 5.403 ↑ 1.0 5,529 1

Hash (cost=179.49..179.49 rows=5,530 width=18) (actual time=5.403..5.403 rows=5,529 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
33. 3.006 3.006 ↑ 1.0 5,529 1

Seq Scan on own_ent_13f_combined_inst own_ent_13f_combined_inst_4 (cost=0.00..179.49 rows=5,530 width=18) (actual time=0.013..3.006 rows=5,529 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3950
34. 0.001 0.052 ↓ 0.0 0 1

Nested Loop Left Join (cost=710.65..721.55 rows=2 width=70) (actual time=0.051..0.052 rows=0 loops=1)

35. 0.001 0.051 ↓ 0.0 0 1

Subquery Scan on current_holdings_2 (cost=710.09..710.15 rows=1 width=30) (actual time=0.051..0.051 rows=0 loops=1)

  • Filter: (current_holdings_2.pos = 1)
36. 0.002 0.050 ↓ 0.0 0 1

WindowAgg (cost=710.09..710.13 rows=2 width=38) (actual time=0.050..0.050 rows=0 loops=1)

37. 0.008 0.048 ↓ 0.0 0 1

Sort (cost=710.09..710.09 rows=2 width=30) (actual time=0.048..0.048 rows=0 loops=1)

  • Sort Key: own_inst_stakes_detail.factset_entity_id, own_inst_stakes_detail.as_of_date DESC
  • Sort Method: quicksort Memory: 25kB
38. 0.013 0.040 ↓ 0.0 0 1

Bitmap Heap Scan on own_inst_stakes_detail (cost=6.71..710.08 rows=2 width=30) (actual time=0.040..0.040 rows=0 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (as_of_date >= '2017-06-30'::date) AND (as_of_date <= '2018-12-31'::date))
  • Filter: (current_flag = 1)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=3
39. 0.027 0.027 ↑ 45.5 4 1

Bitmap Index Scan on own_inst_stakes_detail_fsym_id_as_of_date_idx (cost=0.00..6.71 rows=182 width=0) (actual time=0.027..0.027 rows=4 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (as_of_date >= '2017-06-30'::date) AND (as_of_date <= '2018-12-31'::date))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using own_sec_prices_pkey on own_sec_prices prices_3 (cost=0.57..11.38 rows=2 width=21) (never executed)

  • Index Cond: ((fsym_id = current_holdings_2.fsym_id) AND (price_date = (date_trunc('month'::text, (current_holdings_2.report_date)::timestamp with time zone) - '1 day'::interval)))
41. 0.039 1.483 ↓ 6.5 13 1

Nested Loop Left Join (cost=1,319.82..1,330.75 rows=2 width=70) (actual time=1.360..1.483 rows=13 loops=1)

42. 0.011 1.314 ↓ 13.0 13 1

Subquery Scan on current_holdings_3 (cost=1,319.25..1,319.35 rows=1 width=30) (actual time=1.289..1.314 rows=13 loops=1)

  • Filter: (current_holdings_3.pos = 1)
43. 0.024 1.303 ↓ 4.3 13 1

WindowAgg (cost=1,319.25..1,319.31 rows=3 width=38) (actual time=1.284..1.303 rows=13 loops=1)

44. 0.027 1.279 ↓ 4.3 13 1

Sort (cost=1,319.25..1,319.26 rows=3 width=30) (actual time=1.275..1.279 rows=13 loops=1)

  • Sort Key: own_stakes_detail.factset_entity_id, own_stakes_detail.report_date DESC
  • Sort Method: quicksort Memory: 26kB
45. 1.065 1.252 ↓ 4.3 13 1

Bitmap Heap Scan on own_stakes_detail (cost=10.98..1,319.23 rows=3 width=30) (actual time=0.279..1.252 rows=13 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Filter: ((report_date >= '2017-06-30'::date) AND (report_date <= '2018-12-31'::date) AND (current_flag = 1))
  • Rows Removed by Filter: 1766
  • Heap Blocks: exact=333
46. 0.187 0.187 ↓ 5.7 1,917 1

Bitmap Index Scan on own_stakes_detail_fsym_id_idx (cost=0.00..10.97 rows=339 width=0) (actual time=0.187..0.187 rows=1,917 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
47. 0.130 0.130 ↑ 2.0 1 13

Index Scan using own_sec_prices_pkey on own_sec_prices prices_4 (cost=0.57..11.38 rows=2 width=21) (actual time=0.010..0.010 rows=1 loops=13)

  • Index Cond: ((fsym_id = current_holdings_3.fsym_id) AND (price_date = (date_trunc('month'::text, (current_holdings_3.report_date)::timestamp with time zone) - '1 day'::interval)))
48. 1.277 218.027 ↑ 10.6 3,302 1

Append (cost=126,798.14..132,198.84 rows=34,881 width=272) (actual time=187.781..218.027 rows=3,302 loops=1)

49. 4.200 198.687 ↑ 10.7 3,245 1

Merge Left Join (cost=126,798.14..127,282.20 rows=34,877 width=245) (actual time=187.780..198.687 rows=3,245 loops=1)

  • Merge Cond: ((own_inst_13f_detail.fsym_id = previous.fsym_id) AND ((COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id)) = previous.factset_entity_id))
50. 4.436 172.554 ↑ 10.7 3,245 1

Merge Left Join (cost=120,787.16..121,073.21 rows=34,877 width=225) (actual time=166.515..172.554 rows=3,245 loops=1)

  • Merge Cond: ((own_inst_13f_detail.fsym_id = current.fsym_id) AND ((COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id)) = current.factset_entity_id))
51. 11.145 142.146 ↑ 10.7 3,245 1

Sort (cost=114,567.38..114,654.57 rows=34,877 width=205) (actual time=141.206..142.146 rows=3,245 loops=1)

  • Sort Key: own_inst_13f_detail.fsym_id, (COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id))
  • Sort Method: quicksort Memory: 553kB
52. 24.935 131.001 ↑ 10.7 3,245 1

HashAggregate (cost=111,238.37..111,587.14 rows=34,877 width=205) (actual time=129.887..131.001 rows=3,245 loops=1)

  • Group Key: COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id), own_inst_13f_detail.fsym_id, '13F'::text, '2018-12-31'::text, '2019-03-31'::text, '4'::text, 'institutions'::text
53. 22.037 106.066 ↑ 1.0 34,825 1

Hash Join (cost=3,076.16..110,628.02 rows=34,877 width=205) (actual time=45.506..106.066 rows=34,825 loops=1)

  • Hash Cond: (COALESCE(own_ent_13f_combined_inst_1.factset_rollup_entity_id, own_inst_13f_detail.factset_entity_id) = own_ent_institutions_1.factset_entity_id)
54. 20.475 48.508 ↓ 1.0 35,515 1

Hash Left Join (cost=1,257.86..108,330.16 rows=34,877 width=27) (actual time=9.755..48.508 rows=35,515 loops=1)

  • Hash Cond: (own_inst_13f_detail.factset_entity_id = own_ent_13f_combined_inst_1.factset_filer_entity_id)
55. 18.863 23.886 ↓ 1.0 35,515 1

Bitmap Heap Scan on own_inst_13f_detail (cost=1,009.25..107,747.29 rows=34,877 width=18) (actual time=5.567..23.886 rows=35,515 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2010-05-23'::date) AND (report_date <= '2018-05-30'::date))
  • Heap Blocks: exact=4064
56. 5.023 5.023 ↓ 1.0 35,532 1

Bitmap Index Scan on own_inst_13f_detail_fsym_id_report_date_idx (cost=0.00..1,000.53 rows=34,877 width=0) (actual time=5.023..5.023 rows=35,532 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date >= '2010-05-23'::date) AND (report_date <= '2018-05-30'::date))
57. 1.882 4.147 ↑ 1.0 5,529 1

Hash (cost=179.49..179.49 rows=5,530 width=18) (actual time=4.147..4.147 rows=5,529 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
58. 2.265 2.265 ↑ 1.0 5,529 1

Seq Scan on own_ent_13f_combined_inst own_ent_13f_combined_inst_1 (cost=0.00..179.49 rows=5,530 width=18) (actual time=0.011..2.265 rows=5,529 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3950
59. 18.313 35.521 ↑ 1.0 57,997 1

Hash (cost=1,092.58..1,092.58 rows=58,058 width=9) (actual time=35.521..35.521 rows=57,997 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2835kB
60. 17.208 17.208 ↑ 1.0 57,997 1

Seq Scan on own_ent_institutions own_ent_institutions_1 (cost=0.00..1,092.58 rows=58,058 width=9) (actual time=0.006..17.208 rows=57,997 loops=1)

61. 9.286 25.972 ↓ 1.6 2,428 1

Sort (cost=6,219.78..6,223.55 rows=1,509 width=65) (actual time=25.304..25.972 rows=2,428 loops=1)

  • Sort Key: current.fsym_id, current.factset_entity_id
  • Sort Method: quicksort Memory: 286kB
62. 0.995 16.686 ↓ 1.6 2,428 1

Subquery Scan on current (cost=6,109.93..6,140.11 rows=1,509 width=65) (actual time=14.876..16.686 rows=2,428 loops=1)

63. 2.420 15.691 ↓ 1.6 2,428 1

HashAggregate (cost=6,109.93..6,125.02 rows=1,509 width=65) (actual time=14.875..15.691 rows=2,428 loops=1)

  • Group Key: COALESCE(own_ent_13f_combined_inst_2.factset_rollup_entity_id, own_inst_13f_detail_1.factset_entity_id), own_inst_13f_detail_1.fsym_id
64. 1.803 13.271 ↓ 1.7 2,504 1

Hash Left Join (cost=292.64..6,091.07 rows=1,509 width=73) (actual time=5.200..13.271 rows=2,504 loops=1)

  • Hash Cond: (own_inst_13f_detail_1.factset_entity_id = own_ent_13f_combined_inst_2.factset_filer_entity_id)
65. 6.763 7.438 ↓ 1.7 2,504 1

Bitmap Heap Scan on own_inst_13f_detail own_inst_13f_detail_1 (cost=44.03..5,828.00 rows=1,509 width=46) (actual time=1.129..7.438 rows=2,504 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2019-03-31'::date))
  • Heap Blocks: exact=3473
66. 0.675 0.675 ↓ 2.4 3,609 1

Bitmap Index Scan on own_inst_13f_detail_fsym_id_report_date_idx (cost=0.00..43.66 rows=1,509 width=0) (actual time=0.674..0.675 rows=3,609 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2019-03-31'::date))
67. 1.825 4.030 ↑ 1.0 5,529 1

Hash (cost=179.49..179.49 rows=5,530 width=18) (actual time=4.030..4.030 rows=5,529 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
68. 2.205 2.205 ↑ 1.0 5,529 1

Seq Scan on own_ent_13f_combined_inst own_ent_13f_combined_inst_2 (cost=0.00..179.49 rows=5,530 width=18) (actual time=0.009..2.205 rows=5,529 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3950
69. 9.308 21.933 ↓ 1.6 2,384 1

Sort (cost=6,010.98..6,014.62 rows=1,456 width=65) (actual time=21.261..21.933 rows=2,384 loops=1)

  • Sort Key: previous.fsym_id, previous.factset_entity_id
  • Sort Method: quicksort Memory: 283kB
70. 0.990 12.625 ↓ 1.6 2,384 1

Subquery Scan on previous (cost=5,905.37..5,934.49 rows=1,456 width=65) (actual time=10.839..12.625 rows=2,384 loops=1)

71. 2.360 11.635 ↓ 1.6 2,384 1

HashAggregate (cost=5,905.37..5,919.93 rows=1,456 width=65) (actual time=10.837..11.635 rows=2,384 loops=1)

  • Group Key: COALESCE(own_ent_13f_combined_inst_3.factset_rollup_entity_id, own_inst_13f_detail_2.factset_entity_id), own_inst_13f_detail_2.fsym_id
72. 1.775 9.275 ↓ 1.7 2,457 1

Hash Left Join (cost=288.10..5,887.17 rows=1,456 width=73) (actual time=4.812..9.275 rows=2,457 loops=1)

  • Hash Cond: (own_inst_13f_detail_2.factset_entity_id = own_ent_13f_combined_inst_3.factset_filer_entity_id)
73. 3.028 3.482 ↓ 1.7 2,457 1

Bitmap Heap Scan on own_inst_13f_detail own_inst_13f_detail_2 (cost=39.49..5,624.60 rows=1,456 width=46) (actual time=0.754..3.482 rows=2,457 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2018-12-31'::date))
  • Heap Blocks: exact=2465
74. 0.454 0.454 ↓ 1.7 2,469 1

Bitmap Index Scan on own_inst_13f_detail_fsym_id_report_date_idx (cost=0.00..39.12 rows=1,456 width=0) (actual time=0.453..0.454 rows=2,469 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (report_date = '2018-12-31'::date))
75. 1.877 4.018 ↑ 1.0 5,529 1

Hash (cost=179.49..179.49 rows=5,530 width=18) (actual time=4.018..4.018 rows=5,529 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
76. 2.141 2.141 ↑ 1.0 5,529 1

Seq Scan on own_ent_13f_combined_inst own_ent_13f_combined_inst_3 (cost=0.00..179.49 rows=5,530 width=18) (actual time=0.009..2.141 rows=5,529 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3950
77. 0.046 18.063 ↓ 14.2 57 1

Subquery Scan on *SELECT* 2 (cost=3,168.42..4,567.87 rows=4 width=272) (actual time=5.898..18.063 rows=57 loops=1)

78. 0.023 18.017 ↓ 14.2 57 1

Append (cost=3,168.42..4,567.82 rows=4 width=256) (actual time=5.893..18.017 rows=57 loops=1)

79.          

CTE holders

80. 0.000 0.036 ↓ 0.0 0 1

WindowAgg (cost=3,167.14..3,167.28 rows=7 width=54) (actual time=0.036..0.036 rows=0 loops=1)

81. 0.006 0.036 ↓ 0.0 0 1

Sort (cost=3,167.14..3,167.16 rows=7 width=46) (actual time=0.036..0.036 rows=0 loops=1)

  • Sort Key: own_inst_stakes_detail_1.factset_entity_id, own_inst_stakes_detail_1.as_of_date DESC
  • Sort Method: quicksort Memory: 25kB
82. 0.002 0.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=27.13..3,167.05 rows=7 width=46) (actual time=0.029..0.030 rows=0 loops=1)

  • Join Filter: (source_map.source_code = own_inst_stakes_detail_1.source_code)
83. 0.014 0.028 ↓ 0.0 0 1

Bitmap Heap Scan on own_inst_stakes_detail own_inst_stakes_detail_1 (cost=27.13..3,162.40 rows=7 width=32) (actual time=0.028..0.028 rows=0 loops=1)

  • Recheck Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (as_of_date >= '2010-05-23'::date) AND (as_of_date <= '2018-05-30'::date))
  • Filter: (current_flag = 1)
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=3
84. 0.014 0.014 ↑ 95.1 9 1

Bitmap Index Scan on own_inst_stakes_detail_fsym_id_as_of_date_idx (cost=0.00..27.13 rows=856 width=0) (actual time=0.014..0.014 rows=9 loops=1)

  • Index Cond: ((fsym_id = 'BHM9TN-S'::bpchar) AND (as_of_date >= '2010-05-23'::date) AND (as_of_date <= '2018-05-30'::date))
85. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.47 rows=31 width=24) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Seq Scan on source_map (cost=0.00..1.31 rows=31 width=24) (never executed)

87. 0.001 0.039 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.14..23.23 rows=3 width=256) (actual time=0.039..0.039 rows=0 loops=1)

  • Join Filter: ((current_holdings.fsym_id = previous_holdings.fsym_id) AND (current_holdings.factset_entity_id = previous_holdings.factset_entity_id))
88. 0.000 0.038 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..11.59 rows=2 width=108) (actual time=0.038..0.038 rows=0 loops=1)

89. 0.038 0.038 ↓ 0.0 0 1

CTE Scan on holders current_holdings (cost=0.00..0.19 rows=1 width=100) (actual time=0.038..0.038 rows=0 loops=1)

  • Filter: ((report_date >= '2010-05-23'::date) AND (report_date <= '2018-05-30'::date) AND (pos = 1))
90. 0.000 0.000 ↓ 0.0 0

Index Scan using own_sec_prices_pkey on own_sec_prices prices_1 (cost=0.57..11.38 rows=2 width=21) (never executed)

  • Index Cond: ((fsym_id = current_holdings.fsym_id) AND (price_date = (date_trunc('month'::text, (current_holdings.report_date)::timestamp with time zone) - '1 day'::interval)))
91. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.57..11.56 rows=2 width=92) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.57..11.55 rows=2 width=92) (never executed)

93. 0.000 0.000 ↓ 0.0 0

CTE Scan on holders previous_holdings (cost=0.00..0.16 rows=1 width=84) (never executed)

  • Filter: (pos = 2)
94. 0.000 0.000 ↓ 0.0 0

Index Scan using own_sec_prices_pkey on own_sec_prices previous_prices (cost=0.57..11.38 rows=2 width=21) (never executed)

  • Index Cond: ((fsym_id = previous_holdings.fsym_id) AND (price_date = (date_trunc('month'::text, (previous_holdings.report_date)::timestamp with time zone) - '1 day'::interval)))
95. 0.157 17.955 ↓ 57.0 57 1

Nested Loop Left Join (cost=1,341.79..1,377.26 rows=1 width=256) (actual time=5.852..17.955 rows=57 loops=1)

96.          

CTE holders

97. 1.341 4.996 ↓ 5.5 1,520 1

WindowAgg (cost=1,335.13..1,340.65 rows=276 width=54) (actual time=3.295..4.996 rows=1,520 loops=1)

98. 1.802 3.655 ↓ 5.5 1,520 1

Sort (cost=1,335.13..1,335.82 rows=276 width=46) (actual time=3.289..3.655 rows=1,520 loops=1)

  • Sort Key: own_stakes_detail_1.factset_entity_id, own_stakes_detail_1.report_date DESC
  • Sort Method: quicksort Memory: 167kB
99. 0.872 1.853 ↓ 5.5 1,520 1

Hash Left Join (cost=12.74..1,323.94 rows=276 width=46) (actual time=0.256..1.853 rows=1,520 loops=1)

  • Hash Cond: (own_stakes_detail_1.source_code = source_map_1.source_code)
100. 0.779 0.949 ↓ 5.5 1,520 1

Bitmap Heap Scan on own_stakes_detail own_stakes_detail_1 (cost=11.04..1,318.45 rows=276 width=32) (actual time=0.208..0.949 rows=1,520 loops=1)

  • Recheck Cond: (fsym_id = 'BHM9TN-S'::bpchar)
  • Filter: ((report_date >= '2010-05-23'::date) AND (report_date <= '2018-05-30'::date))
  • Rows Removed by Filter: 259
  • Heap Blocks: exact=333
101. 0.170 0.170 ↓ 5.7 1,917 1

Bitmap Index Scan on own_stakes_detail_fsym_id_idx (cost=0.00..10.97 rows=339 width=0) (actual time=0.170..0.170 rows=1,917 loops=1)

  • Index Cond: (fsym_id = 'BHM9TN-S'::bpchar)
102. 0.014 0.032 ↑ 1.0 31 1

Hash (cost=1.31..1.31 rows=31 width=24) (actual time=0.032..0.032 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
103. 0.018 0.018 ↑ 1.0 31 1

Seq Scan on source_map source_map_1 (cost=0.00..1.31 rows=31 width=24) (actual time=0.010..0.018 rows=31 loops=1)

104. 0.138 17.513 ↓ 57.0 57 1

Nested Loop Left Join (cost=0.57..25.21 rows=1 width=156) (actual time=5.843..17.513 rows=57 loops=1)

105. 0.918 16.976 ↓ 57.0 57 1

Nested Loop Left Join (cost=0.00..13.82 rows=1 width=148) (actual time=5.820..16.976 rows=57 loops=1)

  • Join Filter: ((current_holdings_1.fsym_id = previous_holdings_1.fsym_id) AND (current_holdings_1.factset_entity_id = previous_holdings_1.factset_entity_id))
  • Rows Removed by Join Filter: 2744
106. 3.575 3.575 ↓ 57.0 57 1

CTE Scan on holders current_holdings_1 (cost=0.00..7.59 rows=1 width=100) (actual time=3.301..3.575 rows=57 loops=1)

  • Filter: ((report_date >= '2010-05-23'::date) AND (report_date <= '2018-05-30'::date) AND (pos = 1))
  • Rows Removed by Filter: 1463
107. 12.483 12.483 ↓ 49.0 49 57

CTE Scan on holders previous_holdings_1 (cost=0.00..6.21 rows=1 width=84) (actual time=0.001..0.219 rows=49 loops=57)

  • Filter: (pos = 2)
  • Rows Removed by Filter: 1471
108. 0.399 0.399 ↑ 2.0 1 57

Index Scan using own_sec_prices_pkey on own_sec_prices prices_2 (cost=0.57..11.38 rows=2 width=21) (actual time=0.007..0.007 rows=1 loops=57)

  • Index Cond: ((fsym_id = current_holdings_1.fsym_id) AND (price_date = (date_trunc('month'::text, (current_holdings_1.report_date)::timestamp with time zone) - '1 day'::interval)))
109. 0.285 0.285 ↑ 2.0 1 57

Index Scan using own_sec_prices_pkey on own_sec_prices previous_prices_1 (cost=0.57..11.38 rows=2 width=21) (actual time=0.005..0.005 rows=1 loops=57)

  • Index Cond: ((fsym_id = previous_holdings_1.fsym_id) AND (price_date = (date_trunc('month'::text, (previous_holdings_1.report_date)::timestamp with time zone) - '1 day'::interval)))
110. 0.914 30.772 ↓ 341.4 2,390 1

Hash (cost=0.14..0.14 rows=7 width=88) (actual time=30.772..30.772 rows=2,390 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 187kB
111. 29.858 29.858 ↓ 341.4 2,390 1

CTE Scan on prev_holdings (cost=0.00..0.14 rows=7 width=88) (actual time=25.699..29.858 rows=2,390 loops=1)

112. 1.861 3.978 ↑ 1.0 5,529 1

Hash (cost=179.49..179.49 rows=5,530 width=18) (actual time=3.977..3.978 rows=5,529 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 334kB
113. 2.117 2.117 ↑ 1.0 5,529 1

Seq Scan on own_ent_13f_combined_inst (cost=0.00..179.49 rows=5,530 width=18) (actual time=0.008..2.117 rows=5,529 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3950
114. 29.457 29.457 ↓ 0.0 0 3,273

Index Scan using h_people_pkey on h_people (cost=0.43..8.40 rows=1 width=25) (actual time=0.009..0.009 rows=0 loops=3,273)

  • Index Cond: (factset_person_id = positions.factset_entity_id)
115. 42.549 42.549 ↑ 1.0 1 3,273

Index Scan using sym_entity_pkey on sym_entity inst_entity (cost=0.43..8.45 rows=1 width=40) (actual time=0.013..0.013 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
116. 36.003 36.003 ↑ 1.0 1 3,273

Index Scan using h_entity_pkey on h_entity (cost=0.43..8.45 rows=1 width=37) (actual time=0.011..0.011 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
117. 26.184 26.184 ↑ 1.0 1 3,273

Index Scan using q4_institution_values_pkey on q4_institution_values (cost=0.41..7.44 rows=1 width=14) (actual time=0.007..0.008 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
118. 9.819 9.819 ↑ 1.0 1 3,273

Index Scan using country_map_pkey on country_map (cost=0.15..0.17 rows=1 width=15) (actual time=0.002..0.003 rows=1 loops=3,273)

  • Index Cond: (iso_country = inst_entity.iso_country)
119. 6.539 6.546 ↑ 1.0 8 3,273

Materialize (cost=0.00..1.12 rows=8 width=76) (actual time=0.000..0.002 rows=8 loops=3,273)

120. 0.007 0.007 ↑ 1.0 8 1

Seq Scan on region_map (cost=0.00..1.08 rows=8 width=76) (actual time=0.006..0.007 rows=8 loops=1)

121. 32.730 32.730 ↑ 1.0 1 3,273

Index Scan using ent_entity_metro_areas_pkey on ent_entity_metro_areas (cost=0.43..8.42 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
122. 26.184 26.184 ↑ 1.0 1 3,273

Index Scan using cyclops_inst_overwrites_pkey on cyclops_inst_overwrites cyclops (cost=0.41..7.61 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
123. 0.062 0.119 ↑ 1.0 185 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
124. 0.057 0.057 ↑ 1.0 185 1

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

125. 22.911 22.911 ↑ 1.0 1 3,273

Index Scan using own_ent_institutions_pkey on own_ent_institutions (cost=0.41..0.43 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=3,273)

  • Index Cond: (factset_entity_id = inst_entity.factset_entity_id)
126. 0.011 0.026 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=90) (actual time=0.026..0.026 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
127. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on entity_sub_type_map (cost=0.00..1.28 rows=28 width=90) (actual time=0.006..0.015 rows=28 loops=1)

128. 22.911 22.911 ↑ 1.0 1 3,273

Index Scan using institution_sharkwatch_pkey on institution_sharkwatch (cost=0.41..0.43 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=3,273)

  • Index Cond: (entity_id = inst_entity.factset_entity_id)
129. 16.365 16.365 ↑ 2.0 1 3,273

Index Scan using own_sec_prices_pkey on own_sec_prices prices (cost=0.57..11.38 rows=2 width=45) (actual time=0.005..0.005 rows=1 loops=3,273)

  • Index Cond: ((fsym_id = positions.fsym_id) AND (price_date = (date_trunc('month'::text, ((max(positions.report_date)))::timestamp with time zone) - '1 day'::interval)))
130.          

SubPlan (forNested Loop Left Join)

131. 68.733 572.775 ↑ 1.0 1 3,273

Aggregate (cost=14.39..14.40 rows=1 width=32) (actual time=0.175..0.175 rows=1 loops=3,273)

132. 11.753 504.042 ↓ 2.0 2 3,273

Nested Loop Left Join (cost=8.59..14.39 rows=1 width=138) (actual time=0.124..0.154 rows=2 loops=3,273)

133. 230.762 481.131 ↓ 2.0 2 3,273

Hash Right Join (cost=8.46..14.18 rows=1 width=72) (actual time=0.120..0.147 rows=2 loops=3,273)

  • Hash Cond: (country_map_1.iso_country = ent_entity_address.iso_country)
134. 191.455 191.455 ↑ 1.0 270 3,245

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=3,245)

135. 9.819 58.914 ↓ 2.0 2 3,273

Hash (cost=8.45..8.45 rows=1 width=60) (actual time=0.018..0.018 rows=2 loops=3,273)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
136. 49.095 49.095 ↓ 2.0 2 3,273

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=3,273)

  • Index Cond: (factset_entity_id = positions.factset_entity_id)
137. 11.158 11.158 ↑ 1.0 1 5,579

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=5,579)

  • Index Cond: (region_code = country_map_1.region_code)
Planning time : 11.820 ms
Execution time : 1,217.496 ms