explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SZ9z

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=28,508,656.69..29,547,304.94 rows=1,859 width=220) (actual rows= loops=)

  • Group Key: '2018'::text, 842, comtrade_data_annual_refined.partner_code, comtrade_data_annual_refined.commodity_code, comtrade_data_annual_refined.aggregate_level, (((sum(comtrade_data_annual_refined.refined_value) FILTER (WHERE (comtrade_data_annual_refined.trade_flow_code = 1))))::bigint), (((sum(comtrade_data_annual_refined.refined_value) FILTER (WHERE (comtrade_data_annual_refined.trade_flow_code = 2))))::bigint)
2. 0.000 0.000 ↓ 0.0

Sort (cost=28,508,656.69..28,595,208.00 rows=34,620,524 width=196) (actual rows= loops=)

  • Sort Key: comtrade_data_annual_refined.partner_code, comtrade_data_annual_refined.commodity_code, comtrade_data_annual_refined.aggregate_level, (((sum(comtrade_data_annual_refined.refined_value) FILTER (WHERE (comtrade_data_annual_refined.trade_flow_code = 1))))::bigint), (((sum(comtrade_data_annual_refined.refined_value) FILTER (WHERE (comtrade_data_annual_refined.trade_flow_code = 2))))::bigint)
3. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=19,473,720.88..20,291,994.57 rows=34,620,524 width=196) (actual rows= loops=)

  • Merge Cond: (com.commodity_code = comtrade_data_annual_refined.commodity_code)
4. 0.000 0.000 ↓ 0.0

Index Only Scan using commodities_uniq on commodities com (cost=0.41..1,586.62 rows=36,520 width=64) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Materialize (cost=19,473,720.46..19,598,379.10 rows=189,598 width=176) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,473,720.46..19,597,905.11 rows=189,598 width=176) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,473,720.19..19,587,616.54 rows=18,588 width=172) (actual rows= loops=)

  • Join Filter: (comtrade_data_annual_refined.partner_code = par.reporter_code)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,473,590.57..19,531,995.65 rows=18,588 width=140) (actual rows= loops=)

  • Join Filter: ((842) = rep.reporter_code)
9. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=19,473,460.96..19,476,188.88 rows=18,588 width=140) (actual rows= loops=)

  • Group Key: comtrade_data_annual_refined.commodity_code, ('2018'::text), (842), comtrade_data_annual_refined.partner_code, comtrade_data_annual_refined.aggregate_level
10. 0.000 0.000 ↓ 0.0

Gather Merge (cost=19,473,460.96..19,475,561.13 rows=15,508 width=140) (actual rows= loops=)

  • Workers Planned: 2
11. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=19,472,460.94..19,472,771.10 rows=7,754 width=140) (actual rows= loops=)

  • Group Key: comtrade_data_annual_refined.commodity_code, '2018'::text, 842, comtrade_data_annual_refined.partner_code, comtrade_data_annual_refined.aggregate_level
12. 0.000 0.000 ↓ 0.0

Sort (cost=19,472,460.94..19,472,480.32 rows=7,754 width=88) (actual rows= loops=)

  • Sort Key: comtrade_data_annual_refined.commodity_code, comtrade_data_annual_refined.partner_code, comtrade_data_annual_refined.aggregate_level
13. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on comtrade_data_annual_refined (cost=0.00..19,471,960.00 rows=7,754 width=88) (actual rows= loops=)

  • Filter: ((reporter_code <> partner_code) AND (partner_code <> 0) AND (year = '2018'::text) AND (reporter_code = 842))
14. 0.000 0.000 ↓ 0.0

Materialize (cost=129.61..133.61 rows=200 width=36) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Subquery Scan on rep (cost=129.61..132.61 rows=200 width=36) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Unique (cost=129.61..130.61 rows=200 width=68) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=129.61..130.11 rows=200 width=68) (actual rows= loops=)

  • Sort Key: (COALESCE(reporters.reporter_code, partners.partner_code))
18. 0.000 0.000 ↓ 0.0

HashAggregate (cost=119.97..121.97 rows=200 width=68) (actual rows= loops=)

  • Group Key: COALESCE(reporters.reporter_code, partners.partner_code)
19. 0.000 0.000 ↓ 0.0

WindowAgg (cost=87.94..106.24 rows=915 width=136) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=87.94..90.23 rows=915 width=123) (actual rows= loops=)

  • Sort Key: (COALESCE(reporters.reporter, partners.partner))
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9.97..42.94 rows=915 width=123) (actual rows= loops=)

  • Hash Cond: (reporters.reporter_code = partners.partner_code)
22. 0.000 0.000 ↓ 0.0

Seq Scan on reporters (cost=0.00..18.50 rows=850 width=68) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=6.10..6.10 rows=310 width=19) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on partners (cost=0.00..6.10 rows=310 width=19) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Materialize (cost=129.61..133.61 rows=200 width=36) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Subquery Scan on par (cost=129.61..132.61 rows=200 width=36) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Unique (cost=129.61..130.61 rows=200 width=68) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Sort (cost=129.61..130.11 rows=200 width=68) (actual rows= loops=)

  • Sort Key: (COALESCE(reporters_1.reporter_code, partners_1.partner_code))
29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=119.97..121.97 rows=200 width=68) (actual rows= loops=)

  • Group Key: COALESCE(reporters_1.reporter_code, partners_1.partner_code)
30. 0.000 0.000 ↓ 0.0

WindowAgg (cost=87.94..106.24 rows=915 width=136) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=87.94..90.23 rows=915 width=123) (actual rows= loops=)

  • Sort Key: (COALESCE(reporters_1.reporter, partners_1.partner))
32. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9.97..42.94 rows=915 width=123) (actual rows= loops=)

  • Hash Cond: (reporters_1.reporter_code = partners_1.partner_code)
33. 0.000 0.000 ↓ 0.0

Seq Scan on reporters reporters_1 (cost=0.00..18.50 rows=850 width=68) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=6.10..6.10 rows=310 width=19) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on partners partners_1 (cost=0.00..6.10 rows=310 width=19) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Only Scan using region_countries_country_code_region_id_key on region_countries reg (cost=0.28..0.45 rows=10 width=12) (actual rows= loops=)

  • Index Cond: (country_code = (842))