[PERFORM] Delete performance on delete from table with inherited tables

2004-03-09 Thread Chris Kratz
. The table structure has worked quite well up till now and we are hoping to not have to drop our foreign keys and inheritance if possible. Any ideas? Thanks for your time, -Chris -- Chris Kratz Systems Analyst/Programmer VistaShare LLC ---(end of broadcast

Re: [PERFORM] Delete performance on delete from table with inherited tables

2004-04-05 Thread Chris Kratz
in the triggers. Time spent in triggers is not shown in the pg 7.3.4 version of explain (nor would I necessarily expect it to). Thanks for your time, expertise and responses. -Chris On Tuesday 09 March 2004 7:18 pm, Stephan Szabo wrote: On Wed, 3 Mar 2004, Chris Kratz wrote: Which certainly points

[PERFORM] Long running queries degrade performance

2004-04-16 Thread Chris Kratz
, or are there other things we should be looking at hardware wise. Thank you for your time. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Long running queries degrade performance

2004-04-16 Thread Chris Kratz
development system and on the production server. The same query a while later might respond quickly again. I'm not sure where to look for the delay, either, and it is intermittent enough that I'm not even sure what monitoring techniques to use. -- Mike Nolan -- Chris Kratz Systems Analyst

Re: [PERFORM] Long running queries degrade performance

2004-04-16 Thread Chris Kratz
that. Might be interesting to do something like that in a few key places where we have problems. -- Mike Nolan -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 9: the planner will ignore your

Re: [PERFORM] Long running queries degrade performance

2004-04-16 Thread Chris Kratz
On Friday 16 April 2004 5:12 pm, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: ... Or if worse comes to worse to actually kill long running processes without taking down the whole db as we have had to do on occasion. A quick kill -INT suffices to issue a query cancel, which I

[PERFORM] Performance Tuning

2005-02-09 Thread Chris Kratz
Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we really need more powerfull processor(s) to gain more performance from

Re: [PERFORM] Performance Tuning

2005-02-09 Thread Chris Kratz
the explain again goes back to almost 5s. Now I wonder why that would be different. Changing random cpu cost back to 2 nets little difference (4991.940ms for explain and 496ms) But we will leave it at that for now. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com

Re: [PERFORM] Performance Tuning

2005-02-09 Thread Chris Kratz
On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote: Chris Kratz [EMAIL PROTECTED] writes: We continue to tune our individual queries where we can, but it seems we still are waiting on the db a lot in our app. When we run most queries, top shows the postmaster running at 90

Re: [PERFORM] Performance Tuning

2005-02-09 Thread Chris Kratz
On Wednesday 09 February 2005 05:08 pm, Merlin Moncure wrote: Hello All, In contrast to what we hear from most others on this list, we find our database servers are mostly CPU bound. We are wondering if this is because we have postgres configured incorrectly in some way, or if we

Re: [PERFORM] Large time difference between explain analyze and normal run

2005-02-10 Thread Chris Kratz
On Thursday 10 February 2005 01:58 pm, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: Does anyone have any idea why there be over a 4s difference between running the statement directly and using explain analyze? Aggregate (cost=9848.12..9848.12 rows=1 width=0) (actual time

[PERFORM] Help with performance on current status column

2005-09-13 Thread Chris Kratz
ELSE 'Active'::text END = 'Active'::text) Total runtime: 884.456 ms (3 rows) -- Chris Kratz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [PERFORM] Help with performance on current status column

2005-09-14 Thread Chris Kratz
of the awkwardness in the calculation. That calc gets used for a lot of different things including column definitions when people want to see the column on screen. Thanks, -Chris On Wednesday 14 September 2005 05:13 am, Richard Huxton wrote: Chris Kratz wrote: Hello All, We are struggling

[PERFORM] Incorrect estimates on columns

2007-10-17 Thread Chris Kratz
Hello Everyone, I'm struggling to get postgres to run a particular query quickly. It seems that very early on, the planner seems to mis-estimate the number of rows returned by a join which causes it to assume that there is only 1 row as it goes up the tree. It then picks a nested loop join

Re: [PERFORM] Incorrect estimates on columns

2007-10-17 Thread Chris Kratz
On Wednesday 17 October 2007 14:49, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: I'm struggling to get postgres to run a particular query quickly. The key problem seems to be the join size misestimate here: - Hash Join (cost=45.92..1251.07 rows=21 width=8

Re: [PERFORM] Incorrect estimates on columns

2007-10-18 Thread Chris Kratz
On Wednesday 17 October 2007 20:23, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: On Wednesday 17 October 2007 14:49, Tom Lane wrote: Evidently it's not realizing that every row of par will have a join partner, but why not? I suppose a.activityid is unique, and in most cases

[PERFORM] mis-estimate in nested query causes slow runtimes

2008-02-11 Thread Chris Kratz
Hello, I've been wrestling w/ a complex query for another developer for awhile today. The problem consistently seems to a mis-estimation of the number of rows resulting from a join. This causes the query early on to think it's only going to be processing 1 row and so it chooses nested

Re: [PERFORM] mis-estimate in nested query causes slow runtimes

2008-02-12 Thread Chris Kratz
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote: Chris Kratz [EMAIL PROTECTED] writes: - Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual time=2.932..27.772 rows=20153 loops=1) - Hash Join (cost=10.89..22.58 rows=1 width=24) (actual time=0.065..0.134 rows=1 loops=1

Re: [PERFORM] mis-estimate in nested query causes slow runtimes

2008-02-12 Thread Chris Kratz
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote: Chris Kratz [EMAIL PROTECTED] writes: - Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual time=2.932..27.772 rows=20153 loops=1) - Hash Join (cost=10.89..22.58 rows=1 width=24) (actual time=0.065..0.134 rows=1 loops=1

Re: [PERFORM] mis-estimate in nested query causes slow runtimes

2008-02-18 Thread Chris Kratz
On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote: Chris Kratz [EMAIL PROTECTED] writes: The first frustration is that I can't get the transaction details scan to get any more accurate. It thinks it will find 1407 records, instead it finds 20,153. Then for whatever reason it thinks

Re: [PERFORM] mis-estimate in nested query causes slow runtimes

2008-02-20 Thread Chris Kratz
On 2/18/08, Chris Kratz [EMAIL PROTECTED] wrote: On 2/11/08, Tom Lane [EMAIL PROTECTED] wrote: Chris Kratz [EMAIL PROTECTED] writes: The first frustration is that I can't get the transaction details scan to get any more accurate. It thinks it will find 1407 records, instead

[PERFORM] Ramifications of turning off Nested Loops for slow queries

2008-03-04 Thread Chris Kratz
Hello Everyone, I had posted an issue previously that we've been unable to resolve. An early mis-estimation in one or more subqueries causes the remainder of the query to choose nested loops instead of a more efficient method and runs very slowly (CPU Bound). I don't think there is any

Re: [PERFORM] Ramifications of turning off Nested Loops for slow queries

2008-03-04 Thread Chris Kratz
On 3/4/08, Kevin Grittner [EMAIL PROTECTED] wrote: On Tue, Mar 4, 2008 at 8:42 AM, in message Any other thoughts or suggestions? Make sure your effective_cache_size is properly configured. Increase random_page_cost and/or decrease seq_page_cost. You can play with the cost settings on a

Re: [PERFORM] Ramifications of turning off Nested Loops for slow queries

2008-03-04 Thread Chris Kratz
On 3/4/08, Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: On Tue, Mar 4, 2008 at 8:42 AM, in message [EMAIL PROTECTED], Chris Kratz [EMAIL PROTECTED] wrote: So, I've now been asked to ping the list as to whether turning off nested loops system wide is a bad

[PERFORM] Planner mis-estimation using nested loops followup

2008-03-18 Thread Chris Kratz
A number of weeks ago, I had posted a request for help regarding join estimates in pg 8.2.6. In moderately complex to very complex ad hoc queries in our system, we were consistently having the system massively underestimate the number of rows coming out of join at a low level making these queries

Re: [PERFORM] Planner mis-estimation using nested loops followup

2008-03-18 Thread Chris Kratz
/18/08, Joshua D. Drake [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Mar 2008 11:35:08 -0400 Chris Kratz [EMAIL PROTECTED] wrote: Nondefault settings of interest from postgresql.conf shared_buffers = 1024MB # min 128kB

[PERFORM] Incorrect estimates on correlated filters

2008-08-12 Thread Chris Kratz
Hello All, Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated. So, the estimates come out grossly incorrect (the planner has no way to

Re: [PERFORM] Incorrect estimates on correlated filters

2008-08-13 Thread Chris Kratz
On Wed, Aug 13, 2008 at 10:59 AM, Decibel! [EMAIL PROTECTED] wrote: On Aug 12, 2008, at 4:59 PM, Chris Kratz wrote: Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns

Re: [PERFORM] Databases vs Schemas

2009-10-10 Thread Chris Kratz
On Fri, Oct 9, 2009 at 11:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Carey sc...@richrelevance.com writes: I've got 200,000 tables in one db (8.4), and some tools barely work. The system catalogs get inefficient when large and psql especially has trouble. Tab completion takes