Greenplum: Query failing with 'ERROR: insufficient memory reserved for statement (execHHashagg.c:845)'

Products

• VMware Tanzu Data Suite

Issue/Introduction

The error, insufficient memory reserved for statement (execHHashagg.c:845) occurs when the HashAgg operator's memory requirement exceeds the allocated amount. 

Cause

This can occur with large partitioned tables when a sequential scan consumes significant portions of the statement_mem before the aggregation even begins. 

For example:

1. Consider a table with ~2900 partition leaves, running a query with a planner.
2. The planner creates a sequential scan, and each Seq Scan operator takes 100kB of memory. Adding these up, all SeqScan operators for this table takes ~290MB of memory, which exceeds the default statement_mem (of 256MB)
3. The hashagg operator doesn't have enough memory, as most of the memory is taken up by the Seq Scans.

 

 

Resolution

Possible workaround:

 Run query with `set optimizer_enable_orderedagg=on; set optimizer=on;`

• optimizer_enable_orderedagg=on:
  • This parameter allows GPORCA to generate plans using GroupAgg (which requires sorted input) instead of HashAgg, which is less memory intensive and unlikely to encounter the memory issue.
• set optimizer=on: 
  • This enables GPORCA which uses Dynamic Seq Scan. With a dynamic scan, even for tables with a high number of partitions, the size and memory footprint of the generated plan are independent of the total number of partitions.
    •  In the example above, if ORCA is used then there will be only 1 Dynamic Seq Scan node which will only take up 100kB memory (as opposed to ~290MB).