explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3OwU : optimized

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 16.204 ↑ 7.4 28 1

Sort (cost=167,229.10..167,229.62 rows=206 width=128) (actual time=16.203..16.204 rows=28 loops=1)

  • Sort Key: (sum(tmp2.pctos)) DESC, (COALESCE(tmp2.investor, 'Other Investor Types'::character varying)), (sum((sum(((trunc(hh."position", 6) / 26685495540.000004) * '100'::numeric))))) DESC, (CASE hh.cusip WHEN '023135BM7'::bpchar THEN '2024'::text WHEN '023135BJ4'::bpchar THEN '2024'::text WHEN '023135BF2'::bpchar THEN '2024'::text WHEN '023135BC9'::bpchar THEN '2024'::text WHEN '023135AZ9'::bpchar THEN '2024'::text WHEN '023135AW6'::bpchar THEN '2023'::text WHEN '023135AT3'::bpchar THEN '2020'::text WHEN '023135BN5'::bpchar THEN '2024'::text WHEN 'U02320AK2'::bpchar THEN '2024'::text WHEN 'U02320AJ5'::bpchar THEN '2024'::text WHEN 'U02320AH9'::bpchar THEN '2024'::text WHEN 'U02320AG1'::bpchar THEN '2024'::text WHEN 'U02320AF3'::bpchar THEN '2024'::text WHEN 'U02320AE6'::bpchar THEN '2023'::text WHEN 'U02320AD8'::bpchar THEN '2020'::text WHEN '023135BL9'::bpchar THEN '2024'::text WHEN '023135BK1'::bpchar THEN '2024'::text WHEN '023135BH8'::bpchar THEN '2024'::text WHEN '023135BG0'::bpchar THEN '2024'::text WHEN '023135BE5'::bpchar THEN '2024'::text WHEN '023135BD7'::bpchar THEN '2024'::text WHEN '023135BB1'::bpchar THEN '2024'::text WHEN '023135BA3'::bpchar THEN '2024'::text WHEN '023135AY2'::bpchar THEN '2024'::text WHEN '023135AX4'::bpchar THEN '2024'::text WHEN '023135AV8'::bpchar THEN '2023'::text WHEN '023135AU0'::bpchar THEN '2023'::text WHEN '023135AS5'::bpchar THEN '2020'::text WHEN '023135AR7'::bpchar THEN '2020'::text WHEN '966837AE6'::bpchar THEN '2024'::text WHEN 'U96710AA3'::bpchar THEN '2024'::text WHEN '966837AD8'::bpchar THEN '2024'::text WHEN '023135AQ9'::bpchar THEN '2024'::text WHEN '023135AP1'::bpchar THEN '2024'::text WHEN '023135AN6'::bpchar THEN '2024'::text WHEN '023135AM8'::bpchar THEN '2021'::text WHEN '023135AL0'::bpchar THEN '2019'::text WHEN '023135AJ5'::bpchar THEN '2022'::text WHEN '023135AH9'::bpchar THEN '2017'::text WHEN '023135AG1'::bpchar THEN '2010'::text WHEN 'FDS0U6IA9'::bpchar THEN '2021'::text WHEN 'FDS0O2PP4'::bpchar THEN '2018'::text WHEN 'FDS0LXH52'::bpchar THEN '2019'::text WHEN 'FDS0F9JT8'::bpchar THEN '2020'::text WHEN 'FDS095W76'::bpchar THEN '2016'::text WHEN 'FDSD87D01'::bpchar THEN '2012'::text WHEN 'FDS8E8841'::bpchar THEN '2012'::text WHEN 'FDS8E84C7'::bpchar THEN '2009'::text WHEN 'FDS46A927'::bpchar THEN '2010'::text WHEN 'FDS0FUQ89'::bpchar THEN '2002'::text WHEN 'FDS0FUQ97'::bpchar THEN 'Unavailable'::text ELSE NULL::text END)
  • Sort Method: quicksort Memory: 28kB
