explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x988

Settings
# exclusive inclusive rows x rows loops node
1. 293.949 1,591.270 ↓ 3.1 142,333 1

Merge Full Join (cost=1,025,613.30..1,037,482.33 rows=46,636 width=120) (actual time=1,272.542..1,591.270 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. 168.891 225.093 ↓ 1.0 142,333 1

Sort (cost=510,387.29..510,733.75 rows=138,583 width=38) (actual time=213.306..225.093 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. 40.038 56.202 ↓ 1.0 142,333 1

Bitmap Heap Scan on refined_temp_1 refined_monthly_imports (cost=5,965.05..498,552.03 rows=138,583 width=38) (actual time=16.352..56.202 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))
  • Heap Blocks: exact=1331
4. 16.164 16.164 ↓ 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.164..16.164 rows=142,333 loops=1)

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

Sort (cost=515,226.01..515,575.79 rows=139,909 width=38) (actual time=1,059.203..1,072.228 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
6. 46.118 76.765 ↓ 1.0 142,010 1

Bitmap Heap Scan on refined_temp_2 refined_monthly_exports (cost=6,018.64..503,267.90 rows=139,909 width=38) (actual time=30.846..76.765 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. 30.647 30.647 ↓ 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=30.647..30.647 rows=142,010 loops=1)

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