explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FbyV

Settings
# exclusive inclusive rows x rows loops node
1. 0.617 157,126.151 ↓ 9.9 1,979 1

Subquery Scan on instruments_positions_json (cost=13,406.94..13,416.94 rows=200 width=96) (actual time=157,102.816..157,126.151 rows=1,979 loops=1)

2. 0.243 157,125.534 ↓ 9.9 1,979 1

Subquery Scan on aggregated_instrument_names (cost=13,406.94..13,413.94 rows=200 width=96) (actual time=157,102.813..157,125.534 rows=1,979 loops=1)

3. 22.356 157,125.291 ↓ 9.9 1,979 1

GroupAggregate (cost=13,406.94..13,411.94 rows=200 width=96) (actual time=157,102.813..157,125.291 rows=1,979 loops=1)

  • Group Key: instruments_with_groups.instrument_code
4. 0.889 157,102.935 ↓ 9.9 1,979 1

Sort (cost=13,406.94..13,407.44 rows=200 width=128) (actual time=157,102.800..157,102.935 rows=1,979 loops=1)

  • Sort Key: instruments_with_groups.instrument_code
  • Sort Method: quicksort Memory: 2061kB
5. 41.281 157,102.046 ↓ 9.9 1,979 1

GroupAggregate (cost=13,388.80..13,397.30 rows=200 width=128) (actual time=157,060.534..157,102.046 rows=1,979 loops=1)

  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
6. 8.855 157,060.765 ↓ 25.5 5,106 1

Sort (cost=13,388.80..13,389.30 rows=200 width=240) (actual time=157,060.499..157,060.765 rows=5,106 loops=1)

  • Sort Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, (min(instruments_with_groups.date))
  • Sort Method: quicksort Memory: 965kB
7. 336.555 157,051.910 ↓ 25.5 5,106 1

GroupAggregate (cost=13,374.15..13,381.15 rows=200 width=240) (actual time=156,417.483..157,051.910 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
8. 1,716.478 156,715.355 ↓ 4,069.7 813,946 1

Sort (cost=13,374.15..13,374.65 rows=200 width=236) (actual time=156,417.329..156,715.355 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
9. 142.155 154,998.877 ↓ 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=154,066.220..154,998.877 rows=813,946 loops=1)

10. 454.996 154,856.722 ↓ 4,069.7 813,946 1

WindowAgg (cost=13,359.51..13,364.51 rows=200 width=252) (actual time=154,066.219..154,856.722 rows=813,946 loops=1)

11. 1,884.756 154,401.726 ↓ 4,069.7 813,946 1

Sort (cost=13,359.51..13,360.01 rows=200 width=236) (actual time=154,066.208..154,401.726 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
12. 644.499 152,516.970 ↓ 4,069.7 813,946 1

WindowAgg (cost=13,337.87..13,351.87 rows=200 width=236) (actual time=139,514.998..152,516.970 rows=813,946 loops=1)

13. 2,324.002 151,872.471 ↓ 4,069.7 813,946 1

Unique (cost=13,337.87..13,342.37 rows=200 width=228) (actual time=139,514.980..151,872.471 rows=813,946 loops=1)

14. 71,026.435 149,548.469 ↓ 35,969.8 7,193,968 1

Sort (cost=13,337.87..13,338.37 rows=200 width=228) (actual time=139,514.979..149,548.469 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
15. 1,387.074 78,522.034 ↓ 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=73,752.188..78,522.034 rows=7,193,968 loops=1)

16. 1,426.053 77,134.960 ↓ 35,969.8 7,193,968 1

Unique (cost=12,863.66..13,328.22 rows=200 width=296) (actual time=73,752.185..77,134.960 rows=7,193,968 loops=1)

17. 20,464.808 75,708.907 ↓ 159.1 7,391,691 1

Sort (cost=12,863.66..12,979.80 rows=46,456 width=296) (actual time=73,752.184..75,708.907 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
18. 14,501.225 55,244.099 ↓ 159.1 7,391,691 1

Merge Join (cost=746.21..2,909.99 rows=46,456 width=296) (actual time=35,972.767..55,244.099 rows=7,391,691 loops=1)

  • Merge Cond: (source_file.id = source_path.source_file_id)
19. 2,499.503 40,280.060 ↓ 1,808.5 7,385,885 1

Merge Join (cost=441.11..504.16 rows=4,084 width=216) (actual time=35,952.361..40,280.060 rows=7,385,885 loops=1)

  • Merge Cond: (model.source_file_id = source_file.id)
20. 7,606.723 37,337.268 ↓ 20,573.5 7,385,885 1

Sort (cost=136.01..136.90 rows=359 width=204) (actual time=35,933.829..37,337.268 rows=7,385,885 loops=1)

  • Sort Key: model.source_file_id
  • Sort Method: external merge Disk: 769416kB
21. 29,730.545 29,730.545 ↓ 20,573.5 7,385,885 1

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

22. 426.548 443.289 ↓ 3,249.7 7,393,138 1

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

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

24. 444.202 462.814 ↓ 3,252.3 7,398,944 1

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

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

Planning time : 1.319 ms
Execution time : 157,382.450 ms