2. 0.040 16.165 ↑ 7.4 28 1

GroupAggregate (cost=167,215.52..167,221.19 rows=206 width=128) (actual time=16.135..16.165 rows=28 loops=1)

  • Group Key: (COALESCE(tmp2.investor, 'Other Investor Types'::character varying)), (CASE hh.cusip WHEN '023135BM7'::bpchar THEN '2024'::text WHEN '023135BJ4'::bpchar THEN '2024'::text WHEN '023135BF2'::bpchar THEN '2024'::text WHEN '023135BC9'::bpchar THEN '2024'::text WHEN '023135AZ9'::bpchar THEN '2024'::text WHEN '023135AW6'::bpchar THEN '2023'::text WHEN '023135AT3'::bpchar THEN '2020'::text WHEN '023135BN5'::bpchar THEN '2024'::text WHEN 'U02320AK2'::bpchar THEN '2024'::text WHEN 'U02320AJ5'::bpchar THEN '2024'::text WHEN 'U02320AH9'::bpchar THEN '2024'::text WHEN 'U02320AG1'::bpchar THEN '2024'::text WHEN 'U02320AF3'::bpchar THEN '2024'::text WHEN 'U02320AE6'::bpchar THEN '2023'::text WHEN 'U02320AD8'::bpchar THEN '2020'::text WHEN '023135BL9'::bpchar THEN '2024'::text WHEN '023135BK1'::bpchar THEN '2024'::text WHEN '023135BH8'::bpchar THEN '2024'::text WHEN '023135BG0'::bpchar THEN '2024'::text WHEN '023135BE5'::bpchar THEN '2024'::text WHEN '023135BD7'::bpchar THEN '2024'::text WHEN '023135BB1'::bpchar THEN '2024'::text WHEN '023135BA3'::bpchar THEN '2024'::text WHEN '023135AY2'::bpchar THEN '2024'::text WHEN '023135AX4'::bpchar THEN '2024'::text WHEN '023135AV8'::bpchar THEN '2023'::text WHEN '023135AU0'::bpchar THEN '2023'::text WHEN '023135AS5'::bpchar THEN '2020'::text WHEN '023135AR7'::bpchar THEN '2020'::text WHEN '966837AE6'::bpchar THEN '2024'::text WHEN 'U96710AA3'::bpchar THEN '2024'::text WHEN '966837AD8'::bpchar THEN '2024'::text WHEN '023135AQ9'::bpchar THEN '2024'::text WHEN '023135AP1'::bpchar THEN '2024'::text WHEN '023135AN6'::bpchar THEN '2024'::text WHEN '023135AM8'::bpchar THEN '2021'::text WHEN '023135AL0'::bpchar THEN '2019'::text WHEN '023135AJ5'::bpchar THEN '2022'::text WHEN '023135AH9'::bpchar THEN '2017'::text WHEN '023135AG1'::bpchar THEN '2010'::text WHEN 'FDS0U6IA9'::bpchar THEN '2021'::text WHEN 'FDS0O2PP4'::bpchar THEN '2018'::text WHEN 'FDS0LXH52'::bpchar THEN '2019'::text WHEN 'FDS0F9JT8'::bpchar THEN '2020'::text WHEN 'FDS095W76'::bpchar THEN '2016'::text WHEN 'FDSD87D01'::bpchar THEN '2012'::text WHEN 'FDS8E8841'::bpchar THEN '2012'::text WHEN 'FDS8E84C7'::bpchar THEN '2009'::text WHEN 'FDS46A927'::bpchar THEN '2010'::text WHEN 'FDS0FUQ89'::bpchar THEN '2002'::text WHEN 'FDS0FUQ97'::bpchar THEN 'Unavailable'::text ELSE NULL::text END)
3. 0.045 16.125 ↑ 6.1 34 1

