Improving Partition Pruning for Cross-Type Joins in Tanzu Greenplum

Regulatory investigations in capital markets frequently require reconstructing trading events from historical data spanning multiple years. When regulators ask a bank to reconstruct a trading event, every second matters. Analysts may need to search through years of partitioned market data spread across multiple systems, each recording time at different levels of precision, from nanoseconds to microseconds. To correlate these records, queries often perform timestamp conversions. Although the timestamps represent the same event, the resulting expressions can hide partition boundaries from the optimizer, leading to unnecessary scans of historical data. What should be a targeted investigation can quickly become a costly and time-consuming search through years of data.

That changes with the latest release of VMware Tanzu Greenplum. This architectural advancement enables partition pruning and predicate inference even when predicates involve expressions, casts, or conversions between different data representations. All without query rewrites or fragile manual workarounds.

This post walks through the changes to GPORCA’s constraint derivation framework that make partition pruning and predicate inference work for cross-type joins.

Motivating Query

For example, take an SEC audit of “ABC Capital Trading” involving potential insider trading. To conduct the investigation, ABC’s order trails, recorded with nanosecond-level timestamp9 precision, must be reconciled with the SEC’s microsecond-level timestamp logs. This precision discrepancy compels Tanzu Greenplum to use an implicit cast on the SEC data, which presents hurdles for optimization. As shown in the following figure, the “SEC Consolidated Audit Trail (CAT)” table uses standard microsecond (TS6) precision and is organized into daily partitions, which account for billions of records across the market.

Figure 1: Schema definition for the SEC Consolidated Audit Trail (CAT) table. Range partitioning by event_time, and hash distribution across segments.

The “ABC Capital Trading High-Frequency Execution Logs” table maintains higher precision: a nanosecond extension timestamp (TS9) for algorithmic tracking.

Figure 2: Schema definition for the abc_capital_trades table

SELECT a.symbol, count(*) AS matched, sum(s.reported_volume) AS sec_volume
FROM sec_cat_audit s JOIN abc_capital_trades a ON s.event_time = a.execution_time
WHERE a.execution_time BETWEEN '2025-06-15 00:00:00.000000000'::timestamp9_ntz
  AND '2025-06-15 23:59:59.999999999'::timestamp9_ntz
GROUP BY a.symbol;

In essence, the query above serves as a conceptual tool for regulatory cross-referencing. It illustrates a scenario where the SEC audits trading data to identify precise correlations between the official regulatory master logs (sec_cat_audit) and the internal private ledger provided by ABC Capital Trading (abc_capital_trades).

Figure 3: Query that joins between sec_cat_audit.event_time (microsecond precision) and abc_capital_trades.execution_time (nanosecond precision).

For the day of June 15, 2025, the query identifies concurrent trades across both datasets, grouping them by ticker symbol to display:

1. Ticker Symbol: For example, AAPL or TSLA.
2. Matched Trades: How many individual executions are perfectly aligned in time between ABC Capital’s records and the SEC’s records.
3. SEC Volume: The total accumulated trading volume reported to the SEC for those matched trades.

Queries with these characteristics expose the blind spot:

1. The join condition compares a timestamp column (microsecond precision, TS6) with a timestamp9 column (nanosecond precision, TS9). Because the types differ, the Tanzu Greenplum SQL parser inserts an implicit cast.
2. There is an additional filter condition on the timestamp9 join column.
3. Partitioning is done on the timestamp column.

The plan generated for this query was as follows:

Figure 4: Execution plan without cross-type join support in partition pruning, resulting in an expensive full table scan of all 366 partitions.

Earlier versions of the GPORCA optimizer relied on broad-spectrum partition scanning during join operations. The query engine has now evolved with advanced dynamic pruning, allowing it to bypass irrelevant partitions entirely and dramatically reduce both I/O and query latency.For the provided scenario, this limitation forced the system to process all 366 partitions rather than targeting the single pertinent one, leading to excessive I/O that far exceeded the query’s actual requirements. While users could manually insert redundant filter predicates to mitigate this, such workarounds are both brittle and contrary to the principles of a cost-based optimizer. Consequently, addressing this required a fundamental correction within the optimizer itself.

