explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JQyQ

Settings
# exclusive inclusive rows x rows loops node
1. 28.918 218,887.133 ↓ 12.8 2,553 1

GroupAggregate (cost=14,054.58..14,059.58 rows=200 width=96) (actual time=218,858.080..218,887.133 rows=2,553 loops=1)

  • Group Key: collect_json_instruments.instrument_code
2. 1.131 218,858.215 ↓ 12.8 2,553 1

Sort (cost=14,054.58..14,055.08 rows=200 width=128) (actual time=218,858.061..218,858.215 rows=2,553 loops=1)

  • Sort Key: collect_json_instruments.instrument_code
  • Sort Method: quicksort Memory: 2811kB
3. 0.362 218,857.084 ↓ 12.8 2,553 1

Subquery Scan on collect_json_instruments (cost=14,036.44..14,046.94 rows=200 width=128) (actual time=218,800.272..218,857.084 rows=2,553 loops=1)

4. 56.148 218,856.722 ↓ 12.8 2,553 1

GroupAggregate (cost=14,036.44..14,044.94 rows=200 width=128) (actual time=218,800.270..218,856.722 rows=2,553 loops=1)

  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
5. 12.188 218,800.574 ↓ 34.0 6,808 1

Sort (cost=14,036.44..14,036.94 rows=200 width=240) (actual time=218,800.217..218,800.574 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
6. 512.322 218,788.386 ↓ 34.0 6,808 1

GroupAggregate (cost=14,021.80..14,028.80 rows=200 width=240) (actual time=217,806.662..218,788.386 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
7. 2,772.421 218,276.064 ↓ 5,993.7 1,198,745 1

Sort (cost=14,021.80..14,022.30 rows=200 width=236) (actual time=217,806.645..218,276.064 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
8. 208.415 215,503.643 ↓ 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=214,094.343..215,503.643 rows=1,198,745 loops=1)

9. 679.560 215,295.228 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=14,007.15..14,012.15 rows=200 width=252) (actual time=214,094.342..215,295.228 rows=1,198,745 loops=1)

10. 3,214.572 214,615.668 ↓ 5,993.7 1,198,745 1

Sort (cost=14,007.15..14,007.65 rows=200 width=236) (actual time=214,094.331..214,615.668 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
11. 941.924 211,401.096 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=13,991.51..13,999.51 rows=200 width=236) (actual time=209,864.588..211,401.096 rows=1,198,745 loops=1)

12. 7,732.334 210,459.172 ↓ 5,993.7 1,198,745 1

Sort (cost=13,991.51..13,992.01 rows=200 width=228) (actual time=209,864.576..210,459.172 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
13. 2,594.192 202,726.838 ↓ 5,993.7 1,198,745 1

Unique (cost=13,977.37..13,981.87 rows=200 width=228) (actual time=188,866.821..202,726.838 rows=1,198,745 loops=1)

14. 80,435.652 200,132.646 ↓ 41,275.6 8,255,120 1

Sort (cost=13,977.37..13,977.87 rows=200 width=228) (actual time=188,866.819..200,132.646 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
15. 2,851.801 119,696.994 ↓ 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=112,411.249..119,696.994 rows=8,255,120 loops=1)

16. 2,163.447 116,845.193 ↓ 41,275.6 8,255,120 1

Unique (cost=13,500.66..13,965.22 rows=200 width=328) (actual time=112,411.244..116,845.193 rows=8,255,120 loops=1)

17. 29,857.657 114,681.746 ↓ 177.7 8,257,151 1

Sort (cost=13,500.66..13,616.80 rows=46,456 width=328) (actual time=112,411.242..114,681.746 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
18. 13,764.153 84,824.089 ↓ 177.7 8,257,151 1

Merge Join (cost=746.21..2,909.99 rows=46,456 width=328) (actual time=65,836.264..84,824.089 rows=8,257,151 loops=1)

  • Merge Cond: (source_file.id = source_path.source_file_id)
19. 2,736.210 70,550.547 ↓ 2,021.8 8,257,151 1

Merge Join (cost=441.11..504.16 rows=4,084 width=216) (actual time=65,814.994..70,550.547 rows=8,257,151 loops=1)

  • Merge Cond: (model.source_file_id = source_file.id)
20. 8,745.245 67,320.025 ↓ 23,000.4 8,257,151 1

Sort (cost=136.01..136.90 rows=359 width=204) (actual time=65,795.220..67,320.025 rows=8,257,151 loops=1)

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

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

22. 476.548 494.312 ↓ 3,631.4 8,261,429 1

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

  • Sort Key: source_file.id
  • Sort Method: quicksort Memory: 853kB
23. 17.764 17.764 ↓ 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.759..17.764 rows=9,999 loops=1)

24. 490.083 509.389 ↓ 3,631.4 8,261,431 1

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

  • Sort Key: source_path.source_file_id
  • Sort Method: quicksort Memory: 853kB
25. 19.306 19.306 ↓ 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.358..19.306 rows=10,001 loops=1)

Planning time : 146.176 ms
Execution time : 219,340.239 ms