explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Dde

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=14,054.58..14,059.58 rows=200 width=96) (actual rows= loops=)

  • Output: collect_json_instruments.instrument_code, jsonb_object_agg(collect_json_instruments.source_name, collect_json_instruments.identifiers_array), jsonb_object_agg(collect_json_instruments.source_name, collect_json_instruments.sectors_array)
  • Group Key: collect_json_instruments.instrument_code
2. 0.000 0.000 ↓ 0.0

Sort (cost=14,054.58..14,055.08 rows=200 width=128) (actual rows= loops=)

  • Output: collect_json_instruments.instrument_code, collect_json_instruments.source_name, collect_json_instruments.identifiers_array, collect_json_instruments.sectors_array
  • Sort Key: collect_json_instruments.instrument_code
3. 0.000 0.000 ↓ 0.0

Subquery Scan on collect_json_instruments (cost=14,036.44..14,046.94 rows=200 width=128) (actual rows= loops=)

  • Output: collect_json_instruments.instrument_code, collect_json_instruments.source_name, collect_json_instruments.identifiers_array, collect_json_instruments.sectors_array
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=14,036.44..14,044.94 rows=200 width=128) (actual rows= loops=)

  • Output: instruments_with_groups.source_name, instruments_with_groups.instrument_code, jsonb_agg(jsonb_build_object('start_date', (min(instruments_with_groups.date)), 'instrument_code', instruments_with_groups.instrument_code, 'instrument_name', instruments_with_groups.instrument_name, 'issuer', instruments_with_groups.issuer)), jsonb_agg(jsonb_build_object('start_date', (min(instruments_with_groups.date)), 'sect1', instruments_with_groups.sect1, 'sect2', instruments_with_groups.sect2, 'sect3', instruments_with_groups.sect3))
  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
5. 0.000 0.000 ↓ 0.0

Sort (cost=14,036.44..14,036.94 rows=200 width=240) (actual rows= loops=)

  • Output: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, (min(instruments_with_groups.date)), NULL::date, instruments_with_groups.code_grp
  • Sort Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, (min(instruments_with_groups.date))
6. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=14,021.80..14,028.80 rows=200 width=240) (actual rows= loops=)

  • Output: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, min(instruments_with_groups.date), NULL::date, instruments_with_groups.code_grp
  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, instruments_with_groups.code_grp
7. 0.000 0.000 ↓ 0.0

Sort (cost=14,021.80..14,022.30 rows=200 width=236) (actual rows= loops=)

  • Output: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, instruments_with_groups.code_grp, instruments_with_groups.date
  • Sort Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, instruments_with_groups.code_grp
8. 0.000 0.000 ↓ 0.0

Subquery Scan on instruments_with_groups (cost=14,007.15..14,014.15 rows=200 width=236) (actual rows= loops=)

  • Output: instruments_with_groups.source_name, instruments_with_groups.instrument_code, instruments_with_groups.instrument_name, instruments_with_groups.issuer, instruments_with_groups.sect1, instruments_with_groups.sect2, instruments_with_groups.sect3, instruments_with_groups.code_grp, instruments_with_groups.date
9. 0.000 0.000 ↓ 0.0

WindowAgg (cost=14,007.15..14,012.15 rows=200 width=252) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date, NULL::bigint, NULL::bigint, (row_number() OVER (?) - (row_number() OVER (?)))
10. 0.000 0.000 ↓ 0.0

Sort (cost=14,007.15..14,007.65 rows=200 width=236) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date, (row_number() OVER (?))
  • Sort Key: from_latest_source_valuations.source_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), from_latest_source_valuations.date
11. 0.000 0.000 ↓ 0.0

WindowAgg (cost=13,991.51..13,999.51 rows=200 width=236) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date, row_number() OVER (?)
12. 0.000 0.000 ↓ 0.0

Sort (cost=13,991.51..13,992.01 rows=200 width=228) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
  • Sort Key: from_latest_source_valuations.source_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), from_latest_source_valuations.instrument_name, (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
13. 0.000 0.000 ↓ 0.0

Unique (cost=13,977.37..13,981.87 rows=200 width=228) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
14. 0.000 0.000 ↓ 0.0

Sort (cost=13,977.37..13,977.87 rows=200 width=228) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
  • Sort Key: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, (CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END), (COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text)), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
15. 0.000 0.000 ↓ 0.0

Subquery Scan on from_latest_source_valuations (cost=13,500.66..13,969.72 rows=200 width=228) (actual rows= loops=)

  • Output: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, CASE WHEN ((from_latest_source_valuations.instrument_code = 'CASH'::text) OR (from_latest_source_valuations.instrument_code = 'CASHUNS'::text)) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.issuer, '_', from_latest_source_valuations.fund_code) WHEN (from_latest_source_valuations.instrument_code = 'EXPENSEUNS'::text) THEN concat(from_latest_source_valuations.instrument_code, '_', from_latest_source_valuations.fund_code) ELSE from_latest_source_valuations.instrument_code END, COALESCE(from_latest_source_valuations.issuer, 'no_issuer'::text), from_latest_source_valuations.sect1, from_latest_source_valuations.sect2, from_latest_source_valuations.sect3, from_latest_source_valuations.date
16. 0.000 0.000 ↓ 0.0

Unique (cost=13,500.66..13,965.22 rows=200 width=328) (actual rows= loops=)

  • Output: 'invest_one'::text, model.acc_num, model.rp_dat, (CASE WHEN ((btrim((model.inst_cod)::text) ~~ 'CASH'::text) OR (btrim((model.inst_cod)::text) ~~ 'CASHUNS'::text)) THEN concat(btrim((model.inst_cod)::text), '_', model.issuer, '_', model.acc_num) ELSE btrim((model.inst_cod)::text) END), (btrim((model.inst_desc)::text)), (btrim((model.sect1)::text)), (btrim((model.sect2)::text)), (btrim((model.sect3)::text)), (COALESCE(btrim((model.issuer)::text), 'no_issuer'::text)), NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, NULL::integer, NULL::integer, model.inst_cod, source_file.file_date, source_path.mtime
17. 0.000 0.000 ↓ 0.0

Sort (cost=13,500.66..13,616.80 rows=46,456 width=328) (actual rows= loops=)