[PERFORM] Planner Conceptual Error when Joining a Subquery -- Outer Query Condition not Pulled Into Subquery

2013-10-15 Thread Gavin Wahl
I have a pretty simple parent-child relationship, where parents are segmented into many bins (actually states). I need to query over the (parent, child) join but filter based on aggregates of the parent. That is -- all parent, child pairs for parents that are in bin x and also have more than y chil

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Merlin Moncure
On Tue, Oct 15, 2013 at 12:26 PM, Julien Cigar wrote: > On Tue, Oct 15, 2013 at 08:59:08AM -0700, Tony Kay wrote: >> On Mon, Oct 14, 2013 at 4:42 PM, Tomas Vondra wrote: >> >> > On 15.10.2013 01:00, Tony Kay wrote: >> > > Hi, >> > > >> > > I'm running 9.1.6 w/22GB shared buffers, and 32GB overall

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Tomas Vondra
On 15.10.2013 21:07, Tony Kay wrote: > > On Tue, Oct 15, 2013 at 10:26 AM, Julien Cigar > wrote: > > > for two reasons: > > - some of the overhead of bgwriter and checkpoints is more or less > linear in the size of shared_buffers, for example it could be > possible t

[PERFORM] Hot Standby performance issue

2013-10-15 Thread sparikh
Hi, This is my first post ever in the Postgres forum. I am relatively new to Postgres and coming from oracle background. We have hot stand by setup to serve mainly the read only queries. Past few days we have been facing a performance issues on one of the transaction search. The search mainly utili

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Julien Cigar
On Tue, Oct 15, 2013 at 12:07:38PM -0700, Tony Kay wrote: > On Tue, Oct 15, 2013 at 10:26 AM, Julien Cigar wrote: > > > > > for two reasons: > > > > - some of the overhead of bgwriter and checkpoints is more or less linear > > in the size of shared_buffers, for example it could be possible that a

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Tony Kay
On Tue, Oct 15, 2013 at 10:26 AM, Julien Cigar wrote: > > for two reasons: > > - some of the overhead of bgwriter and checkpoints is more or less linear > in the size of shared_buffers, for example it could be possible that a > large quantity of data could be dirty when a checkpoint occurs). > >

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Julien Cigar
On Tue, Oct 15, 2013 at 08:59:08AM -0700, Tony Kay wrote: > On Mon, Oct 14, 2013 at 4:42 PM, Tomas Vondra wrote: > > > On 15.10.2013 01:00, Tony Kay wrote: > > > Hi, > > > > > > I'm running 9.1.6 w/22GB shared buffers, and 32GB overall RAM on a > > > 16 Opteron 6276 CPU box. We limit connections

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Tony Kay
Thanks for the tip. I forgot there were kernel stats on spinlocks. I'm not sure we'll be able to get it to tip in a test environment, and we're unwilling to revert the code in production in order to have our users trigger it. We'll try triggering it on our test server, and if we manage, I'll get y

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Tony Kay
On Mon, Oct 14, 2013 at 4:42 PM, Tomas Vondra wrote: > On 15.10.2013 01:00, Tony Kay wrote: > > Hi, > > > > I'm running 9.1.6 w/22GB shared buffers, and 32GB overall RAM on a > > 16 Opteron 6276 CPU box. We limit connections to roughly 120, but > > our webapp is configured to allocate a thread-lo

Re: [PERFORM] CPU spikes and transactions

2013-10-15 Thread Merlin Moncure
On Mon, Oct 14, 2013 at 6:45 PM, Tomas Vondra wrote: > On 15.10.2013 01:26, Tony Kay wrote: >> Hi Calvin, >> >> Yes, I have sar data on all systems going back for years. >> >> Since others are going to probably want to be assured I am really >> "reading the data" right: >> >> - This is 92% user CP