ORCA optimizer falls back to Postgres Planner or encounters OOM on complex queries with DISTINCT operations.

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

A complex query may fail to generate an ORCA execution plan, instead falling back to the legacy Postgres Planner or failing with an Out of Memory (OOM) error.

This behavior typically occurs when the query utilizes a DISTINCT operation on a subset of target list columns, which may exceed the optimizer's ability to process the operation efficiently.

Cause

The root cause is a limitation in how the ORCA optimizer handles DISTINCT operations when applied to specific subsets of columns in the target list of highly complex queries.

In these scenarios, the optimizer may fail to find a valid plan within memory constraints or may determine the query is too complex for ORCA's current logic, triggering a fallback to the Postgres Planner to ensure the query can still execute, albeit potentially with sub-optimal performance.

Resolution

To resolve this issue, modify the query structure and adjust optimizer parameters:

• Query Rewrite: Replace the DISTINCT or DISTINCT ON operation with a PARTITION BY window function pattern. Ensure the logic remains semantically identical to the original requirement.
• GUC Configuration: Enable the query parameter optimizer by setting the following Global User Configuration (GUC) parameter:

SET optimizer_enable_query_parameter = on;

• Validation: Verify the new query plan by running an EXPLAIN to ensure ORCA is being utilized without falling back to the legacy planner.