Pivotal Knowledge Base

Follow

SQL fails with out of memory error due to missing column level statistics

Problem

SQL fails with out of memory error.

For example:

ERROR:  out of memory  (seg0 slice11 sdw1.xxx.xxx:1025 pid=761750)
ERROR:  could not temporarily connect to one or more segments (cdbgang.c:2426)

Cause

Some of the involved tables are missing column level statistics which leads the GPDB optimizer to choose wrong SQL plan - which attempts to use too much memory. (Tables could have or not have valid table level statistics.)

Solution

1. Make sure all tables involved have table level statistics.

select relpages,reltuples from pg_class where relname='abc' and relnamespace=123;

2. Make sure all tables have column level statistics also.

select * from pg_stats where schemaname ='sample_schema' and tablename='abc' ;

3. Analyze the tables which do not have table level or column level statistics.

analyze sample_schema.abc;

 

Comments

Powered by Zendesk