SQL fails with out of memory error.
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)
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.)
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.