explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DVik

Settings
# exclusive inclusive rows x rows loops node
1. 291.780 1,458.927 ↓ 3.1 142,333 1

Merge Full Join (cost=1,026,294.85..1,038,154.51 rows=46,592 width=120) (actual time=1,139.823..1,458.927 rows=142,333 loops=1)

  • Merge Cond: ((refined_monthly_imports.reporter_code = refined_monthly_exports.partner_code) AND (refined_monthly_imports.partner_code = refined_monthly_exports.reporter_code) AND (refined_monthly_imports.commod
  • Filter: (GREATEST((COALESCE(refined_monthly_imports.refined_value, '0'::bigint))::double precision, (COALESCE(refined_monthly_exports.refined_value, '0'::bigint))::double precision) > '0'::double precision)
2. 143.703 205.195 ↓ 1.0 142,333 1

Sort (cost=510,731.33..511,077.72 rows=138,557 width=38) (actual time=190.369..205.195 rows=142,333 loops=1)

  • Sort Key: refined_monthly_imports.reporter_code, refined_monthly_imports.partner_code, refined_monthly_imports.commodity_code, refined_monthly_imports.aggregate_level, refined_monthly_imports.year, refine
  • Sort Method: quicksort Memory: 17264kB
3. 44.935 61.492 ↓ 1.0 142,333 1

Bitmap Heap Scan on refined_temp_1 refined_monthly_imports (cost=5,965.04..498,898.48 rows=138,557 width=38) (actual time=16.729..61.492 rows=142,333 loops=1)

  • Recheck Cond: ((period = '201010'::text) AND (reporter_code = 842) AND (trade_flow_code = 1) AND (aggregate_level <> 0) AND (partner_code <> reporter_code))
  • Filter: (partner_code <> 0)
  • Heap Blocks: exact=1331 exports.year) AS year,
4. 16.557 16.557 ↓ 1.0 142,333 1

Bitmap Index Scan on temp_index_1 (cost=0.00..5,930.40 rows=138,583 width=0) (actual time=16.557..16.557 rows=142,333 loops=1)

  • Index Cond: ((period = '201010'::text) AND (reporter_code = 842)),
5. 904.742 961.952 ↓ 1.0 142,010 1

Sort (cost=515,563.52..515,912.96 rows=139,777 width=38) (actual time=949.425..961.952 rows=142,010 loops=1)

  • Sort Key: refined_monthly_exports.partner_code, refined_monthly_exports.reporter_code, refined_monthly_exports.commodity_code, refined_monthly_exports.aggregate_level, refined_monthly_exports.year, refine
  • Sort Method: quicksort Memory: 17239kB'0')::DOUBLE PRECISION AS x1,
6. 40.518 57.210 ↓ 1.0 142,010 1

Bitmap Heap Scan on refined_temp_2 refined_monthly_exports (cost=6,018.61..503,617.64 rows=139,777 width=38) (actual time=16.884..57.210 rows=142,010 loops=1)

  • Recheck Cond: ((period = '201010'::text) AND (partner_code = 842) AND (trade_flow_code = 2) AND (aggregate_level <> 0) AND (partner_code <> reporter_code))
  • Heap Blocks: exact=1515
7. 16.692 16.692 ↓ 1.0 142,010 1

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

  • Index Cond: ((period = '201010'::text) AND (partner_code = 842))