explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 51dE : risk_monitor - rewritten with inline avg_array (left old code as is)

Settings
# exclusive inclusive rows x rows loops node
1. 6.591 14,003.326 ↑ 15.2 3,581 1

Sort (cost=244,165.70..244,302.03 rows=54,532 width=63) (actual time=14,003.128..14,003.326 rows=3,581 loops=1)

  • Sort Key: (abs(((((SubPlan 1) - r.scen0) * 100::numeric) / r.scen0)))
  • Sort Method: quicksort Memory: 689kB
2. 31.169 13,996.735 ↑ 15.2 3,581 1

Hash Anti Join (cost=23,326.93..239,875.44 rows=54,532 width=63) (actual time=501.968..13,996.735 rows=3,581 loops=1)

  • Hash Cond: (m.id = strategy_defs.instr_id)
3. 33.470 4,121.300 ↑ 15.2 3,581 1

Merge Join (cost=23,256.50..31,076.63 rows=54,577 width=63) (actual time=499.280..4,121.300 rows=3,581 loops=1)

  • Merge Cond: (r.id = ((m.id)::numeric))
4. 21.426 3,562.262 ↓ 1.3 3,581 1

Index Scan using temp_index_rp2_rid_id on risk_scen_prices2 r (cost=0.43..6,045.67 rows=2,860 width=46) (actual time=0.926..3,562.262 rows=3,581 loops=1)

  • Index Cond: (risk_run_id = 2672)
  • Filter: ((scen0 <> 0::numeric) AND ((SubPlan 4) <> 0::numeric))
  • Rows Removed by Filter: 323
5.          

SubPlan (for Index Scan)

6. 2,556.840 3,540.836 ↑ 1.0 1 3,874

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.914..0.914 rows=1 loops=3,874)

7. 983.996 983.996 ↓ 15.0 1,500 3,874

Function Scan on unnest s_3 (cost=0.00..1.00 rows=100 width=32) (actual time=0.168..0.254 rows=1,500 loops=3,874)

8. 474.900 525.568 ↑ 1.0 161,974 1

Sort (cost=23,256.07..23,661.01 rows=161,976 width=23) (actual time=498.346..525.568 rows=161,974 loops=1)

  • Sort Key: ((m.id)::numeric)
  • Sort Method: external sort Disk: 6992kB
9. 50.668 50.668 ↑ 1.0 161,976 1

Seq Scan on master_index m (cost=0.00..9,240.76 rows=161,976 width=23) (actual time=0.005..50.668 rows=161,976 loops=1)

10. 0.022 0.097 ↓ 1.2 156 1

Hash (cost=68.76..68.76 rows=133 width=4) (actual time=0.097..0.097 rows=156 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
11. 0.075 0.075 ↓ 1.2 156 1

Index Scan using strategy_defs_pkey on strategy_defs (cost=0.29..68.76 rows=133 width=4) (actual time=0.019..0.075 rows=156 loops=1)

  • Index Cond: ((strat_id = 2) AND (start_date <= '2020-05-20'::date))
  • Filter: ((end_date IS NULL) OR (end_date > '2020-05-20'::date))
12.          

SubPlan (for Hash Anti Join)

13. 2,410.013 3,305.263 ↑ 1.0 1 3,581

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.923..0.923 rows=1 loops=3,581)

14. 895.250 895.250 ↓ 15.0 1,500 3,581

Function Scan on unnest s (cost=0.00..1.00 rows=100 width=32) (actual time=0.164..0.250 rows=1,500 loops=3,581)

15. 2,363.460 3,255.129 ↑ 1.0 1 3,581

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.909..0.909 rows=1 loops=3,581)

16. 891.669 891.669 ↓ 15.0 1,500 3,581

Function Scan on unnest s_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.162..0.249 rows=1,500 loops=3,581)

17. 2,395.689 3,283.777 ↑ 1.0 1 3,581

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.917..0.917 rows=1 loops=3,581)

18. 888.088 888.088 ↓ 15.0 1,500 3,581

Function Scan on unnest s_2 (cost=0.00..1.00 rows=100 width=32) (actual time=0.161..0.248 rows=1,500 loops=3,581)

Total runtime : 14,005.217 ms