Stephen Frost <sfrost@snowman.net> writes:
> I've been fiddling with this on the very much larger overall database
> where this test case came from and have found that hashing the large
> table can actually be *faster* and appears to cause a more consistent
> and constant amount of disk i/o (which is good).
Interesting.
> What I'm trying to get at in this overall email is: why in the world is
> it so expensive to do hash lookups?
perf or oprofile reveal anything?
Also, I assume that the cases you are looking at are large enough that
even the "small" table doesn't fit in a single hash batch? It could
well be that the answer has to do with some bogus or at least
unintuitive behavior of the batching process, and it isn't really at all
a matter of individual hash lookups being slow.
(You never did mention what work_mem setting you're testing, anyway.)
regards, tom lane