From: Jim C. Nasby <jnasby@pervasive.com>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor performance o
Date: Wed, 22 Mar 2006 06:25:45 -0600
Message-ID: <20060322122545.GZ15742@pervasive.com> (raw)
In-Reply-To: <4420A290.2050908@modgraph-usa.com>
References: <43F38867.6010701@gpdnet.co.uk>
<19510.1140036968@sss.pgh.pa.us>
<19779.1140038874@sss.pgh.pa.us>
<43F39E53.1020009@gpdnet.co.uk>
<20781.1140046109@sss.pgh.pa.us>
<19570.1142971720@sss.pgh.pa.us>
<442080D1.5090803@modgraph-usa.com>
<6629.1142983995@sss.pgh.pa.us>
<4420A290.2050908@modgraph-usa.com>
On Tue, Mar 21, 2006 at 05:04:16PM -0800, Craig A. James wrote:
> Tom Lane wrote:
> >"Craig A. James" <cjames@modgraph-usa.com> writes:
> >>It looks to me like the problem is the use of nested loops when a hash
> >>join should be used, but I'm no expert at query planning.
> >
> >Given the sizes of the tables involved, you'd likely have to boost up
> >work_mem before the planner would consider a hash join. What nondefault
> >configuration settings do you have, anyway?
>
> shared_buffers = 20000
> work_mem = 32768
> effective_cache_size = 300000
>
> This is on a 4GB machine. Is there a guideline for work_mem that's related
> to table size? Something like, "allow 2 MB per million rows"?
No. The general guide is "set it as large as possible without making the
machine start swapping." In some cases, you'll want to bump it up much
higher for certain queries, especially if you know those queries will
only run one at a time.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, cjames@modgraph-usa.com, tgl@sss.pgh.pa.us
Subject: Re: Poor performance o
In-Reply-To: <20060322122545.GZ15742@pervasive.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