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.