Slow running queries in Greenplum 6.x

Products

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

Issue/Introduction

In Tanzu Greenplum version 6.x, we have seen cases where the queries are reported to be running slow or it hangs.

Environment

Greenplum Database

Resolution

Check the value of optimizer_join_order.If the value is "exhaustive2", change it to "exhaustive" and then reload the config:

[gpadmin@biscuit-m ~]$ gpconfig -s optimizer_join_order
Values on all segments are consistent
GUC          : optimizer_join_order
Master  value: exhaustive2
Segment value: exhaustive2

Change it to exhaustive and reload the config:

[gpadmin@biscuit-m ~]$ gpconfig -c optimizer_join_order -v exhaustive
20210427:19:53:05:001928 gpconfig:biscuit-m:gpadmin-[INFO]:-completed successfully with parameters '-c optimizer_join_order -v exhaustive'
[gpadmin@biscuit-m ~]$ gpstop -u
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Starting gpstop with args: -u
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Gathering information and validating the environment...
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Obtaining Segment details from master...
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c'
20210427:19:53:08:002034 gpstop:biscuit-m:gpadmin-[INFO]:-Signalling all postmaster processes to reload
[gpadmin@biscuit-m ~]$ gpconfig -s optimizer_join_order
Values on all segments are consistent
GUC          : optimizer_join_order
Master  value: exhaustive
Segment value: exhaustive

Also, it is recommended to follow the instructions in Considerations when Using GPORCA because it has information about different GUCs and how ORCA behaves.