Re: Why hash join instead of nested loop?

Rhett Garber <rhettg(at)gmail(dot)com> writes:
> They are both running SuSE 8, 2.4.21-128-smp kernel

> Compile instructions (I didn't do it myself) indicate we built from
> source with nothing fancy:

You could double-check the configure options by running pg_config.
But probably the more interesting question is whether any nondefault
CFLAGS were used, and I don't think pg_config records that.
(Hmm, maybe it should.)

In any case, there's no smoking gun there. I'm now wondering if maybe
there's something unusual about your runtime parameters. AFAIR you
didn't show us your postgresql.conf settings --- could we see any
nondefault entries there?

(I looked quickly at the 7.4 hashjoin code, and I see that it uses a
hash table sized according to sort_mem even when the input is predicted
to be very small ... so an enormous sort_mem setting would account for
some plan startup overhead to initialize the table ...)

regards, tom lane