Sort (cost=167,215.52..167,216.04 rows=206 width=128) (actual time=16.123..16.125 rows=34 loops=1)

  • Sort Key: (COALESCE(tmp2.investor, 'Other Investor Types'::character varying)), (CASE hh.cusip WHEN '023135BM7'::bpchar THEN '2024'::text WHEN '023135BJ4'::bpchar THEN '2024'::text WHEN '023135BF2'::bpchar THEN '2024'::text WHEN '023135BC9'::bpchar THEN '2024'::text WHEN '023135AZ9'::bpchar THEN '2024'::text WHEN '023135AW6'::bpchar THEN '2023'::text WHEN '023135AT3'::bpchar THEN '2020'::text WHEN '023135BN5'::bpchar THEN '2024'::text WHEN 'U02320AK2'::bpchar THEN '2024'::text WHEN 'U02320AJ5'::bpchar THEN '2024'::text WHEN 'U02320AH9'::bpchar THEN '2024'::text WHEN 'U02320AG1'::bpchar THEN '2024'::text WHEN 'U02320AF3'::bpchar THEN '2024'::text WHEN 'U02320AE6'::bpchar THEN '2023'::text WHEN 'U02320AD8'::bpchar THEN '2020'::text WHEN '023135BL9'::bpchar THEN '2024'::text WHEN '023135BK1'::bpchar THEN '2024'::text WHEN '023135BH8'::bpchar THEN '2024'::text WHEN '023135BG0'::bpchar THEN '2024'::text WHEN '023135BE5'::bpchar THEN '2024'::text WHEN '023135BD7'::bpchar THEN '2024'::text WHEN '023135BB1'::bpchar THEN '2024'::text WHEN '023135BA3'::bpchar THEN '2024'::text WHEN '023135AY2'::bpchar THEN '2024'::text WHEN '023135AX4'::bpchar THEN '2024'::text WHEN '023135AV8'::bpchar THEN '2023'::text WHEN '023135AU0'::bpchar THEN '2023'::text WHEN '023135AS5'::bpchar THEN '2020'::text WHEN '023135AR7'::bpchar THEN '2020'::text WHEN '966837AE6'::bpchar THEN '2024'::text WHEN 'U96710AA3'::bpchar THEN '2024'::text WHEN '966837AD8'::bpchar THEN '2024'::text WHEN '023135AQ9'::bpchar THEN '2024'::text WHEN '023135AP1'::bpchar THEN '2024'::text WHEN '023135AN6'::bpchar THEN '2024'::text WHEN '023135AM8'::bpchar THEN '2021'::text WHEN '023135AL0'::bpchar THEN '2019'::text WHEN '023135AJ5'::bpchar THEN '2022'::text WHEN '023135AH9'::bpchar THEN '2017'::text WHEN '023135AG1'::bpchar THEN '2010'::text WHEN 'FDS0U6IA9'::bpchar THEN '2021'::text WHEN 'FDS0O2PP4'::bpchar THEN '2018'::text WHEN 'FDS0LXH52'::bpchar THEN '2019'::text WHEN 'FDS0F9JT8'::bpchar THEN '2020'::text WHEN 'FDS095W76'::bpchar THEN '2016'::text WHEN 'FDSD87D01'::bpchar THEN '2012'::text WHEN 'FDS8E8841'::bpchar THEN '2012'::text WHEN 'FDS8E84C7'::bpchar THEN '2009'::text WHEN 'FDS46A927'::bpchar THEN '2010'::text WHEN 'FDS0FUQ89'::bpchar THEN '2002'::text WHEN 'FDS0FUQ97'::bpchar THEN 'Unavailable'::text ELSE NULL::text END)
  • Sort Method: quicksort Memory: 29kB
4. 0.013 16.080 ↑ 6.1 34 1

Merge Left Join (cost=167,172.27..167,207.60 rows=206 width=128) (actual time=15.235..16.080 rows=34 loops=1)

  • Merge Cond: ((pu.investor)::text = (tmp2.investor)::text)
5. 0.807 8.958 ↑ 6.1 34 1

