Modern data warehouse systems are undergoing a fundamental transformation. With the rapid adoption of AI-driven analytics, feature engineering pipelines, and vector-based data representations, queries today are no longer simple or narrow. Instead, they are:
- Wider, with many payload columns and large row sizes
- Deeper, with complex multi-join graphs
- Heavier, with increased memory pressure and data movement
As a result, query performance now depends on how effectively the engine:
- Minimizes unnecessary data movement
- Applies filters as early as possible
- Avoids redundant copying of large payloads
- Preserves efficient columnar execution
To address these challenges, the Db2 Columnar Engine (CDE) introduces a key enhancement:
Elimination of Nested Query (NQ) boundaries through enhanced join execution and predicate pushdown. This feature is planned for Db2 version 12.1.5
Experimental Context
The results are based on a representative warehouse workload:
- Dataset: TPC-DS (300 GB scale)
- Pattern: Star-schema analytical queries
- Queries extended to include wide payload columns
This reflects modern workloads involving:
- Feature engineering tables
- Embeddings and high-dimensional data
- Denormalized schemas
Revisiting Nested Query (NQ) Boundaries: Purpose and Trade-offs
Nested Query (NQ) boundaries were originally introduced with a clear goal:
To provide the optimizer greater freedom in join ordering by isolating payload-dependent predicates
Why was this necessary?
In queries involving embedded column references across joins, certain predicates depend on columns not directly visible at higher levels of the plan.
Without isolation, this leads to:
- Hidden dependencies
- Restricted join order choices
- Suboptimal plan selection
How NQ boundaries helped?
NQs addressed this by:
- Decoupling parts of the query into independent blocks
- Isolating payload-dependent predicates
- Expanding the optimizer’s search space
- Enabling better join order exploration
✅ Key Perspective
NQ boundaries were an enabling mechanism — not a limitation.
The Trade-off: Lost Execution Efficiency
While NQs improved optimization flexibility, they introduced execution inefficiencies.
1. Pipeline Breaks and Materialization
- Execution split into stages
- Intermediate results materialized
- Pipeline efficiency reduced
2. Loss of Filter Pushdown
- Predicates cannot cross boundaries
- Filtering happens late
- Large intermediate results
3. Increased Data Copying
- Rows copied across boundaries
- Wide payloads duplicated
- Larger hash tables
4. Loss of Encoding Efficiency (Critical)
NQ boundaries often force decoding of columnar data
This leads to:
- Loss of compression benefits
- Higher memory usage
- Additional CPU overhead
5. Impact on Optimizer Decisions
- Decoding cost influences cost model
- Payload inflation affects estimates
- Overall plan quality can degrade
Net Effect
1 Better join ordering flexibility ✅
2 Full execution efficiency ❌
Key Insight
NQs enabled better plan exploration, but prevented Db2 from fully exploiting its execution engine capabilities.
What Changes with NQ Elimination
With the new enhancement:
- Embedded column access handled directly
- NQ boundaries eliminated where possible
- Plans remain fully flattened and pipelined
- Optimizer retains full join flexibility
Unlocking the Full Potential of Db2 Columnar Engine
Removing NQ boundaries allows Db2 to fully leverage existing capabilities:
1. Early Filtering (Synopsis-Based Pruning)
-
- Selective predicates applied before large joins
2. Efficient Data Pruning
-
- Irrelevant data avoided early
3. Deferred Column Fetching (Late Materialization)
-
- Columns accessed only when required
4. Preservation of Encoded Data
-
- Columnar compression retained deeper in execution
- Decode overhead minimized
Test Query 1 Under Study:
1. SELECT cs.*, d.*, i.*, c.*, cd.*
2 FROM catalog_sales cs
3 JOIN date_dim d ON cs.cs_sold_date_sk = d.d_date_sk
4 JOIN item i ON cs.cs_item_sk = i.i_item_sk
5 JOIN customer c ON cs.cs_bill_customer_sk = c.c_customer_sk
6 JOIN customer_demographics cd ON cs.cs_bill_cdemo_sk = cd.cd_demo_sk
7 WHERE d.d_year = 2000
8 AND d.d_moy = 1
9 AND i.i_category = 'Jewelry'
10 AND cd.cd_gender = 'M'
11 AND d.d_moy = c.c_birth_month
12 ORDER BY cs.cs_item_sk
13 FETCH FIRST 100 ROWS ONLY
Execution Plan (Test Query 1):
Disabled Execution Plan Enabled Execution Plan
Execution Transformation
|
Aspect
|
Earlier
|
Enhanced
|
|
Filter timing
|
Late
|
Early
|
|
Row propagation
|
Millions
|
Thousands
|
|
Left-leg size
|
Large
|
Reduced
|
|
Right-leg size
|
Unfiltered
|
✅ Pruned early
|
|
Payload copying
|
High
|
✅ Minimal
|
|
Encoding
|
Lost early
|
✅ Preserved longer
|
Execution Model Shift
Earlier Enhanced
|
Fact → Join → Join → Filter
|
Filtered Fact/Dimensions → Join → Small Result
|
Test Query 2 Under Study:
1 SELECT ss.*, d.*, i.*, hd.*, ib.*
2 FROM store_sales ss
3 INNER JOIN date_dim d ON ss.ss_sold_date_sk = d.d_date_sk
4 INNER JOIN item i ON ss.ss_item_sk = i.i_item_sk
5 INNER JOIN household_demographics hd ON ss.ss_hdemo_sk = hd.hd_demo_sk
6 INNER JOIN income_band ib ON hd.hd_income_band_sk = ib.ib_income_band_sk
7 WHERE d.d_year = 2000
8 AND d.d_moy = 1
9 AND i.i_category = 'Home'
10 AND hd.hd_buy_potential LIKE = '>%'
11 AND hd.hd_dep_count = i.i_manufact_id
12 FETCH FIRST 100 ROWS ONLY
Execution Plan (Test Query 2):
Disabled Execution Plan Enabled Execution Plan
Why Payload Width Amplifies Gains
Modern workloads include wide payloads across multiple joins.
With NQ
- Repeated copying of wide rows
- High memory usage
- Large hash tables
- Decode-heavy execution
Without NQ
- Minimal copying
- Fewer payloads propagated
- Efficient columnar execution
· 📈 Performance Impact
Across different query categories:
✅ Performance improvements observed: 2x to 50x
Observed scaling
- Moderate queries → 2x–6x
- Wide queries → 6x–8x
- Very wide + selective → 30x–50x
For this query
✅ ~30x improvement observed for query 1
✅ ~12x improvement observed for query 2
Performance improvements observed in large-scale MPP workloads
The PQA tests for the join optimization improvements were also executed on a Power10 Cloud Rack Cluster using the existing 30TB BDI/TPC-DS MPP setup. The results show significant performance gains for several queries, with improvements of up to ~29x in some cases. These findings further validate the benefits observed earlier on the 300GB SMP setup. While not every query shows improvement, there are no significant regressions, indicating stable behavior across workloads. Overall, the results confirm that the feature delivers consistent and scalable performance benefits across both SMP and large DPF environments.
Key Takeaways
1. Early Filter Pushdown
2. Right-Leg Pruning
3. Elimination of NQ Overheads
- Avoids copying
- Removes materialization
- Preserves encoding
Final Insight
Eliminating Nested Query boundaries does not introduce a new optimization—it unlocks the full potential of Db2 by aligning optimizer flexibility with execution efficiency.
Conclusion
Nested Query boundaries were originally introduced to enable flexible optimization. However, they created execution inefficiencies that became increasingly costly in modern workloads. With their elimination, Db2 Columnar Engine now achieves:
- Global optimization visibility
- Early data reduction
- Minimal data movement
- Efficient handling of wide payloads
Final Takeaway
As queries become wider and more complex, the benefits of NQ elimination grow proportionally, making this enhancement foundational for next-generation warehouse and AI-driven analytics workloads.
About the Authors
Ajith Krishnan R is a Software Developer in the Db2 Engine development team, with over 11 years of industry experience, including nearly 5 years focused on Db2 Runtime. His work primarily centres on Db2 engine development, with a strong emphasis on runtime domain, particularly in columnar data engine technologies. He holds a bachelor's degree in Electronics and Communication Engineering from Cochin University of Science and Technology (CUSAT). He can be reached at ajith.krishnan.r@ibm.com.
Ian Finlay is an STSM with nearly three decades of experience working on the IBM Db2 ecosystem, spanning the Query Compiler, Runtime, and the broader operational engine stack. He possesses deep expertise across all aspects of database engine architecture and design. He is widely recognised as one of the leading experts in query cost modelling and join processing. He can be reached at finlay@ca.ibm.com.
Ann Rose Benny is a Software Developer with 4 years of experience in Db2 Runtime, having begun her career in the Db2 Runtime domain. Her expertise includes Db2 engine development, with a strong focus on columnar engine technologies. She holds a bachelor’s degree in Computer Science and Engineering from NSS College of Engineering (NSSCE). She can be reached at ann.rose.benny1@ibm.com.
Disclaimer: This testing was based on the TPC-DS benchmark standard schema but has not been verified or audited by the TPC. The results cannot be compared to official TPC-DS metrics. TPC-DS is a trademark of the Transaction Processing Performance Council. This feature is planned for inclusion in the upcoming 12.1.5 release.