DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Bkc/M3YoCnjghDEkWifAhSya5hVGOHITTWmasJCoHlaNHUmlsLXSdcmLMb+mYCLcA5SqiTMdDZ6Prt3Y96487+sID5S5zn0N4oLjuWoi1RxYOjiFX1hbOYDy2GdOpNXw+4/aR/8PhnLsoE41g3pxkh2YoxLD4BZ+BXizP29goiE=
Message-ID: <41b0fe8905080912113f381f84@mail.gmail.com>
Date: Tue, 9 Aug 2005 12:11:52 -0700
From: Rhett Garber <rhettg@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Why hash join instead of nested loop?
Cc: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>,
	pgsql-performance@postgresql.org
In-Reply-To: <18143.1123614059@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <41b0fe890508051135712a086b@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>
	<41b0fe89050809115174ae1669@mail.gmail.com>
	<18143.1123614059@sss.pgh.pa.us>

Bingo, the smaller the sort_mem, the faster that query is.

Thanks a lot to everybody that helped, i'll tweak with these values
more when I get a chance now that I have some guidelines that make
sense.

Rhett

On 8/9/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rhett Garber <rhettg@gmail.com> writes:
> > Well that could be an issue, is this abnormally large:
> > #shared_buffers =3D 1536          # min 16, at least max_connections*2,=
 8KB each
> > shared_buffers =3D 206440
> > #sort_mem =3D 131072              # min 64, size in KB
> > sort_mem =3D 524288               # min 64, size in KB
> > vacuum_mem =3D 131072             # min 1024, size in K
>=20
> The vacuum_mem number is OK I think, but both of the others seem
> unreasonably large.  Conventional wisdom about shared_buffers is that
> the sweet spot is maybe 10000 or so buffers, rarely more than 50000.
> (Particularly in pre-8.0 releases, there are code paths that grovel
> through all the buffers linearly, so there is a significant cost to
> making it too large.)  Don't worry about it being too small to make
> effective use of RAM --- we rely on the kernel's disk cache to do that.
>=20
> sort_mem is *per sort*, and so half a gig in a machine with only a
> couple of gig is far too much except when you know you have only one
> query running.  A couple dozen backends each trying to use half a gig
> will drive you into the ground in no time.  Conventional wisdom here
> is that the global setting should be conservatively small (perhaps
> 10Mb to 100Mb depending on how many concurrent backends you expect to
> have), and then you can explicitly increase it locally with SET for
> specific queries that need it.
>=20
> In terms of the problem at hand, try the test case with a few different
> values of sort_mem (use SET to adjust it, you don't need to touch the
> config file) and see what happens.  I think the cost you're seeing is
> just startup overhead to zero a hash table of a few hundred meg ...
>=20
>                         regards, tom lane
>