In part one of this series, we explored how VMware Tanzu Greenplum 7.6 introduced Implied Indexing to solve a critical inefficiency in Append-Optimized Columnar (AOCO) tables. By optimizing selective WHERE-clause queries, Implied Indexing ensured that only necessary rows were materialized. But while filters became highly efficient, not all hash joins could deliver the maximum performance benefits of columnar storage.
Tanzu Greenplum 7.9 introduced Early Join Filtering (EJF) which extends the same “read less, decide early” architecture to hash joins, EJF filters out non-matching rows before payload columns are ever materialized, delivering the query speed of an indexed table without the maintenance overhead.
In the following sections, we’ll walk through how Early Join Filtering works under the hood and the performance gains it delivers.
The Problem: Selective Joins Materialize Everything
Consider a highly selective join in which only 1% of rows in a large fact table match rows in a dimension table. In standard execution, the engine still has to reconstruct and decompress all payload columns for 100% of the fact table’s rows before the join can even evaluate which ones match. Roughly 99% of that decompression and materialization work is thrown away immediately at the probe step. On columnar storage, where decompression is often the dominant cost, that’s a lot of wasted CPU and I/O for rows that were never going to survive the join.
A query like the one below, with a selective filter feeding into a join, is exactly the shape we want to optimize:
SELECT *
FROM customers c
JOIN orders o USING (customer_id)
JOIN dim USING (customer_id)
WHERE c.country = 'France';
Here, the country = 'France' predicate and the join keys can both act as early filters, so the wide payload columns of orders and dim are only ever fetched for the small slice of rows that survive both.
The Fix: Filter On the Join Key Before Touching Anything
Early Join Filtering (EJF), introduced in Tanzu Greenplum 7.9, evaluates just the join key column to cheaply discard non-matching rows before the rest of the row’s columns are ever read.
EJF favors Early Filtering (EF) over Late Materialization (LM) as a strategy for MPP joins. A compact Bloom filter, built from the join’s build side, is pushed down directly into the scan layer. Non-matching rows are dropped during the scan itself, before the join step, avoiding the batch-sorting and intermediate tuple-copy overhead that late-materialization approaches carry.
Guardrails That Keep Early Join Filtering Safe
EJF is designed to help without ever making a plan worse, via several built-in safeguards:
- Planner costing: the optimizer only chooses the EJF path when it’s strictly cheaper than a standard hash join, which also keeps the plan search space from exploding.
- Build-side circuit breaker: if the number of distinct keys is high enough to push the Bloom filter’s false-positive rate toward 100% (making it pure CPU overhead with no filtering benefit), the filter is freed mid-build and probe-side verification is bypassed entirely.
- Probe-side adaptive breaker: the engine samples a small window of probe rows to check real-world selectivity at runtime. If too many rows are surviving the filter, meaning it isn’t actually filtering much, probing is disabled for the rest of that window to avoid paying for a filter that isn’t earning its keep.
Performance Analysis
TPC-DS (Scale 1000): Enabling the Early Join Filter feature demonstrates a significant net positive performance impact across the TPC benchmark workload, reducing overall workload execution time by 14.83% and improving 91 out of 99 queries (91.9%). The optimization cut roughly 3 hours off a 19.5-hour run.
Distribution of Query Performance Improvements
Beyond the aggregate median gain, the distribution of individual query results highlights the consistency of these improvements.
- High Win Rate: 91 queries (91.9%) showed positive performance gains, 2 queries stayed identical, and only 6 queries experienced slight regressions (maximum slowdown was 12.09%).
- Median Gain: The median improvement per query is 26.91%, with the largest single-query speedup reaching 79.86%.
Top 20 Queries by Absolute Time Savings
- Highest Relative Gain: Query 72 leads all queries with a 79.86% reduction in execution time, dropping from 1,519 units down to 306 units.
- Heavy Hitters: Query 74 delivered the largest absolute savings, saving 2,335 seconds (72.0% speedup).
- Top 3 Performers: Query 72 (79.86%), Query 93 (77.64%), and Query 74 (72.02%) all saw execution times slashed by over 70%.
- Threshold for Top 20: Every query in the top 20 achieved at least a 44.17% speedup (Query 55).
- Workload Drivers: The top 3 queries alone account for 40.1% of the entire workload’s cumulative time savings.
Detailed Breakdown of Regressed Queries
Out of the 99 benchmark queries, exactly 6 queries (6.1%) experienced a performance regression when the Early Join Filter feature was enabled.
- Impact Clustering: Only two queries (Query 14 and Query 95) incurred meaningful overhead, 12.09% and 10.25%, respectively. The remaining four regressions (Queries 30, 91, 4, and 9) were under 9%.
- Low Impact on Fast Queries: Queries 30 and 91 show higher percentage drops due to short baseline runtimes, but their absolute execution overhead is only 1–2 seconds.
- Workload Scope: Regressed queries represent only 6.06% of all tested queries (6 of 99), while improved queries account for 91.9% (91 of 99) and unchanged queries account for 2.02% (2 of 99).
One Scan Philosophy, Two Access Patterns
Implied Indexing and Early Join Filtering aren’t separate optimizations that happen to ship close together. They’re the same idea applied to the two dominant ways selectivity shows up in analytical SQL:
| Implied Indexing (7.6) | Early Join Filtering (7.9) | |
| Targets | WHERE-clause filters | Hash join keys |
| Narrow-first scan of | Filter columns | Join key column |
| Skips full read/decompress for | Rows failing the predicate | Rows failing the join |
| Avoids | Building/maintaining secondary indexes | Full materialization before the probe step |
Put differently, the engine now earns the benefits of an index, for both filters and joins. This is achieved without anyone building one. It decides, scan by scan, exactly which rows are worth fully materializing, and lets the rest stay compressed on disk.
High Performance Columnar Storage Built for Massive Scale
By combining Implied Indexing with Early Join Filtering, Tanzu Greenplum fundamentally rewrites the performance expectations for columnar storage. Data teams no longer have to compromise between the storage efficiency of AOCO tables and the rapid query execution traditionally reserved for heavily indexed environments.
This unified “decide early” architecture ensures that compute resources are spent strictly on relevant data, drastically lowering CPU and I/O utilization while accelerating complex analytical workloads. As enterprise datasets continue to scale, these innovations provide a highly optimized, cost-efficient foundation for next-generation data operations.
