Thanks. We are on Greenplum GP 4.2 (Postgres 8.2). As per GP suggestions, we have 6 primary/6 mirror instances on each server. The server has 64 G RAM, and shared_buffers is at ...125 MB :-). I suppose the idea is for the OS buffer cache to do the legwork.
But still...performance is at least "not bad". If all HASH JOIN queries touch shared_buffers in some way, I find it non-intuitive that we can have concurrent hash queries involving big tables (100M+ joined with say 100K), all apparently using the 125MB shared_buffers in some way, and yet giving reasonable performance.
Basically what is the anatomy of a hash join involving large tables? Disk->Shared_Buffers->Hash Join areas? something like that?