explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hpat

Settings
# exclusive inclusive rows x rows loops node
1. 0.351 135.655 ↓ 2.2 2,217 1

Merge Full Join (cost=168.52..203.53 rows=1,000 width=60) (actual time=135.156..135.655 rows=2,217 loops=1)

  • Merge Cond: ((goods_data.commodity_code = services_data.commodity_code) AND (goods_data.year = services_data.year) AND (goods_data.reporter_code = services_data.reporter_code) AND (goods_data.partner_code = services_data.partner_code) AND (goods_data.refined_imports = services_data.refined_imports) AND (goods_data.refined_exports = services_data.refined_exports))
2.          

CTE commodity_codes

3. 0.003 0.011 ↑ 20.0 5 1

ProjectSet (cost=0.28..1.80 rows=100 width=32) (actual time=0.009..0.011 rows=5 loops=1)

4. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using sic_hs_mapping_uniq on sic_hs_mapping (cost=0.28..1.29 rows=1 width=85) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (sic_code = 3,841)
  • Heap Fetches: 0
5.          

CTE goods_commodity_codes

6. 0.019 0.019 ↑ 1.2 5 1

CTE Scan on commodity_codes (cost=0.00..2.25 rows=6 width=32) (actual time=0.014..0.019 rows=5 loops=1)

  • Filter: (commodity_code ~ '^\d+(.\d+)?$'::text)
7.          

CTE services_commodity_codes

8. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on commodity_codes commodity_codes_1 (cost=0.00..2.25 rows=94 width=32) (actual time=0.009..0.009 rows=0 loops=1)

  • Filter: (commodity_code !~ '^\d+(.\d+)?$'::text)
  • Rows Removed by Filter: 5
9.          

CTE goods_data

10. 131.328 131.352 ↓ 2.2 2,217 1

Function Scan on get_refined_raw_view_sector (cost=0.40..10.40 rows=1,000 width=60) (actual time=131.126..131.352 rows=2,217 loops=1)

11.          

Initplan (for Function Scan)

12. 0.021 0.021 ↑ 1.2 5 1

CTE Scan on goods_commodity_codes (cost=0.00..0.12 rows=6 width=32) (actual time=0.015..0.021 rows=5 loops=1)

13. 0.001 0.003 ↑ 1.0 1 1

Limit (cost=0.00..0.03 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

14. 0.002 0.002 ↑ 6.0 1 1

CTE Scan on goods_commodity_codes goods_commodity_codes_1 (cost=0.00..0.20 rows=6 width=4) (actual time=0.002..0.002 rows=1 loops=1)

15.          

CTE services_data

16. 0.720 0.730 ↓ 0.0 0 1

Function Scan on get_refined_raw_service_view_sector (cost=2.16..12.15 rows=1,000 width=60) (actual time=0.730..0.730 rows=0 loops=1)

17.          

Initplan (for Function Scan)

18. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on services_commodity_codes (cost=0.00..1.88 rows=94 width=32) (actual time=0.009..0.009 rows=0 loops=1)

19. 0.001 0.001 ↓ 0.0 0 1

Limit (cost=0.00..0.03 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on services_commodity_codes services_commodity_codes_1 (cost=0.00..2.35 rows=94 width=4) (actual time=0.000..0.000 rows=0 loops=1)

21. 2.647 134.567 ↓ 2.2 2,217 1

Sort (cost=69.83..72.33 rows=1,000 width=60) (actual time=134.412..134.567 rows=2,217 loops=1)

  • Sort Key: goods_data.commodity_code, goods_data.year, goods_data.reporter_code, goods_data.partner_code, goods_data.refined_imports, goods_data.refined_exports
  • Sort Method: quicksort Memory: 270kB
22. 131.920 131.920 ↓ 2.2 2,217 1

CTE Scan on goods_data (cost=0.00..20.00 rows=1,000 width=60) (actual time=131.128..131.920 rows=2,217 loops=1)

23. 0.006 0.737 ↓ 0.0 0 1

Sort (cost=69.83..72.33 rows=1,000 width=60) (actual time=0.737..0.737 rows=0 loops=1)

  • Sort Key: services_data.commodity_code, services_data.year, services_data.reporter_code, services_data.partner_code, services_data.refined_imports, services_data.refined_exports
  • Sort Method: quicksort Memory: 25kB
24. 0.731 0.731 ↓ 0.0 0 1

CTE Scan on services_data (cost=0.00..20.00 rows=1,000 width=60) (actual time=0.731..0.731 rows=0 loops=1)