Indexes not used in queries where ORCA is on in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

In some cases, we find that the index is not used in some clusters, even if it is a very simple query that should be using indexes.

Meanwhile, in other clusters with almost the same settings running the same query, indexes are used as expected.

However, when we set ORCA to off, both the two clusters use indexes.

Environment

Product Version: 5.21

Resolution

There are several other Global User Configurations (GUCs) to control the index usage behavior for the optimizer, such as:

optimizer_enable_bitmapscan
optimizer_enable_indexscan

They should be set to on by default, and when they set to off, indexes might not be used when ORCA is set to on.

Note: Currently, these two GUCs are not shown in pg_settings.