Query is failing with "workfile per query size limit exceeded" error, and some cases cause huge spilling

Products

• VMware Tanzu Data Suite

Issue/Introduction

User encountering an issue with query failing with the following error, and in some cases it runs with huge spilling. The data is not changed and the same query for a different date is successfully completing.

workfile per query size limit exceeded

Environment

GPDB6

Cause

Possible cause:

When stats are collected for a leaf partition, the stats are merged into the root partition. This means that if a value is common only in a single individual partition leaf, that will not be reflected well in the root partition statistics. Orca uses root partition statistics to make its decision, whereas the Postgres-based planner uses the leaf partition statistics. Both have advantages and disadvantages in various cases, but when querying an individual leaf partition, the Postres-based Planner will give much more accurate cardinality estimates when there are multiple correlated predicates, which can potentially be the case for this query.

Resolution

This is a limitation in the Orca optimizer's architecture in GPDB6. The best path forward for this issue is to set `optimizer=off` for only this query.