From: Rhett Garber <rhettg@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Havasvölgyi Ottó <h.otto@freemail.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why hash join instead of nested loop?
Date: Tue, 9 Aug 2005 11:51:30 -0700
Message-ID: <41b0fe89050809115174ae1669@mail.gmail.com> (raw)
In-Reply-To: <17825.1123612638@sss.pgh.pa.us>
References: <41b0fe890508051135712a086b@mail.gmail.com>
<001801c59a0a$f4be7480$9a00a8c0@OTTO>
<41b0fe8905080516164464e04f@mail.gmail.com>
<15425.1123304631@sss.pgh.pa.us>
<41b0fe89050808100021a2a4fc@mail.gmail.com>
<10930.1123549106@sss.pgh.pa.us>
<41b0fe890508090951546156f7@mail.gmail.com>
<17253.1123607579@sss.pgh.pa.us>
<41b0fe89050809111027e1d274@mail.gmail.com>
<17825.1123612638@sss.pgh.pa.us>
Well that could be an issue, is this abnormally large:
#shared_buffers = 1536 # min 16, at least max_connections*2, 8KB each
shared_buffers = 206440
#sort_mem = 131072 # min 64, size in KB
sort_mem = 524288 # min 64, size in KB
vacuum_mem = 131072 # min 1024, size in K
I actually had a lot of trouble finding example values for these... no
one wants to give real numbers in any postgres performance tuning
articles I saw. What would be appropriate for machines with 1 or 6
gigs of RAM and wanting to maximize performance.
Rhett
On 8/9/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rhett Garber <rhettg@gmail.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rhettg@gmail.com, tgl@sss.pgh.pa.us, h.otto@freemail.hu
Subject: Re: Why hash join instead of nested loop?
In-Reply-To: <41b0fe89050809115174ae1669@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox