On Fri, Nov 18, 2011 at 3:39 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote:
On 11/17/2011 10:44 PM, CSS wrote:There is no *simple* documentation on any part of the query planner that's also accurate. Query planning is inherently complicated.
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?
I think this point wasn't quite made clearly. PostgreSQL has no idea how much memory is in your system; it doesn't try to guess or detect it. However, when people move from one system to a larger one, they tend to increase some of the query planning parameters in the postgresql.conf to reflect the new capacity. That type of change can cause various types of query plan changes. Let's say your old system has 16GB of RAM and you set effective_cache_size to 12GB; if you upgrade to a 64GB server, it seems logical to increase that value to 48GB to keep the same proportions. But that will can you different plans, and it's possible they will be worse. There's a similar concern if you change work_mem because you have more memory, because that will alter how plans do things like sorting and hashing
But you don't have to make any changes. You can migrate to the new hardware with zero modifications to the Postgres configuration, then introduce changes later.
The whole memorys speed topic is also much more complicated than any simple explanation can cover. How many banks of RAM you can use effectively changes based on the number of CPUs and associated chipset too. Someone just sent me an explanation recently of why I was seeing some strange things on my stream-scaling benchmark program. That dove into a bunch of trivia around how the RAM is actually accessed on the motherboard. One of the reasons I keep so many samples on that program's page is to help people navigate this whole maze, and have some data points to set expectations against. See https://github.com/gregs1104/stream-scaling for the code and the samples.
--
Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Greg
On a slightly unrelated note, you had once (http://archives.postgresql.org/pgsql-general/2011-08/msg00944.php) said to limit shared_buffers max to 8 GB on Linux and leave the rest for OS caching. Does the same advice hold on FreeBSD systems too?
Amitabh
