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=12.455..12.455 rows=30000 loops=1) Buckets: 32768 Batches: 2 Memory Usage: 3679kB Buffers: shared hit=858, temp written=400 -> Seq Scan on t2 (actual time=0.003..2.125 rows=30000 loops=1) Buffers: shared hit=858 Planning: Buffers: shared hit=33 Planning Time: 0.484 ms Execution Time: 35.457 ms
My question is :
- I know that both the hash join and HashSetOp Except above use a hash table.
While the hash table in the first query overflowed work_mem, it seems that HashSetOp Expert operation didn’t overflow work_mem (I can’t see any indication that it used temp files in the execution plan.)
Do they use different memory areas?
- If both the hash join and the HashSetOp Except use the same memory area, how does the HashSetOp Except operation consume smaller area?
I would appreciate it if anyone could give me a clue or answer.
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.