Analyze on partitioned tables taking a "ShareUpdateExclusiveLock' lock on root table.

Products

• VMware Tanzu Greenplum

Issue/Introduction

Root cause

Running an analyze on the partition directly can sometimes trigger an internal analyze on the root table which locks the root table in a "ShareUpdateExclusiveLock" lock. This can cause locking contention on other queries running on this table and slow down performance.

Example of an internal analyze being run on root "public.p1":

gpadmin=# analyze verbose p1_1_prt_1;
INFO: analyzing "public.p1_1_prt_1"
INFO: Executing SQL: select pg_catalog.gp_acquire_sample_rows(24579, 10000, 'f');
INFO: analyzing "public.p1" inheritance tree

ANALYZE in a maintenance scenario where scripts are running an analyze on partitions separately can lead to excessive locking on the root table and thus lead to locking contention and blocked queries.

One way to disable the analyze on the root table "INFO: analyzing "public.p1" inheritance tree" is to set the following GUC to off:

set optimizer_analyze_enable_merge_of_leaf_stats = off;

Once analyze on all the partitions are completed, you can then run a separate analyze on the root using:

analyze rootpartition public.p1;

Important:

When using ORCA (optimizer=on), it is important to run an analyze on the root since ORCA uses root stats when generating explain plans.

Environment

Product Version: 6.x 

Resolution

Set "set optimizer_analyze_enable_merge_of_leaf_stats = off;" when analyzing partitions, then run a separate "analyze rootpartition ".