{"id":27933,"date":"2025-06-20T04:50:24","date_gmt":"2025-06-20T11:50:24","guid":{"rendered":"https:\/\/www.pingcap.com\/?p=27933"},"modified":"2025-11-24T21:46:32","modified_gmt":"2025-11-25T05:46:32","slug":"hash-join-improvements-in-tidb-8-5-part-1","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/hash-join-improvements-in-tidb-8-5-part-1\/","title":{"rendered":"Improving TiDB Hash Joins: Up to 5\u00d7 Faster with No Tuning Required"},"content":{"rendered":"

Hash joins are a common performance bottleneck in SQL workloads, and TiDB was no exception. In TiDB 8.5<\/a>, we introduced a brand-new hash join executor that improves performance across the board. This new engine fully exploits modern hardware with multi-threaded build, vectorized probe, and a more efficient spill strategy. Internal benchmarks show it doubles the performance of common join workloads, reduces latency<\/a>, and improves predictability under tight memory constraints.<\/p>\n\n\n\n

This is the first part of a two part blog series. In this post, we’ll focus on high-level updates and what users need to know to benefit from the performance gains. <\/p>\n\n\n\n

<\/span>Why Joins Still Matter<\/span><\/h2>\n\n\n\n

Joins are at the heart of both analytical and transactional workloads. When they slow down, they do not just impact a single query. They can bottleneck entire applications.<\/p>\n\n\n\n

Hash joins are often the go-to for performance, but under real-world conditions like skewed data or constrained memory, they can become unreliable and hard to debug. TiDB\u2019s original hash join executor had several limitations: it built the hash table using a single thread, relied on Go\u2019s general-purpose map structure, and had limited spill capabilities when memory ran out.<\/p>\n\n\n\n

These issues led to high latency and inconsistent performance. If you have ever had a TiDB join stall, this was likely the cause.<\/p>\n\n\n\n

<\/span>Background<\/span><\/h2>\n\n\n\n

TiDB uses hash joins to execute a wide range of SQL queries, particularly when equality conditions are involved across large datasets. In a typical hash join, the executor selects one table as the build side and constructs an in-memory hash table from its rows. The other table is used to probe for matches.<\/p>\n\n\n\n

For example, the following queries all trigger a hash join in TiDB:<\/p>\n\n\n\n

-- Classic inner join\nSELECT * FROM lineitem l JOIN orders o ON l.orderkey = o.orderkey;\n\n-- Semi join with filter\nSELECT * FROM lineitem l WHERE EXISTS (\n  SELECT 1 FROM orders o WHERE l.orderkey = o.orderkey AND o.orderstatus = 'F'\n);\n\n-- Join with additional filter\nSELECT * FROM lineitem l JOIN orders o\n  ON l.orderkey = o.orderkey AND l.partkey > o.custkey;<\/code><\/pre>\n\n\n\n
In earlier versions of TiDB, the build side was processed by a single thread using Go\u2019s built-in map structure. This structure was not optimized for large-scale joins. It introduced pointer indirection, poor CPU cache locality, and runtime map resizes, which became performance bottlenecks<\/a> as data volumes grew.<\/p>\n\n\n\n
The probe phase had similar issues. Joins were executed one row at a time, which prevented any form of vectorization. For join types like semi joins, the executor did not short-circuit after finding a match. Instead, it continued scanning for duplicates. If the join involved string keys, collation logic was re-evaluated on each candidate match, further increasing cost.<\/p>\n\n\n\n
When memory usage exceeded configured limits, TiDB attempted to spill intermediate data to disk. However, only the row data was spilled; the hash table remained in memory. This limited how much memory could be reclaimed. Under constrained memory, this often resulted in query failures or significantly degraded performance due to inefficient disk access patterns.<\/p>\n\n\n\n
These limitations motivated a complete redesign of the hash join executor in TiDB 8.5, with a focus on parallelism, memory efficiency, and predictable performance.<\/p>\n\n\n\n
<\/span>The Legacy Bottlenecks<\/span><\/h2>\n\n\n\n
The original hash join executor in TiDB had several architectural limitations that created performance challenges, especially in large or complex queries like the examples shown in the previous section. These issues affected all three major phases of execution: build, probe, and spill.<\/p>\n\n\n\n
Build phase<\/h3>\n\n\n\n
The build phase was limited by its single-threaded design. Even when TiDB was running on a system with 16 or 32 cores, only one core was used to construct the hash table. This created a hard limit on throughput, particularly for joins like the lineitem<\/code>\u2013orders<\/code> query:<\/p>\n\n\n\n
SELECT * FROM lineitem l JOIN orders o ON l.orderkey = o.orderkey;<\/code><\/pre>\n\n\n\n
Additional bottlenecks included:<\/p>\n\n\n\n