GroupAggregate (cost=83,595.35..83,627.79 rows=206 width=76) (actual time=8.123..8.958 rows=34 loops=1)

  • Group Key: pu.investor, (CASE hh.cusip WHEN '023135BM7'::bpchar THEN '2024'::text WHEN '023135BJ4'::bpchar THEN '2024'::text WHEN '023135BF2'::bpchar THEN '2024'::text WHEN '023135BC9'::bpchar THEN '2024'::text WHEN '023135AZ9'::bpchar THEN '2024'::text WHEN '023135AW6'::bpchar THEN '2023'::text WHEN '023135AT3'::bpchar THEN '2020'::text WHEN '023135BN5'::bpchar THEN '2024'::text WHEN 'U02320AK2'::bpchar THEN '2024'::text WHEN 'U02320AJ5'::bpchar THEN '2024'::text WHEN 'U02320AH9'::bpchar THEN '2024'::text WHEN 'U02320AG1'::bpchar THEN '2024'::text WHEN 'U02320AF3'::bpchar THEN '2024'::text WHEN 'U02320AE6'::bpchar THEN '2023'::text WHEN 'U02320AD8'::bpchar THEN '2020'::text WHEN '023135BL9'::bpchar THEN '2024'::text WHEN '023135BK1'::bpchar THEN '2024'::text WHEN '023135BH8'::bpchar THEN '2024'::text WHEN '023135BG0'::bpchar THEN '2024'::text WHEN '023135BE5'::bpchar THEN '2024'::text WHEN '023135BD7'::bpchar THEN '2024'::text WHEN '023135BB1'::bpchar THEN '2024'::text WHEN '023135BA3'::bpchar THEN '2024'::text WHEN '023135AY2'::bpchar THEN '2024'::text WHEN '023135AX4'::bpchar THEN '2024'::text WHEN '023135AV8'::bpchar THEN '2023'::text WHEN '023135AU0'::bpchar THEN '2023'::text WHEN '023135AS5'::bpchar THEN '2020'::text WHEN '023135AR7'::bpchar THEN '2020'::text WHEN '966837AE6'::bpchar THEN '2024'::text WHEN 'U96710AA3'::bpchar THEN '2024'::text WHEN '966837AD8'::bpchar THEN '2024'::text WHEN '023135AQ9'::bpchar THEN '2024'::text WHEN '023135AP1'::bpchar THEN '2024'::text WHEN '023135AN6'::bpchar THEN '2024'::text WHEN '023135AM8'::bpchar THEN '2021'::text WHEN '023135AL0'::bpchar THEN '2019'::text WHEN '023135AJ5'::bpchar THEN '2022'::text WHEN '023135AH9'::bpchar THEN '2017'::text WHEN '023135AG1'::bpchar THEN '2010'::text WHEN 'FDS0U6IA9'::bpchar THEN '2021'::text WHEN 'FDS0O2PP4'::bpchar THEN '2018'::text WHEN 'FDS0LXH52'::bpchar THEN '2019'::text WHEN 'FDS0F9JT8'::bpchar THEN '2020'::text WHEN 'FDS095W76'::bpchar THEN '2016'::text WHEN 'FDSD87D01'::bpchar THEN '2012'::text WHEN 'FDS8E8841'::bpchar THEN '2012'::text WHEN 'FDS8E84C7'::bpchar THEN '2009'::text WHEN 'FDS46A927'::bpchar THEN '2010'::text WHEN 'FDS0FUQ89'::bpchar THEN '2002'::text WHEN 'FDS0FUQ97'::bpchar THEN 'Unavailable'::text ELSE NULL::text END)
6. 0.520 8.151 ↓ 4.1 842 1

