explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 61HG : risk_monitor - ewritten with inline avg_array

Settings
# exclusive inclusive rows x rows loops node
1. 3,801.275 3,801.275 ↑ 15.2 3,581 1

CTE Scan on final (cost=98,879.75..100,930.60 rows=54,525 width=318) (actual time=631.999..3,801.275 rows=3,581 loops=1)

  • Filter: (mc_average_px <> 0::numeric)
  • Rows Removed by Filter: 293
2.          

CTE main

3. 9.680 702.911 ↑ 14.1 3,874 1

Hash Anti Join (cost=23,326.93..28,463.03 rows=54,799 width=63) (actual time=630.239..702.911 rows=3,874 loops=1)

  • Hash Cond: (m.id = strategy_defs.instr_id)
4. 29.774 693.034 ↑ 14.2 3,874 1

Merge Join (cost=23,256.50..27,426.44 rows=54,844 width=63) (actual time=630.013..693.034 rows=3,874 loops=1)

  • Merge Cond: (r.id = ((m.id)::numeric))
5. 8.625 8.625 ↓ 1.3 3,874 1

Index Scan using temp_index_rp2_rid_id on risk_scen_prices2 r (cost=0.43..2,392.74 rows=2,874 width=46) (actual time=0.018..8.625 rows=3,874 loops=1)

  • Index Cond: (risk_run_id = 2672)
  • Filter: (scen0 <> 0::numeric)
  • Rows Removed by Filter: 30
6. 585.659 654.635 ↑ 1.0 161,974 1

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

  • Sort Key: ((m.id)::numeric)
  • Sort Method: external sort Disk: 6992kB
7. 68.976 68.976 ↑ 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.006..68.976 rows=161,976 loops=1)

8. 0.059 0.197 ↓ 1.2 156 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
9. 0.138 0.138 ↓ 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.138 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))
10.          

CTE final

11. 710.341 3,790.171 ↑ 14.1 3,874 1

CTE Scan on main (cost=0.00..70,416.71 rows=54,799 width=318) (actual time=631.985..3,790.171 rows=3,874 loops=1)

12.          

SubPlan (for CTE Scan)

13. 2,223.676 3,079.830 ↑ 1.0 1 3,874

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

14. 856.154 856.154 ↓ 15.0 1,500 3,874

Function Scan on unnest s (cost=0.00..1.00 rows=100 width=32) (actual time=0.145..0.221 rows=1,500 loops=3,874)

Total runtime : 3,803.561 ms