explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cvMv

Settings
# exclusive inclusive rows x rows loops node
1. 4.477 150,492.478 ↓ 29.9 29,921 1

Merge Full Join (cost=168.52..203.53 rows=1,000 width=60) (actual time=150,485.912..150,492.478 rows=29,921 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.010..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.021 0.021 ↑ 1.2 5 1

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

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

CTE services_commodity_codes

8. 0.012 0.012 ↓ 0.0 0 1

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

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

CTE goods_data

10. 150,434.673 150,434.698 ↓ 29.9 29,921 1

Function Scan on get_refined_raw_view_sector (cost=0.40..10.40 rows=1,000 width=60) (actual time=150,431.588..150,434.698 rows=29,921 loops=1)

11.          

Initplan (for Function Scan)

12. 0.022 0.022 ↑ 1.2 5 1

CTE Scan on goods_commodity_codes (cost=0.00..0.12 rows=6 width=32) (actual time=0.016..0.022 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. 2.368 2.381 ↓ 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=2.381..2.381 rows=0 loops=1)

17.          

Initplan (for Function Scan)

18. 0.012 0.012 ↓ 0.0 0 1

CTE Scan on services_commodity_codes (cost=0.00..1.88 rows=94 width=32) (actual time=0.012..0.012 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. 42.711 150,485.608 ↓ 29.9 29,921 1

Sort (cost=69.83..72.33 rows=1,000 width=60) (actual time=150,483.510..150,485.608 rows=29,921 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: 3,106kB
22. 150,442.897 150,442.897 ↓ 29.9 29,921 1

CTE Scan on goods_data (cost=0.00..20.00 rows=1,000 width=60) (actual time=150,431.589..150,442.897 rows=29,921 loops=1)

23. 0.010 2.393 ↓ 0.0 0 1

Sort (cost=69.83..72.33 rows=1,000 width=60) (actual time=2.393..2.393 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. 2.383 2.383 ↓ 0.0 0 1

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

Planning time : 0.242 ms
Execution time : 150,494.456 ms