Partition Elimination Failure on timestamp9 Joins After Upgrade to Greenplum 6.31.2

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum

Issue/Introduction

After upgrading to Tanzu Greenplum 6.31.2, queries that join partitioned tables using custom data types (such as those from the timestamp9 extension) may experience performance regressions.

Observed Behavior:

• The primary table (with the explicit WHERE clause) prunes partitions correctly.
• The joined table (e.g., a reference or history table) fails to perform Dynamic Partition Elimination (DPE).
• The execution plan shows a full table scan on the joined table, touching all partitions despite a highly selective join key.

Example Scenario

A query filters a Sales table by date and joins it with an Order History table:

• Table_A (Sales): Partitioned on timestamp9_ntz.
• Table_B (History): Partitioned on native timestamp (without time zone).

SELECT a.*, b.status
FROM Table_A a
JOIN Table_B b
   ON a.id = b.id
  AND a.event_time = b.event_time
WHERE a.event_time BETWEEN '2025-01-01' AND '2025-01-02';

Result: Table_A scans 2 partitions; Table_B scans all partitions.

Environment

Tanzu Greenplum 6.31.2

Cause

The regression stems from how the GPORCA optimizer handles Dynamic Partition Elimination when join conditions require type casting between different but related data types.

1. Stricter Operator Family Checks: In Greenplum 6.31.2, GPORCA was hardened to require stricter matching of operator classes and families. It now requires explicit proof that operators preserve ordering across different types.
2. Type Disparity & Opaque Expressions: When joining timestamp9_ntz to a native timestamp, the planner inserts a cast. GPORCA treats this casted comparison as an "opaque expression," meaning it cannot determine if the ordering of the custom type matches the ordering of the native partition key.

 

Resolution

A product fix is planned for a future Greenplum 6.31.x maintenance release. Please subscribe and follow this article to receive updates on the fixed version and upgrade guidance.

Workaround: Predicate Redundancy (Static Pruning)

You can restore performance by converting the failed dynamic pruning into static partition pruning. This involves making the partition key visible to both tables in their native types.

Step 1: Expose the Native Partition Key

Modify your view or subquery to project the join key from the second table in its native format, avoiding the cast that GPORCA cannot resolve:

CREATE OR REPLACE VIEW v_performance_workaround AS
SELECT a.*,
       b.event_time AS event_time_native -- Raw native partition key
FROM Table_A a
JOIN Table_B b
  ON a.id = b.id
 AND a.event_time = b.event_time::timestamp9_ntz;

Step 2: Apply Redundant Filters

Explicitly apply the range filter to both columns. This forces GPORCA to prune both tables independently before the join:

SELECT *
FROM v_performance_workaround
WHERE event_time BETWEEN '2025-01-01' AND '2025-01-02'        -- Prunes Table_A
  AND event_time_native BETWEEN '2025-01-01' AND '2025-01-02'; -- Forces pruning on Table_B

 

 

Additional Information

References:

VMware Greenplum 6.x Release Notes

Tanzu Greenplum Schema Design