Certain queries falling back to planner when using ORCA [Optimizer]

Products

• VMware Tanzu Greenplum

Issue/Introduction

When running, certain queries will fall back on the legacy planner [Postgres query optimizer] which might cause poor performance. This can happen especially after a cluster expansion.

Environment

Product Version: 6.15

Resolution

• Set the following parameter: set optimizer_trace_fallback=on;
• Rerun the explain

The output might show us the reason why it is falling back:

Example: 1056: INFO: 0A000: GPORCA failed to produce a plan, falling back to planner DETAIL: Unknown error: Partially Distributed Data LOCATION: CTranslatorRelcacheToDXL.cpp:541

Solution:

Run the following query for all the tables [replace table names in the "IN" clause]. The output will point us to specific tables whose data is not redistributed to new segments:

SELECT oid::regclass, p.numsegments FROM pg_class rel JOIN gp_distribution_policy p ON rel.oid = p.localoid WHERE relname IN ('table_1','table_2');

Identify all the tables which do not have data distributed to the new segments. Alternatively, you can also run the following query to check which tables do not have data on new segments: 

select max(gp_segment_id),count(*) from gp_dist_random('table_name') order by 1;

**For problem tables, the max(gp_segment_id) value will be less than other tables.

For all the tables that do NOT have data distributed to the new segments, run an expansion:

alter table <table_name> expand table; 

Once completed, rerun the explain on the query and confirm whether it uses ORCA [Optimizer: Pivotal Optimizer (GPORCA)].