explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a2ST : Optimization for: Optimization for: Optimization for: plan #Rl3v; plan #3WDY; plan #hr8E

Settings

Optimization path:

Optimization(s) for this plan:

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

Limit (cost=4,975,178.72..4,975,178.72 rows=1 width=269) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=4,975,178.72..4,975,178.72 rows=1 width=269) (actual rows= loops=)

  • Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
3. 0.000 0.000 ↓ 0.0

Merge Join (cost=846,074.42..4,975,178.71 rows=1 width=269) (actual rows= loops=)

  • Merge Cond: (part.p_partkey = partsupp.ps_partkey)
  • Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
4. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,000.46..859,946.70 rows=81,028 width=30) (actual rows= loops=)

  • Workers Planned: 2
5. 0.000 0.000 ↓ 0.0

Parallel Index Scan using pk_part on part (cost=0.44..849,594.03 rows=33,762 width=30) (actual rows= loops=)

  • Filter: (((p_type)::text ~~ '%NICKEL'::text) AND (p_size = 4))
6. 0.000 0.000 ↓ 0.0

Materialize (cost=845,073.87..847,426.76 rows=470,577 width=247) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=845,073.87..846,250.32 rows=470,577 width=247) (actual rows= loops=)

  • Sort Key: partsupp.ps_partkey
8. 0.000 0.000 ↓ 0.0

Gather (cost=1,024.87..691,360.94 rows=470,577 width=247) (actual rows= loops=)

  • Workers Planned: 2
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=24.87..643,303.24 rows=196,074 width=247) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=24.31..27,573.99 rows=2,451 width=243) (actual rows= loops=)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
11. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on supplier (cost=0.00..25,962.67 rows=416,667 width=143) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=24.29..24.29 rows=1 width=108) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.14..24.29 rows=1 width=108) (actual rows= loops=)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
14. 0.000 0.000 ↓ 0.0

Seq Scan on nation (cost=0.00..11.70 rows=170 width=112) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=12.12..12.12 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on region (cost=0.00..12.12 rows=1 width=4) (actual rows= loops=)

  • Filter: (r_name = 'ASIA'::bpchar)
17. 0.000 0.000 ↓ 0.0

Index Scan using partsupp_ps_suppkey_idx on partsupp (cost=0.57..250.44 rows=78 width=12) (actual rows= loops=)

  • Index Cond: (ps_suppkey = supplier.s_suppkey)
18.          

SubPlan (for Merge Join)

19. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,713.74..1,713.75 rows=1 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=13.13..1,713.73 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (supplier_1.s_nationkey = nation_1.n_nationkey)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.14..24.29 rows=1 width=4) (actual rows= loops=)

  • Hash Cond: (nation_1.n_regionkey = region_1.r_regionkey)
22. 0.000 0.000 ↓ 0.0

Seq Scan on nation nation_1 (cost=0.00..11.70 rows=170 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=12.12..12.12 rows=1 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on region region_1 (cost=0.00..12.12 rows=1 width=4) (actual rows= loops=)

  • Filter: (r_name = 'ASIA'::bpchar)
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..1,687.37 rows=166 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using pk_partsupp on partsupp partsupp_1 (cost=0.57..301.91 rows=166 width=8) (actual rows= loops=)

  • Index Cond: (ps_partkey = part.p_partkey)
27. 0.000 0.000 ↓ 0.0

Index Scan using pk_supplier on supplier supplier_1 (cost=0.42..8.35 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (s_suppkey = partsupp_1.ps_suppkey)