GPSS MERGE Jobs Generate Large Spill Files and Poor Performance After Upgrade to Greenplum 7.6.1

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

After upgrading environment from Greenplum Database 7.5.x to 7.6.1, customers may observe that certain GPSS MERGE jobs become significantly slower and generate very large spill (workfile) sizes. This behavior is typically not observed in Greenplum 7.5.x with the same workload and data volume.

Environment

GPDB: 7.6.1

Cause

The performance regression is due to optimizer misestimation when GPSS uses internal temporary tables (gpsstmp_*) that lack statistics. In Greenplum 7.6.1, changes to ORCA’s handling of unanalyzed tables can lead to incorrect join direction selection, resulting in large hash spills.

Resolution

Option 1: Set Globally (Recommended)

Add the following parameter to "postgresql.conf" on the Greenplum coordinator and reload the configuration:

gp_autostats_mode = on_no_stats

then reload

gpstop -u

 

Option 2: Set for GPSS User Only

If GPSS runs under a dedicated database user, configure the setting at the user level:

ALTER USER <gpss_user> SET gp_autostats_mode = on_no_stats;

This limits the change to GPSS workloads only.