Sort (cost=83,595.35..83,595.86 rows=206 width=76) (actual time=8.106..8.151 rows=842 loops=1)

  • Sort Key: pu.investor, (CASE hh.cusip WHEN '023135BM7'::bpchar THEN '2024'::text WHEN '023135BJ4'::bpchar THEN '2024'::text WHEN '023135BF2'::bpchar THEN '2024'::text WHEN '023135BC9'::bpchar THEN '2024'::text WHEN '023135AZ9'::bpchar THEN '2024'::text WHEN '023135AW6'::bpchar THEN '2023'::text WHEN '023135AT3'::bpchar THEN '2020'::text WHEN '023135BN5'::bpchar THEN '2024'::text WHEN 'U02320AK2'::bpchar THEN '2024'::text WHEN 'U02320AJ5'::bpchar THEN '2024'::text WHEN 'U02320AH9'::bpchar THEN '2024'::text WHEN 'U02320AG1'::bpchar THEN '2024'::text WHEN 'U02320AF3'::bpchar THEN '2024'::text WHEN 'U02320AE6'::bpchar THEN '2023'::text WHEN 'U02320AD8'::bpchar THEN '2020'::text WHEN '023135BL9'::bpchar THEN '2024'::text WHEN '023135BK1'::bpchar THEN '2024'::text WHEN '023135BH8'::bpchar THEN '2024'::text WHEN '023135BG0'::bpchar THEN '2024'::text WHEN '023135BE5'::bpchar THEN '2024'::text WHEN '023135BD7'::bpchar THEN '2024'::text WHEN '023135BB1'::bpchar THEN '2024'::text WHEN '023135BA3'::bpchar THEN '2024'::text WHEN '023135AY2'::bpchar THEN '2024'::text WHEN '023135AX4'::bpchar THEN '2024'::text WHEN '023135AV8'::bpchar THEN '2023'::text WHEN '023135AU0'::bpchar THEN '2023'::text WHEN '023135AS5'::bpchar THEN '2020'::text WHEN '023135AR7'::bpchar THEN '2020'::text WHEN '966837AE6'::bpchar THEN '2024'::text WHEN 'U96710AA3'::bpchar THEN '2024'::text WHEN '966837AD8'::bpchar THEN '2024'::text WHEN '023135AQ9'::bpchar THEN '2024'::text WHEN '023135AP1'::bpchar THEN '2024'::text WHEN '023135AN6'::bpchar THEN '2024'::text WHEN '023135AM8'::bpchar THEN '2021'::text WHEN '023135AL0'::bpchar THEN '2019'::text WHEN '023135AJ5'::bpchar THEN '2022'::text WHEN '023135AH9'::bpchar THEN '2017'::text WHEN '023135AG1'::bpchar THEN '2010'::text WHEN 'FDS0U6IA9'::bpchar THEN '2021'::text WHEN 'FDS0O2PP4'::bpchar THEN '2018'::text WHEN 'FDS0LXH52'::bpchar THEN '2019'::text WHEN 'FDS0F9JT8'::bpchar THEN '2020'::text WHEN 'FDS095W76'::bpchar THEN '2016'::text WHEN 'FDSD87D01'::bpchar THEN '2012'::text WHEN 'FDS8E8841'::bpchar THEN '2012'::text WHEN 'FDS8E84C7'::bpchar THEN '2009'::text WHEN 'FDS46A927'::bpchar THEN '2010'::text WHEN 'FDS0FUQ89'::bpchar THEN '2002'::text WHEN 'FDS0FUQ97'::bpchar THEN 'Unavailable'::text ELSE NULL::text END)
  • Sort Method: quicksort Memory: 90kB
7. 0.581 7.631 ↓ 4.1 842 1

Nested Loop (cost=28,809.30..83,587.43 rows=206 width=76) (actual time=3.515..7.631 rows=842 loops=1)

8. 0.529 3.682 ↑ 8.3 842 1

HashAggregate (cost=28,808.87..28,878.78 rows=6,991 width=88) (actual time=3.499..3.682 rows=842 loops=1)

  • Group Key: hh.company_number, hh.cusip, hh."position
9.          

CTE secmas_cusips

10. 0.652 0.652 ↓ 43.0 43 1

