explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7BL

Settings
# exclusive inclusive rows x rows loops node
1. 0.757 248,532.445 ↓ 12.8 2,553 1

Subquery Scan on instruments_valuations_json (cost=14,054.58..14,064.58 rows=200 width=96) (actual time=248,501.648..248,532.445 rows=2,553 loops=1)

2. 0.332 248,531.688 ↓ 12.8 2,553 1

Subquery Scan on aggregated_instrument_names (cost=14,054.58..14,061.58 rows=200 width=96) (actual time=248,501.646..248,531.688 rows=2,553 loops=1)

3. 29.522 248,531.356 ↓ 12.8 2,553 1

GroupAggregate (cost=14,054.58..14,059.58 rows=200 width=96) (actual time=248,501.645..248,531.356 rows=2,553 loops=1)

  • Group Key: collect_json_instruments.instrument_code
4. 1.535 248,501.834 ↓ 12.8 2,553 1

Sort (cost=14,054.58..14,055.08 rows=200 width=128) (actual time=248,501.628..248,501.834 rows=2,553 loops=1)

  • Sort Key: collect_json_instruments.instrument_code
  • Sort Method: quicksort Memory: 2,811kB
5. 0.388 248,500.299 ↓ 12.8 2,553 1

Subquery Scan on collect_json_instruments (cost=14,036.44..14,046.94 rows=200 width=128) (actual time=248,442.376..248,500.299 rows=2,553 loops=1)

6. 57.068 248,499.911 ↓ 12.8 2,553 1

GroupAggregate (cost=14,036.44..14,044.94 rows=200 width=128) (actual time=248,442.375..248,499.911 rows=2,553 loops=1)

  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
7. 13.284 248,442.843 ↓ 34.0 6,808 1

Sort (cost=14,036.44..14,036.94 rows=200 width=240) (actual time=248,442.318..248,442.843 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: 1,275kB
8. 519.118 248,429.559 ↓ 34.0 6,808 1

GroupAggregate (cost=14,021.80..14,028.80 rows=200 width=240) (actual time=247,441.135..248,429.559 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,749.809 247,910.441 ↓ 5,993.7 1,198,745 1

Sort (cost=14,021.80..14,022.30 rows=200 width=236) (actual time=247,441.118..247,910.441 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: 126,432kB
10. 212.848 245,160.632 ↓ 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=243,719.628..245,160.632 rows=1,198,745 loops=1)

11. 695.329 244,947.784 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=14,007.15..14,012.15 rows=200 width=252) (actual time=243,719.627..244,947.784 rows=1,198,745 loops=1)

12. 3,271.336 244,252.455 ↓ 5,993.7 1,198,745 1

Sort (cost=14,007.15..14,007.65 rows=200 width=236) (actual time=243,719.616..244,252.455 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: 126,480kB
13. 982.079 240,981.119 ↓ 5,993.7 1,198,745 1

WindowAgg (cost=13,991.51..13,999.51 rows=200 width=236) (actual time=239,391.097..240,981.119 rows=1,198,745 loops=1)

14. 7,415.301 239,999.040 ↓ 5,993.7 1,198,745 1

Sort (cost=13,991.51..13,992.01 rows=200 width=228) (actual time=239,391.082..239,999.040 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: 114,752kB
15. 2,624.867 232,583.739 ↓ 5,993.7 1,198,745 1

Unique (cost=13,977.37..13,981.87 rows=200 width=228) (actual time=214,307.239..232,583.739 rows=1,198,745 loops=1)

16. 229,958.872 229,958.872 ↓ 41,275.6 8,255,120 1

Sort (cost=13,977.37..13,977.87 rows=200 width=228) (actual time=214,307.237..229,958.872 rows=8,255,120 loops=1)