Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 4CBFA97D956 for ; Fri, 18 Nov 2011 12:31:18 -0400 (AST) Received: from [89.187.132.100] (helo=elizabeth.gransy.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RRRLN-0003VY-Nb for pgsql-performance@postgresql.org; Fri, 18 Nov 2011 16:31:18 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 498E615E4136; Fri, 18 Nov 2011 17:30:40 +0100 (CET) Received: from 85.160.32.227 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Fri, 18 Nov 2011 17:30:40 +0100 Message-ID: <82b3dbde9ae3559f7e91e0fec379b309.squirrel@sq.gransy.com> In-Reply-To: References: <0464974E-CF82-41D7-A266-5BAE3CBCEC51@morefoo.com> <4E9811DE.6030601@tweakers.net> <4EC62ED0.7040408@2ndQuadrant.com> Date: Fri, 18 Nov 2011 17:30:40 +0100 Subject: Re: SSD options, small database, ZFS From: "Tomas Vondra" To: "Scott Marlowe" Cc: "Greg Smith" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Host-Lookup-Failed: Reverse DNS lookup failed for 89.187.132.100 (deferred) X-Archive-Number: 201111/242 X-Sequence-Number: 45753 On 18 Listopad 2011, 17:17, Scott Marlowe wrote: > On Fri, Nov 18, 2011 at 3:09 AM, Greg Smith wrot= e: >> On 11/17/2011 10:44 PM, CSS wrote: >>> >>> Is there any sort of simple documentation on the query planner that >>> might >>> cover how things like increased RAM could impact how a query is >>> executed? >> >> There is no *simple* documentation on any part of the query planner >> that's >> also accurate. =C2=A0Query planning is inherently complicated. >> >> I think this point wasn't quite made clearly. =C2=A0PostgreSQL has no = idea >> how >> much memory is in your system; it doesn't try to guess or detect it. > > effective_cache_size tells the db how much memory you have. Since you > have to set it, it can be anything you want, but if you've set it to > something much higher on the new machine then it can affect query > planning. That's only half of the truth. effective_cache_size is used to estimate the page cache hit ratio, nothing else. It influences the planning a bit (AFAIK it's used only to estimate a nested loop with inner index scan) bu= t it has no impact on things like work_mem, maintenance_work_mem, wal_buffers etc. People often bump these settings up (especially work_mem) on new hw without properly testing the impact. PostgreSQL will happily do that because it was commanded to, but then the system starts swapping or the OOM killer starts shooting the processes. Tomas