Index Scan using pk_own_secmas_1442104178 on own_secmas (cost=0.56..407.74 rows=1 width=10) (actual time=0.163..0.652 rows=43 loops=1)

  • Index Cond: ((cusip)::text = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::text[]))
  • Filter: (lower((fi_flag)::text) = 'true'::text)
  • Rows Removed by Filter: 1
11. 0.067 3.153 ↑ 8.3 842 1

Append (cost=0.59..28,348.70 rows=6,991 width=88) (actual time=0.734..3.153 rows=842 loops=1)

12. 0.029 1.114 ↓ 4.5 9 1

Nested Loop (cost=0.59..12.66 rows=2 width=22) (actual time=0.734..1.114 rows=9 loops=1)

13. 0.029 0.698 ↓ 43.0 43 1

HashAggregate (cost=0.02..0.03 rows=1 width=36) (actual time=0.692..0.698 rows=43 loops=1)

  • Group Key: (secmas_cusips.cusip)::bpchar
14. 0.669 0.669 ↓ 43.0 43 1

CTE Scan on secmas_cusips (cost=0.00..0.02 rows=1 width=36) (actual time=0.165..0.669 rows=43 loops=1)

15. 0.387 0.387 ↓ 0.0 0 43

Index Scan using "IX_own_schedule_d_hist_snapshot_ix_own_schedule_d_hist_snapshot" on own_schedule_d_hist_snapshot hh (cost=0.56..12.60 rows=2 width=22) (actual time=0.009..0.009 rows=0 loops=43)

  • Index Cond: ((cusip = (secmas_cusips.cusip)::bpchar) AND (report_date = '2019-03-31'::date))
16. 1.972 1.972 ↑ 8.4 833 1

Index Scan using "IX_own_snapshot_ix_own_snapshot_cusip" on own_snapshot hh_1 (cost=0.57..28,266.13 rows=6,989 width=48) (actual time=0.159..1.972 rows=833 loops=1)

  • Index Cond: ((cusip = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::bpchar[])) AND (report_date = '2019-03-31'::date))
17. 3.368 3.368 ↑ 1.0 1 842

Index Scan using pk_own_portmas_ultimate_1648776981 on own_portmas_ultimate pu (cost=0.43..7.81 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=842)

  • Index Cond: ((company_number = hh.company_number) AND (portfolio_type = 'F'::bpchar))
18. 0.012 7.109 ↑ 5.0 4 1

Sort (cost=83,576.93..83,576.98 rows=20 width=44) (actual time=7.108..7.109 rows=4 loops=1)

  • Sort Key: tmp2.investor
  • Sort Method: quicksort Memory: 25kB
19. 0.002 7.097 ↑ 5.0 4 1

Subquery Scan on tmp2 (cost=83,574.69..83,576.49 rows=20 width=44) (actual time=7.091..7.097 rows=4 loops=1)

  • Filter: (tmp2.rnk <= 4)
  • Rows Removed by Filter: 3
20. 0.012 7.095 ↑ 8.6 7 1

WindowAgg (cost=83,574.69..83,575.74 rows=60 width=52) (actual time=7.089..7.095 rows=7 loops=1)

21. 0.011 7.083 ↑ 8.6 7 1

Sort (cost=83,574.69..83,574.84 rows=60 width=44) (actual time=7.083..7.083 rows=7 loops=1)

  • Sort Key: (sum(((trunc(hh_2."position", 6) / 26685495540.000004) * '100'::numeric))) DESC
  • Sort Method: quicksort Memory: 25kB
22. 0.758 7.072 ↑ 8.6 7 1

GroupAggregate (cost=83,569.08..83,572.92 rows=60 width=44) (actual time=6.290..7.072 rows=7 loops=1)

  • Group Key: pu_1.investor
23. 0.291 6.314 ↓ 4.1 842 1

Sort (cost=83,569.08..83,569.60 rows=206 width=44) (actual time=6.276..6.314 rows=842 loops=1)

  • Sort Key: pu_1.investor
  • Sort Method: quicksort Memory: 90kB
