On 6 May 2014 18:08, Josh Berkus <j...@agliodbs.com> wrote: > On 05/06/2014 08:41 AM, Simon Riggs wrote: >> On 6 May 2014 15:18, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> Simon Riggs <si...@2ndquadrant.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 didn't use the word "most" anywhere. So not really clear what you are saying. > 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 read the code, think what to say and then say what I think, not rely on dogma. I tried to help years ago by changing the docs on e_c_s, but that's been mostly ignored down the years, as it is again here. >> 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 Is that it? The above use case is the basis for a default setting?? It's a circular argument, since you're assuming we've all followed your advice of setting shared_buffers to 25% of RAM, which then presumes a large gap between (1) and (2). It also ignores that if ECS is set low then it increases the cost, but does not actually preclude index scans larger than that setting. It also ignores that if your database fits in RAM, your random_page_cost setting is wrong and lowering that appropriately will increase the incidence of index scans again. You should also include (5) You're only running one query at a time (which you know, how?) (6) You don't care if you flush your cache for later queries (7) You've got big tables yet are not partitioning them effectively > 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. Setting it high generates lovely EXPLAINs for a single query, but do we have any evidence that whole workloads are better off with higher settings? And that represents the general case? And it makes sense even if it makes it bigger than actual RAM?? If you assume that you can use all of that memory, you're badly wrong. Presumably you also set work_mem larger than shared_buffers, since that will induce exactly the same behaviour and have the same downsides. (Large memory usage for single query, but causes cache churn, plus problems if we try to overuse RAM because of concurrent usage). In the absence of performance measurements that show the genuine effect on workloads, I am attempting to make a principle-based argument. I suggested 25% of shared_buffers because we already use that as the point where other features cut in to minimise cache churn. I'm making the argument that if *that* setting is the right one to control cache churn, then why is it acceptable for index scans to churn up even bigger chunks of cache? In case it wasn't clear, I am only suggesting 25% of shared_buffers for large settings, not for micro-configurations. My proposal to remove the setting completely was a rhetorical question, asking why we have a setting for this parameter and yet no tunables for other things. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers