Query performance drops significantly following upgrade from VMware Tanzu Greenplum 6.4.0 or higher

Products

• VMware Tanzu Greenplum

Issue/Introduction

Due to a fix in VMware Tanzu Greenplum 6.5.0 and above, there can be a performance degradation for some queries with a Query Optimizer. The following is a detailed description of why this happens:

• Removed support of range predicates for dynamic partition elimination (DPE). The reasoning is that for the general case only, equality predicate is useful for dynamic partition elimination, and range predicates based on dynamic partition elimination can be very expensive.

In other words, queries using dynamic partition elimination (DPE) with range predicates are running slow. This issue has been fixed by allowing only equality comparisons with DPE.

Environment

Product Version: 6.13

Resolution

This behavior can be reversed by upgrading to GPDB 6.13.0 or higher and setting optimizer_enable_range_predicate_dpe to on with the following command:

gpconfig -c optimizer_enable_range_predicate_dpe -v on --skipvalidation
gpstop -u