When the key values are the same, the memory limit is exceeded when making a hash join Source code comment If we dumped out either all or none of the tuples in the table, disable further expansion of nbatch. This situation implies that we have enough tuples of identical hashvalues to overflow spaceAllowed. Increasing nbatch will not fix it since there's no way to subdivide the group any more finely. We have to just gut it out and hope the server has enough RAM.
create table t1(a int);
create table t2(a int);
insert into t1 values(1);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t2 select * from t1;
select count(*) from t1 join t2 on t1.a=t2.a;
I want to be able to query the result with limited memory size