24. 0.471 6.023 ↓ 4.1 842 1

Nested Loop (cost=28,809.30..83,561.17 rows=206 width=44) (actual time=2.865..6.023 rows=842 loops=1)

25. 0.506 3.026 ↑ 8.3 842 1

HashAggregate (cost=28,808.87..28,878.78 rows=6,991 width=88) (actual time=2.854..3.026 rows=842 loops=1)

  • Group Key: hh_2.company_number, hh_2.cusip, hh_2."position
26.          

CTE secmas_cusips

27. 0.564 0.564 ↓ 43.0 43 1

Index Scan using pk_own_secmas_1442104178 on own_secmas own_secmas_1 (cost=0.56..407.74 rows=1 width=10) (actual time=0.134..0.564 rows=43 loops=1)

  • Index Cond: ((cusip)::text = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::text[]))
  • Filter: (lower((fi_flag)::text) = 'true'::text)
  • Rows Removed by Filter: 1
28. 0.065 2.520 ↑ 8.3 842 1

Append (cost=0.59..28,348.70 rows=6,991 width=88) (actual time=0.627..2.520 rows=842 loops=1)

29. 0.000 0.949 ↓ 4.5 9 1

Nested Loop (cost=0.59..12.66 rows=2 width=22) (actual time=0.627..0.949 rows=9 loops=1)

30. 0.026 0.605 ↓ 43.0 43 1

HashAggregate (cost=0.02..0.03 rows=1 width=36) (actual time=0.599..0.605 rows=43 loops=1)

  • Group Key: (secmas_cusips_1.cusip)::bpchar
31. 0.579 0.579 ↓ 43.0 43 1

CTE Scan on secmas_cusips secmas_cusips_1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.136..0.579 rows=43 loops=1)

32. 0.344 0.344 ↓ 0.0 0 43

Index Scan using "IX_own_schedule_d_hist_snapshot_ix_own_schedule_d_hist_snapshot" on own_schedule_d_hist_snapshot hh_2 (cost=0.56..12.60 rows=2 width=22) (actual time=0.008..0.008 rows=0 loops=43)

  • Index Cond: ((cusip = (secmas_cusips_1.cusip)::bpchar) AND (report_date = '2019-03-31'::date))
33. 1.506 1.506 ↑ 8.4 833 1

Index Scan using "IX_own_snapshot_ix_own_snapshot_cusip" on own_snapshot hh_3 (cost=0.57..28,266.13 rows=6,989 width=48) (actual time=0.144..1.506 rows=833 loops=1)

  • Index Cond: ((cusip = ANY ('{023135BM7,023135BJ4,023135BF2,023135BC9,023135AZ9,023135AW6,023135AT3,023135BN5,U02320AK2,U02320AJ5,U02320AH9,U02320AG1,U02320AF3,U02320AE6,U02320AD8,023135BL9,023135BK1,023135BH8,023135BG0,023135BE5,023135BD7,023135BB1,023135BA3,023135AY2,023135AX4,023135AV8,023135AU0,023135AS5,023135AR7,966837AE6,U96710AA3,966837AD8,023135AQ9,023135AP1,023135AN6,023135AM8,023135AL0,023135AJ5,023135AH9,023135AG1,FDS0U6IA9,FDS0O2PP4,FDS0LXH52,FDS0F9JT8,FDS095W76,FDSD87D01,FDS8E8841,FDS8E84C7,FDS46A927,FDS0FUQ89,FDS0FUQ97}'::bpchar[])) AND (report_date = '2019-03-31'::date))
34. 2.526 2.526 ↑ 1.0 1 842

Index Scan using pk_own_portmas_ultimate_1648776981 on own_portmas_ultimate pu_1 (cost=0.43..7.81 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=842)

  • Index Cond: ((company_number = hh_2.company_number) AND (portfolio_type = 'F'::bpchar))
Planning time : 3.499 ms
Execution time : 17.008 ms