Introduction

If you work with data analytics, you’ve probably had this painful experience: it’s “just” a big table join, but the database behaves like an old TV stuck on a frozen frame.

The outer table easily reaches tens or hundreds of millions of rows, and the cost of scanning, shipping and computing keeps snowballing. Five minutes later, your query is still “spinning” with no result in sight.

In this article, we’ll look at a performance accelerator that specifically targets slow big table joins — YMatrix Runtime Filter. In many scenarios, it can make your queries run 10× faster, and in some cases even 20× faster.

01. Why are big table joins slow? The “wasted work” of traditional methods

Let’s quickly recap how a traditional hash join works:

The database first scans the smaller inner table (for example, a user table) and builds a hash dictionary from it — think of it as a pre-built index.

It then scans the large outer table (for example, an orders table) and probes that dictionary row by row to find matching records.

Here’s the problem: the outer table might hold hundreds of millions of rows, but only a few thousand may actually match.

Yet the database still has to scan, transfer and process all those rows on the outer side. That means over 99% of the work is wasted — pure overhead that burns time and resources for no benefit.

02. Runtime Filter: Narrow down “useful data” before scanning

The smart part about a runtime filter is this:

Instead of blindly scanning the outer table first, it uses the inner table to build a set of “valid keys” and tells the outer table upfront: “Only keep rows whose keys are in this set.”

A simple example:

• Orders table (orders)

• • 100 million rows
• • Contains order ID, customer ID and other order attributes

• Customer table (customer)

• • 100,000 rows
• • Contains customer ID, country, and other attributes
• • Each country has roughly 4,000 customers

Now we want to count the number of orders from customers in China.

Traditional approach:

• Scan all 100 million rows in orders
• Join with customer
• Then apply the country = 'China' filter

With a runtime filter:

1. The database uses the customer table to build a set of customer IDs from China.
2. It pushes this set down as a filter on the orders table.
3. Only orders whose customer_id is in that set are scanned and passed downstream.

If the inner table ultimately contributes only 1,000 customer IDs, the runtime filter can precisely keep the rows matching those 1,000 IDs — and skip the remaining 99% of the outer table.

This immediately cuts down I/O and CPU work, so the query naturally becomes much faster.

03. Real-world results: How much speed-up can Runtime Filter bring?

Nothing speaks louder than real numbers. Here are some results from YMatrix benchmarks:

• TPC-H Q17 (classic benchmark)

• • With Runtime Filter enabled, query performance improved by 10–20×.

• TPC-H S100 (scale 100 dataset)

• • Total runtime dropped from 100 seconds to 80 seconds.

• Real business workload

• • Rows scanned on the outer table dropped from 24.43 million to 23,000.
• • Query time went from 20+ seconds down to 2.6 seconds (more than 8× faster).

In summary, Runtime Filter shines when you have a big outer table and a small result set:

• The larger the outer table,
• The smaller the actual join result,

…the more accurately the runtime filter can eliminate useless rows — and the more performance you gain.

04. Is it hard to use? Just 3 simple steps

YMatrix’s Runtime Filter is designed to be very user-friendly and is enabled by default. With just three small tips, you can start using it effectively:

1.Toggle the feature (on/off)

• Enabled by default.
• To temporarily disable or enable it, use a single SQL command, for example:

SET enable_runtime_filter = off;

2.Check the execution plan

• Use EXPLAIN (VERBOSE) to inspect the plan.
• If you see keywords like local/global (local vs. cross-node transfer) and initiator/target (sender/receiver), it indicates the runtime filter is in play.

3.Validate the effect

• Run the query with EXPLAIN ANALYZE.
• On scan nodes you’ll see something like inputrows/outputrows (rows entering / rows passing the filter).
• If outputrows is much smaller than inputrows — for example, 10 million → 20,000 — it means the runtime filter has successfully skipped 99.8% of the useless rows.

05. When does Runtime Filter work best?

Runtime Filter is powerful, but it’s not a magic bullet for every query. It performs best in scenarios like these:

✅ Large outer table

• • The more rows on the outer side, the more time you save after filtering.

✅ Small join result set

• • The fewer keys from the inner table that actually participate in the join, the more precise (and effective) the filter becomes.

✅ Filter size stays under control

• • If the inner table has too many distinct keys, YMatrix will automatically turn off the runtime filter to avoid overhead outweighing the benefits.

Next time you’re stuck with a painfully slow big table join, try enabling Runtime Filter — sometimes it only takes a single SET command to make your query take off.

Your queries deserve to run faster.