explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eee6

Settings
# exclusive inclusive rows x rows loops node
1. 73.546 139,657.443 ↓ 3.1 142,333 1

Subquery Scan on result (cost=13,808,569.04..13,819,079.07 rows=46,197 width=120) (actual time=139,522.793..139,657.443 rows=142,333 loops=1)

2. 0.000 139,583.897 ↓ 3.1 142,333 1

Gather Merge (cost=13,808,569.04..13,814,805.85 rows=46,197 width=130) (actual time=139,522.777..139,583.897 rows=142,333 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 49.887 418,588.677 ↓ 2.5 47,444 3

Merge Left Join (cost=13,807,569.02..13,808,473.55 rows=19,249 width=130) (actual time=139,508.946..139,529.559 rows=47,444 loops=3)

  • Merge Cond: ((refined_1.commodity_code = refined_temp_2.commodity_code) AND (refined_1.aggregate_level = refined_temp_2.aggregate_level) AND (refined_1.year = refined_temp_2.year) AND (refined_1.par
  • Join Filter: ((refined_1.period = refined_temp_2.period) AND (refined_1.reporter_code = refined_temp_2.reporter_code))
  • Filter: ((refined_1.partner_code <> COALESCE(refined_1.reporter_code, refined_temp_2.reporter_code)) AND (GREATEST((COALESCE(refined_1.refined_value, '0'::bigint))::double precision, (COALESCE(refin
4. 590.586 416,168.013 ↑ 1.2 47,444 3

Sort (cost=13,303,222.99..13,303,368.05 rows=58,022 width=38) (actual time=138,718.667..138,722.671 rows=47,444 loops=3)

  • Sort Key: refined_1.commodity_code, refined_1.aggregate_level, refined_1.year, refined_1.partner_code
  • Sort Method: quicksort Memory: 4894kB
  • Worker 0: Sort Method: quicksort Memory: 4684kB
  • Worker 1: Sort Method: quicksort Memory: 6151kB
5. 415,577.427 415,577.427 ↑ 1.2 47,444 3

Parallel Seq Scan on refined_temp_1 refined_1 (cost=0.00..13,298,632.20 rows=58,022 width=38) (actual time=85,525.550..138,525.809 rows=47,444 loops=3)

  • Filter: ((aggregate_level <> 0) AND (partner_code <> 0) AND (period = '201010'::text) AND (reporter_code = 842) AND (trade_flow_code = 1))
  • Rows Removed by Filter: 236741514
6. 0.042 2,370.777 ↓ 0.0 0 3

Sort (cost=504,346.03..504,362.29 rows=6,504 width=38) (actual time=790.259..790.259 rows=0 loops=3)

  • Sort Key: refined_temp_2.commodity_code, refined_temp_2.aggregate_level, refined_temp_2.year, refined_temp_2.partner_code
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
7. 2,319.204 2,370.735 ↓ 0.0 0 3

Bitmap Heap Scan on refined_temp_2 (cost=5,985.29..503,934.09 rows=6,504 width=38) (actual time=790.245..790.245 rows=0 loops=3)

  • Recheck Cond: ((period = '201010'::text) AND (partner_code = 842) AND (trade_flow_code = 2) AND (aggregate_level <> 0) AND (partner_code <> reporter_code))
  • Filter: (reporter_code = 842)
  • Rows Removed by Filter: 142010
  • Heap Blocks: exact=1515
8. 51.531 51.531 ↓ 1.0 142,010 3

Bitmap Index Scan on temp_index_4 (cost=0.00..5,983.66 rows=139,909 width=0) (actual time=17.177..17.177 rows=142,010 loops=3)

  • Index Cond: ((period = '201010'::text) AND (partner_code = 842))
Planning time : 13.178 ms
Execution time : 139,664.607 ms