explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uyMY

Settings
# exclusive inclusive rows x rows loops node
1. 5.568 12,902.856 ↓ 2,307.0 2,307 1

Sort (cost=174,301.97..174,301.98 rows=1 width=271) (actual time=12,902.605..12,902.856 rows=2,307 loops=1)

  • Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
  • Sort Method: quicksort Memory: 720kB
2. 176.976 12,897.288 ↓ 2,307.0 2,307 1

Merge Join (cost=120,928.65..174,301.96 rows=1 width=271) (actual time=9,503.820..12,897.288 rows=2,307 loops=1)

  • Merge Cond: (part.p_partkey = partsupp.ps_partkey)
  • Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
  • Rows Removed by Join Filter: 801
3. 0.000 34.849 ↓ 1.1 3,991 1

Gather Merge (cost=1,000.45..44,123.94 rows=3,539 width=30) (actual time=26.213..34.849 rows=3,991 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 9,261.576 9,261.576 ↑ 1.1 1,330 3

Parallel Index Scan using part_pkey on part (cost=0.42..42,715.43 rows=1,475 width=30) (actual time=3.862..3,087.192 rows=1,330 loops=3)

  • Filter: (((p_type)::text ~~ '%TIN'::text) AND (p_size = 19))
  • Rows Removed by Filter: 332003
5. 136.185 9,763.943 ↓ 33.7 792,114 1

Materialize (cost=119,928.21..120,045.85 rows=23,529 width=251) (actual time=9,384.333..9,763.943 rows=792,114 loops=1)

6. 1,667.482 9,627.758 ↓ 33.7 792,114 1

Sort (cost=119,928.21..119,987.03 rows=23,529 width=251) (actual time=9,384.324..9,627.758 rows=792,114 loops=1)

  • Sort Key: partsupp.ps_partkey
  • Sort Method: external sort Disk: 144792kB
7. 0.000 7,960.276 ↓ 33.7 792,560 1

Gather (cost=2,833.68..115,402.25 rows=23,529 width=251) (actual time=470.871..7,960.276 rows=792,560 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 548.911 8,576.244 ↓ 26.9 264,187 3

Hash Join (cost=1,833.68..112,049.35 rows=9,804 width=251) (actual time=467.227..8,576.244 rows=264,187 loops=3)

  • Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
9. 7,560.581 7,560.581 ↑ 1.3 1,333,333 3

Parallel Seq Scan on partsupp (cost=0.00..103,867.67 rows=1,666,667 width=14) (actual time=0.198..7,560.581 rows=1,333,333 loops=3)

10. 7.106 466.752 ↓ 33.7 9,907 3

Hash (cost=1,830.00..1,830.00 rows=294 width=245) (actual time=466.752..466.752 rows=9,907 loops=3)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2080kB
11. 13.624 459.646 ↓ 33.7 9,907 3

Hash Join (cost=13.50..1,830.00 rows=294 width=245) (actual time=120.069..459.646 rows=9,907 loops=3)

  • Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
12. 325.987 325.987 ↑ 1.0 50,000 3

Seq Scan on supplier (cost=0.00..1,609.00 rows=50,000 width=145) (actual time=0.014..325.987 rows=50,000 loops=3)

13. 0.012 120.035 ↓ 5.0 5 3

Hash (cost=13.49..13.49 rows=1 width=108) (actual time=120.035..120.035 rows=5 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.061 120.023 ↓ 5.0 5 3

Hash Join (cost=12.14..13.49 rows=1 width=108) (actual time=120.006..120.023 rows=5 loops=3)

  • Hash Cond: (nation.n_regionkey = region.r_regionkey)
15. 75.876 75.876 ↑ 1.0 25 3

Seq Scan on nation (cost=0.00..1.25 rows=25 width=112) (actual time=75.871..75.876 rows=25 loops=3)

16. 0.022 44.086 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 44.064 44.064 ↑ 1.0 1 3

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

  • Filter: (r_name = 'AFRICA'::bpchar)
  • Rows Removed by Filter: 4
18.          

SubPlan (for Merge Join)

19. 18.648 2,921.520 ↑ 1.0 1 3,108

Aggregate (cost=121.24..121.25 rows=1 width=32) (actual time=0.940..0.940 rows=1 loops=3,108)

20. 9.324 2,902.872 ↓ 2.0 2 3,108

Nested Loop (cost=0.86..121.23 rows=1 width=6) (actual time=0.910..0.934 rows=2 loops=3,108)

  • Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
  • Rows Removed by Join Filter: 2
21. 12.432 12.432 ↑ 1.0 1 3,108

Seq Scan on region region_1 (cost=0.00..12.12 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=3,108)

  • Filter: (r_name = 'AFRICA'::bpchar)
  • Rows Removed by Filter: 4
22. 12.432 2,881.116 ↑ 2.5 4 3,108

Nested Loop (cost=0.86..108.98 rows=10 width=10) (actual time=0.877..0.927 rows=4 loops=3,108)

23. 21.756 2,843.820 ↑ 2.5 4 3,108

Nested Loop (cost=0.72..107.43 rows=10 width=10) (actual time=0.872..0.915 rows=4 loops=3,108)

24. 2,685.312 2,685.312 ↑ 2.5 4 3,108

Index Scan using partsupp_pkey on partsupp partsupp_1 (cost=0.43..24.36 rows=10 width=10) (actual time=0.861..0.864 rows=4 loops=3,108)

  • Index Cond: (part.p_partkey = ps_partkey)
25. 136.752 136.752 ↑ 1.0 1 12,432

Index Scan using supplier_pkey on supplier supplier_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=12,432)

  • Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
26. 24.864 24.864 ↑ 1.0 1 12,432

Index Scan using nation_pkey on nation nation_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=12,432)

  • Index Cond: (n_nationkey = supplier_1.s_nationkey)