Out of Memory error while creating table but SELECT works

user_3546389 0
Viewed 3463

I'm trying to CREATE a table using CREATE TABLE AS, which gives the following error:

[Amazon](500310) Invalid operation: Out Of Memory: 
Details: 
 -----------------------------------------------
  error:  Out Of Memory: 
  code:      1004
  context:   alloc(524288,MtPool)
  query:     6041453
  location:  alloc.cpp:405
  process:   query2_98_6041453 [pid=8414]
  -----------------------------------------------;

I'm getting this error everytime I execute the query, but executing just the SELECT part of the query (without the CREATE TABLE AS) works fine. The result has around 38k rows. However I see there's a drastic difference in Bytes returned in the sequential scan on one table.

SELECT

enter image description here enter image description here

CREATE TABLE AS SELECT enter image description here enter image description here

I fail to understand why there's so much difference between these two scenarios and what can be done to mitigate it. I also tried to create a TEMP TABLE but that also results in memory error.

I'm not great at understanding Query Plans (never found a detailed guide to it for Redshift, so if you could link to some resource that'd be a bonus).

Update : Also tried creating the table first and then INSERTing the data using SELECT, that also gives the same error.

Update 2 : Tried set wlm_query_slot_count to 40; or even 50 but still the same error.

amazon-redshift sql
3 Answers

We ran into a similar issue after our clusters got updated to the latest release (1.0.10694).

Two things that helped:

We suspect that AWS may have changed something with memory management with the most recent updates. I'll update once we hear back.

user_38665390

As a workaround, you could try inserting the records in batches.

user_103359090

Solved this using manual WLM implement.

user_32313470
Related