explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ulPx

Settings
# exclusive inclusive rows x rows loops node
1. 13.299 1,267.525 ↓ 28.7 28,681 1

Merge Full Join (cost=168.52..203.53 rows=1,000 width=60) (actual time=1,247.951..1,267.525 rows=28,681 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.005 0.022 ↑ 20.0 5 1

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

4. 0.017 0.017 ↑ 1.0 1 1

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

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

CTE goods_commodity_codes

6. 0.036 0.036 ↑ 1.2 5 1

CTE Scan on commodity_codes (cost=0.00..2.25 rows=6 width=32) (actual time=0.027..0.036 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.012..0.012 rows=0 loops=1)

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

CTE goods_data

10. 1,181.513 1,181.557 ↓ 28.7 28,681 1

Function Scan on get_refined_raw_view_sector (cost=0.40..10.40 rows=1,000 width=60) (actual time=1,173.556..1,181.557 rows=28,681 loops=1)

11.          

Initplan (for Function Scan)

12. 0.039 0.039 ↑ 1.2 5 1

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

13. 0.002 0.005 ↑ 1.0 1 1

Limit (cost=0.00..0.03 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

14. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

15.          

CTE services_data

16. 0.888 0.903 ↓ 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.903..0.903 rows=0 loops=1)

17.          

Initplan (for Function Scan)

18. 0.013 0.013 ↓ 0.0 0 1

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

19. 0.001 0.002 ↓ 0.0 0 1

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

20. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

21. 53.830 1,253.312 ↓ 28.7 28,681 1

Sort (cost=69.83..72.33 rows=1,000 width=60) (actual time=1,247.029..1,253.312 rows=28,681 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,009kB
22. 1,199.482 1,199.482 ↓ 28.7 28,681 1

CTE Scan on goods_data (cost=0.00..20.00 rows=1,000 width=60) (actual time=1,173.559..1,199.482 rows=28,681 loops=1)

23. 0.009 0.914 ↓ 0.0 0 1

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

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

Planning time : 0.313 ms
Execution time : 1,273.666 ms