explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lyi5

Settings
# exclusive inclusive rows x rows loops node
1. 0.687 101,873.171 ↓ 12.8 2,553 1

Subquery Scan on instruments_valuations_json (cost=2,782,319.45..2,782,410.01 rows=200 width=96) (actual time=101,842.806..101,873.171 rows=2,553 loops=1)

  • Functions: 29
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 4.016 ms, Inlining 588.185 ms, Optimization 394.707 ms, Emission 362.131 ms, Total 1349.039 ms
2. 0.229 101,872.484 ↓ 12.8 2,553 1

Subquery Scan on aggregated_instrument_names (cost=2,782,319.45..2,782,407.01 rows=200 width=96) (actual time=101,842.803..101,872.484 rows=2,553 loops=1)

3. 29.300 101,872.255 ↓ 12.8 2,553 1

GroupAggregate (cost=2,782,319.45..2,782,405.01 rows=200 width=69) (actual time=101,842.802..101,872.255 rows=2,553 loops=1)

  • Group Key: collect_json_instruments.instrument_code
4. 1.251 101,842.955 ↑ 3.2 2,553 1

Sort (cost=2,782,319.45..2,782,340.09 rows=8,256 width=80) (actual time=101,842.768..101,842.955 rows=2,553 loops=1)

  • Sort Key: collect_json_instruments.instrument_code
  • Sort Method: quicksort Memory: 2809kB
5. 0.277 101,841.704 ↑ 3.2 2,553 1

Subquery Scan on collect_json_instruments (cost=2,779,305.68..2,781,782.34 rows=8,256 width=80) (actual time=101,786.428..101,841.704 rows=2,553 loops=1)

6. 54.685 101,841.427 ↑ 3.2 2,553 1

GroupAggregate (cost=2,779,305.68..2,781,699.78 rows=8,256 width=80) (actual time=101,786.427..101,841.427 rows=2,553 loops=1)

  • Group Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code
7. 12.255 101,786.742 ↑ 12.2 6,792 1

Sort (cost=2,779,305.68..2,779,512.07 rows=82,555 width=99) (actual time=101,786.363..101,786.742 rows=6,792 loops=1)

  • Sort Key: instruments_with_groups.source_name, instruments_with_groups.instrument_code, (min(instruments_with_groups.date))
  • Sort Method: quicksort Memory: 1276kB
8. 388.852 101,774.487 ↑ 12.2 6,792 1

GroupAggregate (cost=2,746,584.55..2,768,048.80 rows=82,555 width=99) (actual time=100,926.871..101,774.487 rows=6,792 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,724.227 101,385.635 ↓ 1.5 1,198,745 1

Sort (cost=2,746,584.55..2,748,648.42 rows=825,548 width=95) (actual time=100,926.847..101,385.635 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: 126496kB
10. 127.072 98,661.408 ↓ 1.5 1,198,745 1

Subquery Scan on instruments_with_groups (cost=2,551,908.67..2,580,802.85 rows=825,548 width=95) (actual time=97,433.849..98,661.408 rows=1,198,745 loops=1)

11. 572.462 98,534.336 ↓ 1.5 1,198,745 1

WindowAgg (cost=2,551,908.67..2,572,547.37 rows=825,548 width=111) (actual time=97,433.847..98,534.336 rows=1,198,745 loops=1)

12. 3,224.009 97,961.874 ↓ 1.5 1,198,745 1

Sort (cost=2,551,908.67..2,553,972.54 rows=825,548 width=95) (actual time=97,433.823..97,961.874 rows=1,198,745 loops=1)

  • Sort Key: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_code, from_latest_source_valuations.date
  • Sort Method: external merge Disk: 126592kB
13. 867.201 94,737.865 ↓ 1.5 1,198,745 1

WindowAgg (cost=2,353,105.05..2,386,126.97 rows=825,548 width=95) (actual time=93,264.650..94,737.865 rows=1,198,745 loops=1)

14. 7,521.412 93,870.664 ↓ 1.5 1,198,745 1

Sort (cost=2,353,105.05..2,355,168.92 rows=825,548 width=87) (actual time=93,264.618..93,870.664 rows=1,198,745 loops=1)

  • Sort Key: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_code, from_latest_source_valuations.instrument_name, from_latest_source_valuations.issuer, 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: 114792kB
15. 2,437.114 86,349.252 ↓ 1.5 1,198,745 1

Unique (cost=1,998,961.68..2,184,709.87 rows=825,548 width=87) (actual time=72,646.052..86,349.252 rows=1,198,745 loops=1)

16. 80,739.559 83,912.138 ↑ 1.0 8,255,120 1

Sort (cost=1,998,961.68..2,019,600.37 rows=8,255,475 width=87) (actual time=72,646.050..83,912.138 rows=8,255,120 loops=1)

  • Sort Key: from_latest_source_valuations.source_name, from_latest_source_valuations.instrument_name, from_latest_source_valuations.instrument_code, from_latest_source_valuations.issuer, 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: 817488kB
17. 3,172.579 3,172.579 ↑ 1.0 8,255,120 1

Seq Scan on from_latest_source_valuations (cost=0.00..260,458.75 rows=8,255,475 width=87) (actual time=1,345.594..3,172.579 rows=8,255,120 loops=1)

Planning time : 0.737 ms
Execution time : 102,953.559 ms