explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wZ8y

Settings
# exclusive inclusive rows x rows loops node
1. 354.365 181,933.183 ↓ 25.5 5,106 1

GroupAggregate (cost=13,374.15..13,381.15 rows=200 width=240) (actual time=181,259.307..181,933.183 rows=5,106 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
2. 1,761.692 181,578.818 ↓ 4,069.7 813,946 1

Sort (cost=13,374.15..13,374.65 rows=200 width=236) (actual time=181,259.128..181,578.818 rows=813,946 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: 84432kB
3. 140.601 179,817.126 ↓ 4,069.7 813,946 1

Subquery Scan on instruments_with_groups (cost=13,359.51..13,366.51 rows=200 width=236) (actual time=178,887.986..179,817.126 rows=813,946 loops=1)

4. 458.396 179,676.525 ↓ 4,069.7 813,946 1

WindowAgg (cost=13,359.51..13,364.51 rows=200 width=252) (actual time=178,887.985..179,676.525 rows=813,946 loops=1)

5. 1,847.592 179,218.129 ↓ 4,069.7 813,946 1

Sort (cost=13,359.51..13,360.01 rows=200 width=236) (actual time=178,887.974..179,218.129 rows=813,946 loops=1)

  • Sort Key: from_latest_source_positions.source_name, from_latest_source_positions.instrument_code, from_latest_source_positions.date
  • Sort Method: external merge Disk: 84128kB
6. 638.045 177,370.537 ↓ 4,069.7 813,946 1

WindowAgg (cost=13,337.87..13,351.87 rows=200 width=236) (actual time=164,452.729..177,370.537 rows=813,946 loops=1)

7. 2,300.856 176,732.492 ↓ 4,069.7 813,946 1

Unique (cost=13,337.87..13,342.37 rows=200 width=228) (actual time=164,452.710..176,732.492 rows=813,946 loops=1)

8. 71,802.903 174,431.636 ↓ 35,969.8 7,193,968 1

Sort (cost=13,337.87..13,338.37 rows=200 width=228) (actual time=164,452.709..174,431.636 rows=7,193,968 loops=1)

  • Sort Key: from_latest_source_positions.source_name, from_latest_source_positions.instrument_code, from_latest_source_positions.instrument_name, from_latest_source_positions.issuer, from_latest_source_positions.sect1, from_latest_source_positions.sect2, from_latest_source_positions.sect3, from_latest_source_positions.date
  • Sort Method: external merge Disk: 664968kB
9. 1,400.418 102,628.733 ↓ 35,969.8 7,193,968 1

Subquery Scan on from_latest_source_positions (cost=12,863.66..13,330.22 rows=200 width=228) (actual time=97,882.887..102,628.733 rows=7,193,968 loops=1)

10. 1,417.034 101,228.315 ↓ 35,969.8 7,193,968 1

Unique (cost=12,863.66..13,328.22 rows=200 width=296) (actual time=97,882.884..101,228.315 rows=7,193,968 loops=1)

11. 20,425.183 99,811.281 ↓ 159.1 7,391,691 1

Sort (cost=12,863.66..12,979.80 rows=46,456 width=296) (actual time=97,882.883..99,811.281 rows=7,391,691 loops=1)

  • Sort Key: model.fund_code, model.date, (CASE WHEN ((btrim((model.instrument_code)::text) ~~ 'CASH'::text) OR (btrim((model.instrument_code)::text) ~~ 'CASHUNS'::text)) THEN concat(btrim((model.instrument_code)::text), '_', model.issuer, '_', model.fund_code) ELSE btrim((model.instrument_code)::text) END), source_file.file_date DESC, source_path.mtime DESC
  • Sort Method: external merge Disk: 898280kB
12. 14,527.829 79,386.098 ↓ 159.1 7,391,691 1

Merge Join (cost=746.21..2,909.99 rows=46,456 width=296) (actual time=60,091.081..79,386.098 rows=7,391,691 loops=1)

  • Merge Cond: (source_file.id = source_path.source_file_id)
13. 2,505.414 64,377.907 ↓ 1,808.5 7,385,885 1

Merge Join (cost=441.11..504.16 rows=4,084 width=216) (actual time=60,052.710..64,377.907 rows=7,385,885 loops=1)

  • Merge Cond: (model.source_file_id = source_file.id)
14. 7,741.566 61,401.180 ↓ 20,573.5 7,385,885 1

Sort (cost=136.01..136.90 rows=359 width=204) (actual time=60,015.245..61,401.180 rows=7,385,885 loops=1)

  • Sort Key: model.source_file_id
  • Sort Method: external merge Disk: 769416kB
15. 53,659.614 53,659.614 ↓ 20,573.5 7,385,885 1

Foreign Scan on positions model (cost=100.00..120.77 rows=359 width=204) (actual time=20.798..53,659.614 rows=7,385,885 loops=1)

16. 437.085 471.313 ↓ 3,249.7 7,393,138 1

Sort (cost=305.10..310.79 rows=2,275 width=12) (actual time=37.200..471.313 rows=7,393,138 loops=1)

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

18. 445.346 480.362 ↓ 3,252.3 7,398,944 1

Sort (cost=305.10..310.79 rows=2,275 width=12) (actual time=38.102..480.362 rows=7,398,944 loops=1)

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

Planning time : 0.718 ms
Execution time : 182,260.725 ms