Re: Why hash join instead of nested loop?

From: Tom Lane
Subject: Re: Why hash join instead of nested loop?
Date: ,
Msg-id: 17825.1123612638@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Why hash join instead of nested loop?  (Rhett Garber)
Responses: Re: Why hash join instead of nested loop?  (Rhett Garber)
List: pgsql-performance

Tree view

Why hash join instead of nested loop?  (Rhett Garber, )
 Re: Why hash join instead of nested loop?  (Havasvölgyi Ottó, )
  Re: Why hash join instead of nested loop?  (Rhett Garber, )
   Re: Why hash join instead of nested loop?  (Tom Lane, )
    Re: Why hash join instead of nested loop?  (Rhett Garber, )
     Re: Why hash join instead of nested loop?  (Tom Lane, )
      Re: Why hash join instead of nested loop?  ("Steinar H. Gunderson", )
      Re: Why hash join instead of nested loop?  (Michael Fuhr, )
      Re: Why hash join instead of nested loop?  (Ian Westmacott, )
       Re: Why hash join instead of nested loop?  (Tom Lane, )
        Re: Why hash join instead of nested loop?  (Ian Westmacott, )
      Re: Why hash join instead of nested loop?  (Rhett Garber, )
       Re: Why hash join instead of nested loop?  (Tom Lane, )
        Re: Why hash join instead of nested loop?  (Rhett Garber, )
         Re: Why hash join instead of nested loop?  (Tom Lane, )
          Re: Why hash join instead of nested loop?  (Rhett Garber, )
           Re: Why hash join instead of nested loop?  (Tom Lane, )
            Re: Why hash join instead of nested loop?  (Rhett Garber, )

Rhett Garber <> 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


pgsql-performance by date:

From: John A Meinel
Date:
Subject: Re: Table locking problems?
From: Tobias Brox
Date:
Subject: partial index regarded more expensive