Am 05.11.2012 18:09, schrieb Jeff Janes:
On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff.janes@xxxxxxxxx> 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@xxxxxxxxxxx
__________________________________________________________________________
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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
