Message-ID: <5099602A.8020103@pro-open.de>
Date: Tue, 06 Nov 2012 20:08:26 +0100
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>
CC: Claudio Freire <klaussfreire@gmail.com>, Petr Praus <petr@praus.net>,
	Marcos Ortiz <mlortiz@uci.cu>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres
	9.2 significantly slows down queries
References: 
 <CACezXZ_w7HbqSxZ=5SJH=kxb4nBDNbpDejttSaU6EC1AeO4PYg@mail.gmail.com>
	<CACezXZ_UYbzvNEP_V6-bsyzGij-kJdeNz04-o=kbMXmcLMHnqw@mail.gmail.com>
	<5092DE42.5060206@uci.cu> <509304DC.1090809@pro-open.de>
	<CACezXZ-p1UK0K4EJH8b+EBuiD5HY0vNaQvxrnMcjwvjf9cb+oQ@mail.gmail.com>
	<5094F280.6010606@pro-open.de>
	<CACezXZ9SV58CbFM3-MpKGm1z-J0Ho1zERMUBuLH6QhC7RJ5T8Q@mail.gmail.com>
	<50954FAF.3060905@pro-open.de>
	<CAMkU=1wkUvrJGSj7kJq3CqoSBmKr7qtAMsqEPEBCnggwv4Yn2A@mail.gmail.com>
	<CAGTBQpYGwEu__NzmrHXG865CL7thGESDaLp1D6SA6HHYT4r5yQ@mail.gmail.com>
	<CAMkU=1w+oWOvV51pRhikzUL99+WN5BJ234jDYJ1Ut5c5wK4RZQ@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1w+oWOvV51pRhikzUL99+WN5BJ234jDYJ1Ut5c5wK4RZQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Am 05.11.2012 18:09, schrieb Jeff Janes:
> On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
>> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>>> normally ;-)
>>> In my experience when doing sorts in isolation, having more work_mem
>>> is a bad thing, unless it enables you to remove a layer of
>>> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.
>> Blame it on quicksort, which is quite cache-unfriendly.
> The observation applies to heap sort.  If you can't set work_mem large
> enough to do the sort in memory, then you want to set it just barely
> large enough to avoid two layers of tape sorting.  Any larger than
> that reduces performance rather than increasing it.  Of course that
> assumes you have the luxury of knowing ahead of time exactly how large
> your sort will be and can set work_mem accordingly on a case by case
> basis, which is unlikely in the real world.
>
>> Perhaps PG should consider using in-memory mergesort for the bigger chunks.
I don't want to be the party pooper here, but when you have another look 
at the EXPLAINs, you'll realize that there's not a single sort involved. 
The expensive parts are HASH, HASH JOIN and HASH RIGHT JOIN (although 
the SeqScan takes longer as well, for whatever reason). In those parts, 
the difference is clearly in the # of buckets and batches. So to a 
degree, PG even does tell us that it uses a different code path (sorry, 
PG ;-)...

Greg Smith mentions an optimization wrt. Hash Joins that can become a 
pitfall. His advise is to increase the statistic targets on the hashed 
outer relation. Might be worth a try.

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne