Query runs slower for normal user compared to gpadmin user or superuser user in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

When running a simple select query, it runs 4 to 5 times slower for a normal user compared to running it as a gpadmin user or superuser user.

Resource Queues are used to manage resources.

Environment

Product Version: 6.16

Resolution

Superusers will bypass resource queues and run queries without limitations. If the tables accessed in the query are compressed, then the amount of CPU allocated will affect the performance.

If the GUC gp_resqueue_priority_cpucores_per_segment is set incorrectly then the CPU can be under-utilized and can result in queries performing poorly.

See gp_resqueue_priority_cpucores_per_segment for information on how to set the GUC correctly.

To set the GUC, run this command:

gpconfig -c gp_resqueue_priority_cpucores_per_segment -v <segment_value> -m <master_value> 

For example, for a GUC with 72 cores on all hosts and 8 primary segments on each segment host, run this command:

gpconfig -c gp_resqueue_priority_cpucores_per_segment -v 9 -m 72

The database needs to be restarted for the GUC to take effect.

To restart the database, run the following command:

gpstop -rf