explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Epv2

Settings
# exclusive inclusive rows x rows loops node
1. 0.772 212,393.103 ↓ 12.8 2,553 1

Subquery Scan on instruments_valuations_json (cost=14,054.58..14,064.58 rows=200 width=96) (actual time=212,362.592..212,393.103 rows=2,553 loops=1)

2. 0.323 212,392.331 ↓ 12.8 2,553 1

Subquery Scan on aggregated_instrument_names (cost=14,054.58..14,061.58 rows=200 width=96) (actual time=212,362.590..212,392.331 rows=2,553 loops=1)

3. 29.245 212,392.008 ↓ 12.8 2,553 1

GroupAggregate (cost=14,054.58..14,059.58 rows=200 width=96) (actual time=212,362.590..212,392.008 rows=2,553 loops=1)

  • Group Key: collect_json_instruments.instrument_code
4. 1.208 212,362.763 ↓ 12.8 2,553 1

Sort (cost=14,054.58..14,055.08 rows=200 width=128) (actual time=212,362.573..212,362.763 rows=2,553 loops=1)

  • Sort Key: collect_json_instruments.instrument_code
  • Sort Method: quicksort Memory: 2811kB
5. 0.387 212,361.555 ↓ 12.8 2,553 1

Subquery Scan on collect_json_instruments (cost=14,036.44..14,046.94 rows=200 width=128) (actual time=212,305.256..212,361.555 rows=2,553 loops=1)

6. 55.583 212,361.168 ↓ 12.8 2,553 1

GroupAggregate (cost=14,036.44..14,044.94 rows=200 width=128) (actual time=212,305.256..212,361.168 rows=2,553 loops=1)

  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
7. 12.307 212,305.585 ↓ 34.0 6,808 1

Sort (cost=14,036.44..14,036.94 rows=200 width=240) (actual time=212,305.220..212,305.585 rows=6,808 loops=1)

  • Sort Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, (min(instruments_with_groups.date))
  • Sort Method: quicksort Memory: 1275kB
8. 530.641 212,293.278 ↓ 34.0 6,808 1

GroupAggregate (cost=14,021.80..14,028.80 rows=200 width=240) (actual time=211,281.174..212,293.278 rows=6,808 loops=1)

  • 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
9. 2,729.143 211,762.637 ↓ 5,993.7 1,198,745 1

Sort (cost=14,021.80..14,022.30 rows=200 width=236) (actual time=211,281.158..211,762.637 rows=1,198,745 loops=1)

  • 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
  • Sort Method: external merge Disk: 126432kB
10. 208.445 209,033.494 ↓ 5,993.7 1,198,745 1

Subquery Scan on instruments_with_groups (cost=14,007.15..14,014.15 rows=200 width=236) (actual time=207,591.005..209,033.494 rows=1,198,745 loops=1)

11. 700.357 208,825.049 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=14,007.15..14,012.15 rows=200 width=252) (actual time=207,591.004..208,825.049 rows=1,198,745 loops=1)

12. 3,328.337 208,124.692 ↓ 5,993.7 1,198,745 1

Sort (cost=14,007.15..14,007.65 rows=200 width=236) (actual time=207,590.994..208,124.692 rows=1,198,745 loops=1)

  • 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
  • Sort Method: external merge Disk: 126480kB
13. 965.926 204,796.355 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=13,991.51..13,999.51 rows=200 width=236) (actual time=203,221.578..204,796.355 rows=1,198,745 loops=1)

14. 7,280.310 203,830.429 ↓ 5,993.7 1,198,745 1

Sort (cost=13,991.51..13,992.01 rows=200 width=228) (actual time=203,221.567..203,830.429 rows=1,198,745 loops=1)

  • 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
  • Sort Method: external merge Disk: 114752kB
15. 2,630.380 196,550.119 ↓ 5,993.7 1,198,745 1

Unique (cost=13,977.37..13,981.87 rows=200 width=228) (actual time=182,481.165..196,550.119 rows=1,198,745 loops=1)

16. 80,571.171 193,919.739 ↓ 41,275.6 8,255,120 1

Sort (cost=13,977.37..13,977.87 rows=200 width=228) (actual time=182,481.164..193,919.739 rows=8,255,120 loops=1)

  • 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
  • Sort Method: external merge Disk: 816744kB
17. 2,881.441 113,348.568 ↓ 41,275.6 8,255,120 1

Subquery Scan on from_latest_source_valuations (cost=13,500.66..13,969.72 rows=200 width=228) (actual time=106,051.907..113,348.568 rows=8,255,120 loops=1)

18. 2,155.721 110,467.127 ↓ 41,275.6 8,255,120 1

Unique (cost=13,500.66..13,965.22 rows=200 width=328) (actual time=106,051.899..110,467.127 rows=8,255,120 loops=1)

19. 27,154.978 108,311.406 ↓ 177.7 8,257,151 1

Sort (cost=13,500.66..13,616.80 rows=46,456 width=328) (actual time=106,051.897..108,311.406 rows=8,257,151 loops=1)

  • Sort Key: model.acc_num, model.rp_dat, model.inst_cod, source_file.file_date DESC, source_path.mtime DESC
  • Sort Method: external merge Disk: 1107840kB
20. 13,575.061 81,156.428 ↓ 177.7 8,257,151 1

Merge Join (cost=746.21..2,909.99 rows=46,456 width=328) (actual time=61,883.333..81,156.428 rows=8,257,151 loops=1)

  • Merge Cond: (source_file.id = source_path.source_file_id)
21. 2,747.888 67,072.772 ↓ 2,021.8 8,257,151 1

Merge Join (cost=441.11..504.16 rows=4,084 width=216) (actual time=61,864.158..67,072.772 rows=8,257,151 loops=1)

  • Merge Cond: (model.source_file_id = source_file.id)
22. 8,994.447 63,832.469 ↓ 23,000.4 8,257,151 1

Sort (cost=136.01..136.90 rows=359 width=204) (actual time=61,846.237..63,832.469 rows=8,257,151 loops=1)

  • Sort Key: model.source_file_id
  • Sort Method: external merge Disk: 788976kB
23. 54,838.022 54,838.022 ↓ 23,000.4 8,257,151 1

Foreign Scan on valuations model (cost=100.00..120.77 rows=359 width=204) (actual time=27.518..54,838.022 rows=8,257,151 loops=1)

24. 476.051 492.415 ↓ 3,631.4 8,261,429 1

Sort (cost=305.10..310.79 rows=2,275 width=12) (actual time=17.913..492.415 rows=8,261,429 loops=1)

  • Sort Key: source_file.id
  • Sort Method: quicksort Memory: 853kB
25. 16.364 16.364 ↓ 4.4 9,999 1

Foreign Scan on source_files source_file (cost=100.00..178.25 rows=2,275 width=12) (actual time=0.509..16.364 rows=9,999 loops=1)

26. 491.071 508.595 ↓ 3,631.4 8,261,431 1

Sort (cost=305.10..310.79 rows=2,275 width=12) (actual time=19.163..508.595 rows=8,261,431 loops=1)

  • Sort Key: source_path.source_file_id
  • Sort Method: quicksort Memory: 853kB
27. 17.524 17.524 ↓ 4.4 10,001 1

Foreign Scan on source_paths source_path (cost=100.00..178.25 rows=2,275 width=12) (actual time=0.289..17.524 rows=10,001 loops=1)

Planning time : 1.539 ms
Execution time : 212,726.824 ms