To understand why this correction was necessary and how GPORCA previously lost track of these partitions, we have to look at how the database maps relationships between different data types. Next, we look at where that relationship logic fell short.

Gaps: Constraint Framework Only Spoke “Base Columns”

To pass filters across a join, Tanzu Greenplum’s GPORCA groups together columns that must hold matching values. Historically, it could only do this for plain, unmodified columns. When a cross-type join occurs, the database inserts an implicit cast, transforming the predicate into something like:(s.event_time)::timestamp9_ntz = a.execution_time

The Solution: Intelligent Expression Resolution

To overcome this optimization blind spot without embarking on a risky, ground-up rewrite of the query engine, the Tanzu Greenplum team introduced an elegant enhancement to GPORCA’s constraint derivation framework. The optimizer has been upgraded with an intelligent abstraction layer that seamlessly translates complex expressions into logical references that the engine can natively understand and process.

Streamlining Expression Intelligence

Instead of treating modified columns, such as a timestamp wrapped in an implicit cast, as unreadable black boxes, GPORCA now dynamically integrates these expressions into its existing optimization pipeline.

• Unified Expression Mapping: The framework automatically pairs single-column expressions, casts, and functions with standard logical references. To the core reasoning engine, these complex expressions now behave exactly like direct column references, unlocking standard optimization paths.
• Smart Evaluation Layer: A new evaluation pipeline sits at the front of the constraint-derivation path. It handles standard identifiers naturally, simplifies structurally compatible data types, and applies a robust processing mechanism for complex expressions to ensure they are never ignored during planning.
• Recursive Pruning Tracing: GPORCA can now trace a chain of data transformations back to the original base column. This allows the query planner to accurately align partition boundaries with the modified expression, regardless of how many layers of casting or data-type conversions are applied.

Data-type alignment was also fixed. Specialized high-precision timestamp types now belong to are now treated as part of the standard datetime group of types, so GPORCA recognizes cross-type comparisons between them as valid, equivalent relationships.

The result is an optimizer that is vastly more sophisticated, capable of looking “through” data transformations to find the most efficient path to the underlying data.

Through this architectural upgrade, GPORCA produces a more efficient execution plan (detailed below) that successfully derives a new predicate: (((event_time)::timestamp9_ntz >= '2025-06-15 00:00:00.000000000'::timestamp9_ntz) AND ((event_time)::timestamp9_ntz <= '2025-06-15 23:59:59.999999999'::timestamp9_ntz)). By leveraging this inferred constraint, the system can now skip 364 of the 366 total partitions, drastically reducing data scans.

Figure: Execution plan with cross-type join partition pruning support, enabling constraint inference to filter out 364 unnecessary partition scans.

The Impact

The change to GPORCA’s constraint derivation framework helps Tanzu Greenplum clusters in three ways:

• Performance: Cross-type join queries on partitioned tables now get full partition pruning without query rewrites or fragile manual workarounds.
• Correctness: Strict data-type compatibility checking prevents silent wrong-result bugs caused by bad cross-type substitutions.
• Generality: This feature isn’t exclusive to timestamps. Any single-column deterministic expression, be it arithmetic (a + 1) or a scalar function, can now seamlessly pass constraint data down the planning pipeline.

Safe Deployment Note: The new expression-handling capability is fully gated behind the  optimizer_expression_constraints GUC (Grand Unified Configuration) flag, allowing production clusters to test and adopt the optimization incrementally. The data-type correctness fixes are always active.

By finding an elegant way to reuse the existing column machinery rather than over-engineering a new expression subsystem, the team has made the GPORCA optimizer significantly more robust, laying a clean foundation for even deeper expression-level reasoning in the future.

The Bottom Line: Solving the “Implicit Cast” Blind Spot

Cross-type joins, like matching nanosecond and microsecond timestamps, typically introduce implicit casts that hide partition boundaries from the query optimizer and force costly scans of every partition.

To solve this, Tanzu Greenplum has enhanced its GPORCA optimizer with an advanced expression-intelligence layer. By enabling the optimization framework to look past data-type conversions and evaluate underlying constraints directly, Tanzu Greenplum natively delivers full partition pruning and predicate pushdowns across mixed-type datasets. This results in massively accelerated regulatory and analytical queries, without the need for fragile, manual query workarounds.