In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?

I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.

Here is the PostgreSQL script I wrote to explain my question.

drop table if exists t1 cascade;
drop table if exists t2 cascade;
create table t1
as
select trunc((i-1)/15) n1,
       trunc((i-1)/15) n2,
       rpad(i::text,180)::text    v2
  from generate_series(1,30000) a(i); 
create table t2 as select * from t1;

update t1 set v2 = 'kk' where v2 like '42%';
vacuum t1;
vacuum t2;

Below is the query I executed followed by its execution plan

select  * from t1
except
select * from t2;

**HashSetOp Except** (actual time=32.602..33.485 rows=11 loops=1)
   Buffers: shared hit=1716
   ->  Append (actual time=0.014..15.065 rows=60000 loops=1)
         Buffers: shared hit=1716
         ->  Subquery Scan on "*SELECT* 1" (actual time=0.013..6.388 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t1 (actual time=0.010..2.801 rows=30000 loops=1)
                     Buffers: shared hit=858
         ->  Subquery Scan on "*SELECT* 2" (actual time=0.011..5.596 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t2 (actual time=0.009..1.590 rows=30000 loops=1)
                     Buffers: shared hit=858
 Planning:
   Buffers: shared hit=40
 Planning Time: 0.230 ms
 Execution Time: 34.567 ms

Below is another query with the same data followed by its execution plan.

```
select n1, n2, v2
from t1
where not exists (select 1
from t2
where t1.n1 = t2.n1
and t1.n2 = t2.n2
and t1.v2 = t2.v2)
Hash Anti Join (actual time=12.733..35.390 rows=111 loops=1)
Hash Cond: ((t1.n1 = t2.n1) AND (t1.n2 = t2.n2) AND (t1.v2 = t2.v2))
Buffers: shared hit=1717, temp read=800 written=800
-> Seq Scan on t1 (actual time=0.008..1.985 rows=30000 loops=1)
Buffers: shared hit=858
-> Hash (actual time

work_mem is not an "area", it is a just value.

HashSetOp is pretty poor at memory estimation and usage. It has no provision for spilling to disk. If it ends up using more memory than work_mem is set to, then it just goes ahead and uses more memory than it was supposed to. There is no external enforcement mechanism (until the kernel steps in), and in the case of HashSetOp no internal one either.

If the planner thinks ahead of time that HashSetOp would overrun work_mem, then it just doesn't get chosen, and you will probably get some sort-based method instead. But again, the estimation is poor, it will often use several times more memory than the planner thought it would, and will happily blow way past the setting of work_mem.

HashSetOp could really use some improvements, but it is so seldom used that no one is very excited about doing the work.

StackExchange Database Administrators Q#292287, answer score: 4