From: Craig A. James <cjames@modgraph-usa.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor performance o
Date: Tue, 21 Mar 2006 17:04:16 -0800
Message-ID: <4420A290.2050908@modgraph-usa.com> (raw)
In-Reply-To: <6629.1142983995@sss.pgh.pa.us>
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>
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"?
I'm also curious why the big difference between my "Query #1" and "Query #2". Even though it does a nested loop, #2's outer loop only returns one result from a very tiny table, so shouldn't it be virtually indistinguishable from #1?
Thanks,
Craig
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: cjames@modgraph-usa.com, tgl@sss.pgh.pa.us
Subject: Re: Poor performance o
In-Reply-To: <4420A290.2050908@modgraph-usa.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