From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: Adonias Malosso <malosso@gmail.com>
Cc: Claus Guttesen <kometen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 4s query want to run faster
Date: Thu, 21 Feb 2008 17:16:10 -0600
Message-ID: <dcc563d10802211516x65e80eb4o3d9975e626b3a760@mail.gmail.com> (raw)
In-Reply-To: <47BE023A.6000905@paradise.net.nz>
References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com>
<b41c75520802211310j40c51014y5d6fbc56bf38c467@mail.gmail.com>
<8a5d3c890802211311m5fab66d6q865fda1737e47802@mail.gmail.com>
<b41c75520802211316q35954881q26a6a6058efefcbf@mail.gmail.com>
<8a5d3c890802211323j2a7e2ebdye42f988222046e44@mail.gmail.com>
<47BE023A.6000905@paradise.net.nz>
On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood <markir@paradise.net.nz> wrote:
> The other parameter you might want to look at is effective_cache_size -
> increasing it will encourage index use. On a machine with 16GB the
> default is probably too small (there are various recommendations about
> how to set this ISTR either Scott M or Greg Smith had a page somewhere
> that covered this quite well - guys?).
>
> Obviously, decreasing random_page_cost fixed this query for you, but if
> find yourself needing to tweak it again for other queries, then look at
> changing effective_cache_size.
effective_cache_size is pretty easy to set, and it's not real
sensitive to small changes, so guesstimation is fine where it's
concerned. Basically, let your machine run for a while, then add the
cache and buffer your unix kernel has altogether (top and free will
tell you these things). If you're running other apps on the server,
make a SWAG (scientific wild assed guess) how much the other apps are
pounding on the kernel cache / buffer and set effective_cache_size to
how much you think postgresql is using of the total and set it to
that.
If your data set fits into memory, then setting random page cost
closer to 1 makes a lot of sense, and the larger effective cache size.
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: scott.marlowe@gmail.com, markir@paradise.net.nz, malosso@gmail.com, kometen@gmail.com
Subject: Re: 4s query want to run faster
In-Reply-To: <dcc563d10802211516x65e80eb4o3d9975e626b3a760@mail.gmail.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