explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgnV

Settings
# exclusive inclusive rows x rows loops node
1. 2,240.672 2,314,229.897 ↑ 1.9 829,759 1

GroupAggregate (cost=66,435,209.69..69,853,866.92 rows=1,547,992 width=47) (actual time=2,300,669.307..2,314,229.897 rows=829,759 loops=1)

  • Group Key: comtrade_data_annual_refined.year, comtrade_data_annual_refined.commodity_code, comtrade_data_annual_refined.reporter_code
2.          

CTE reporter_commodity_years

3. 5,236.358 7,384.162 ↓ 1.2 1,653,399 1

HashSetOp Except (cost=1,000.00..789,759.64 rows=1,392,441 width=72) (actual time=7,072.922..7,384.162 rows=1,653,399 loops=1)

4. 146.759 2,147.804 ↓ 1.0 1,653,399 1

Append (cost=1,000.00..777,765.02 rows=1,599,282 width=72) (actual time=8.957..2,147.804 rows=1,653,399 loops=1)

5. 239.846 1,999.907 ↓ 1.0 1,653,399 1

Subquery Scan on *SELECT* 1 (cost=1,000.00..769,750.95 rows=1,599,281 width=19) (actual time=8.545..1,999.907 rows=1,653,399 loops=1)

6. 0.000 1,760.061 ↓ 1.0 1,653,399 1

Gather (cost=1,000.00..753,758.14 rows=1,599,281 width=15) (actual time=8.544..1,760.061 rows=1,653,399 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 2,343.949 2,343.949 ↑ 1.2 551,133 3

Parallel Seq Scan on mv_country_commodity_year (cost=0.00..592,830.04 rows=666,367 width=15) (actual time=0.247..2,343.949 rows=551,133 loops=3)

  • Filter: (year = '2016'::text)
  • Rows Removed by Filter: 16533990
8. 0.724 1.138 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..17.66 rows=1 width=72) (actual time=1.138..1.138 rows=0 loops=1)

9. 0.414 0.414 ↓ 0.0 0 1

Seq Scan on comtrade_trade_indicators_per_country_commodity (cost=0.00..17.65 rows=1 width=68) (actual time=0.414..0.414 rows=0 loops=1)

  • Filter: ((ti_name = 'RCA'::text) AND (year = '2016'::text))
10. 40,709.662 2,311,989.225 ↓ 11.0 17,009,006 1

Merge Join (cost=65,645,450.05..69,029,277.46 rows=1,547,992 width=23) (actual time=2,300,666.302..2,311,989.225 rows=17,009,006 loops=1)

  • Merge Cond: ((comtrade_data_annual_refined.year = reporter_commodity_years.year) AND (comtrade_data_annual_refined.commodity_code = reporter_commodity_years.commodity_code) AND (comtrade_data_annual_refined.reporter_code = reporter_commodity_years.reporter_code))
11. 1,316,845.294 2,256,461.645 ↑ 1.1 318,887,432 1

Sort (cost=65,475,508.31..66,313,725.20 rows=335,286,757 width=23) (actual time=2,148,362.039..2,256,461.645 rows=318,887,432 loops=1)

  • Sort Key: comtrade_data_annual_refined.year, comtrade_data_annual_refined.commodity_code, comtrade_data_annual_refined.reporter_code
  • Sort Method: external merge Disk: 11113624kB
12. 862,853.545 939,616.351 ↓ 1.0 336,229,571 1

Bitmap Heap Scan on comtrade_data_annual_refined (cost=3,420,096.05..14,068,386.40 rows=335,286,757 width=23) (actual time=77,588.230..939,616.351 rows=336,229,571 loops=1)

  • Recheck Cond: ((trade_flow_code = 2) AND (partner_code <> 0))
  • Heap Blocks: exact=2791372
13. 76,762.806 76,762.806 ↓ 1.0 336,229,571 1

Bitmap Index Scan on cda_year_commodity (cost=0.00..3,336,274.36 rows=335,286,757 width=0) (actual time=76,762.806..76,762.806 rows=336,229,571 loops=1)

14. 7,071.127 14,817.918 ↓ 12.8 17,832,646 1

Sort (cost=169,941.75..173,422.85 rows=1,392,441 width=68) (actual time=13,823.908..14,817.918 rows=17,832,646 loops=1)

  • Sort Key: reporter_commodity_years.year, reporter_commodity_years.commodity_code, reporter_commodity_years.reporter_code
  • Sort Method: quicksort Memory: 126656kB
15. 7,746.791 7,746.791 ↓ 1.2 1,653,399 1

CTE Scan on reporter_commodity_years (cost=0.00..27,848.82 rows=1,392,441 width=68) (actual time=7,072.927..7,746.791 rows=1,653,399 loops=1)

Planning time : 23.127 ms
Execution time : 2,314,885.882 ms