Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

On 05/06/2014 08:41 AM, Simon Riggs wrote:
> On 6 May 2014 15:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>>> Lets fix e_c_s at 25% of shared_buffers and remove the parameter
>>> completely, just as we do with so many other performance parameters.
>>
>> Apparently, you don't even understand what this parameter is for.
>> Setting it smaller than shared_buffers is insane.
>
> You know you can't justify that comment and so do I. What workload is
> so badly affected as to justify use of the word insane in this
> context?

Most of them? Really?

I have to tell you, your post sounds like you've missed out on the last
12 years of PostgreSQL query tuning. Which is a little shocking
considering where you've spent that 12 years.

> I can read code. But it appears nobody apart from me actually does, or
> at least understand the behaviour that results.

So, break it down for us: explain how we'll get desirable query plans
out of the current code if:

(1) Table & Index is larger than shared_buffers;
(2) Table & Index is smaller than RAM;
(3) Selectivity is 0.02
(4) ECS is set lower than shared_buffers

I think the current cost math does a pretty good job of choosing the
correct behavior if ECS is set correctly. But if it's not, no.

If I'm wrong, then you've successfully found a bug in our costing math,
so I'd love to see it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com