Re: [PERFORM] Very poor performance with Nested Loop Anti Join

På tirsdag 02. august 2016 kl. 01:15:05, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > This query performs terribly slow (~26 minutes,��1561346.597ms): Seems like the key misestimation is on the inner antijoin: >                ->  Hash Anti Join  (cost=654.21..40

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

Andreas Joseph Krogh writes: > This query performs terribly slow (~26 minutes, 1561346.597ms): Seems like the key misestimation is on the inner antijoin: >-> Hash Anti Join (cost=654.21..4008.72 rows=1 width=8) > (actual time=9.016..40.672 rows=76174 loops=1) >

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

På mandag 01. august 2016 kl. 15:33:04, skrev Andreas Joseph Krogh < andr...@visena.com >: I have this schema:   CREATE TABLE onp_crm_person( id serial PRIMARY KEY, onp_user_id bigint referencesonp_user(id) deferrable initially deferred, is_resource boolean not null def

Re: [PERFORM] Very poor performance

On Tue, Aug 17, 2010 at 7:54 PM, Aaron Burnett wrote: > Yeah, missed a '.', it's 8.2.5 Centos 5.5 has postgresql 8.4.4 available from the main repository. You might consider an upgrade. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscri

Re: [PERFORM] Very poor performance

Mark Kirkwood wrote: > I'm guessing you meant to suggest setting effective_cache_size > to 15GB (not 15MB) Yes. Sorry about that. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mail

Re: [PERFORM] Very poor performance

On 18/08/10 06:19, Kevin Grittner wrote: Since you haven't set effective_cache_size, you're discouraging some types of plans which might be worth considering. This should normally be set to the sum of your shared_buffers setting and whatever is cached by the OS; try setting effective_cache_siz

Re: [PERFORM] Very poor performance

Aaron Burnett wrote: >>> 16 Gig RAM >>> 192MB work_mem (increasing to 400MB didn't change the outcome) >> >> What other non-default settings do you have? > > maintenance_work_mem = 1024MB > max_stack_depth = 8MB > max_fsm_pages = 800 > max_fsm_relations = 2000 Since you haven't set e

Re: [PERFORM] Very poor performance

Thanks for the response kevin. Answers interspersed below. On 8/17/10 10:18 AM, "Kevin Grittner" wrote: > "Aaron Burnett" wrote: > >> Postgres Version 8.25 > > Do you mean 8.2.5? (There is no PostgreSQL version 8.25.) > Yeah, missed a '.', it's 8.2.5 > If you're concerned about perfo

Re: [PERFORM] Very poor performance

So, building the partial index will avoid the table lookup. Currently answerselectindex only has single-column indexes on memberid and answerid, so any query with a predicate on both columns is gonna be forced to do an index lookup on one column followed by a table lookup to get the other one (w

Re: [PERFORM] Very poor performance

"Kevin Grittner" writes: > By the way, 8.0 is going out of support as soon as the 9.0 release > comes out; likely next month. Small clarification on that: the plan is that there will be exactly one more minor update of 8.0 (and 7.4). So it'll go out of support after the next set of back-branch u

Re: [PERFORM] Very poor performance

"Kevin Grittner" wrote: > "Aaron Burnett" wrote: >> Postgres Version 8.25 > > Do you mean 8.2.5? (There is no PostgreSQL version 8.25.) I just noticed that there's an 8.0.25 -- if that's what you're running, it's a bit silly trying to optimize individual slow queries -- performance has im

Re: [PERFORM] Very poor performance

"Aaron Burnett" wrote: > Postgres Version 8.25 Do you mean 8.2.5? (There is no PostgreSQL version 8.25.) If you're concerned about performance and you're still on 8.2, you might want to consider updating to a new major version. > 16 Gig RAM > 192MB work_mem (increasing to 400MB didn't ch

Re: [PERFORM] Very poor performance

Thanks Mark, Yeah, I apologize, I forgot to mention a couple of things. m.id is the primary key but the biggest problem is that the query loops 626410 times because at one time people were allowed to delete member.id rows which now will break the application if the a.memberid comes out and it

Re: [PERFORM] Very poor performance

This is weird - is there a particular combination of memberid/answered in answerselectindex that has a very high rowcount? First change I would suggest looking into would be to try changing sub-query logic to check existence and limit the result set of the sub-query to a single row Select dist

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

On Tue, 29 Apr 2008, John Rouillard wrote: So swap the memory usage from the OS cache to the postgresql process. Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a better setting. So I'll try 30 to start (1/8 of memory) and see what it does to the other processes on the box.

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

On Mon, Apr 28, 2008 at 02:16:02PM -0400, Greg Smith wrote: > On Mon, 28 Apr 2008, John Rouillard wrote: > > > 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds > > apart) > > so I changed: > > checkpoint_segments = 30 > > checkpoint_warning = 150 > > That's good, but you

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

On Tue, Apr 29, 2008 at 05:19:59AM +0930, Shane Ambler wrote: > John Rouillard wrote: > > >We can't do this as we are backfilling a couple of months of data > >into tables with existing data. > > Is this a one off data loading of historic data or an ongoing thing? Yes it's a one off bulk data l

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

John Rouillard wrote: We can't do this as we are backfilling a couple of months of data into tables with existing data. Is this a one off data loading of historic data or an ongoing thing? The only indexes we have to drop are the ones on the primary keys (there is one non-primary key index

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

On Mon, 28 Apr 2008, John Rouillard wrote: 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds apart) so I changed: checkpoint_segments = 30 checkpoint_warning = 150 That's good, but you might go higher than 30 for a bulk loading operation like this, particularly on 8.1

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

On Mon, Apr 28, 2008 at 06:53:09PM +0100, Heikki Linnakangas wrote: > John Rouillard wrote: > >We are running postgresql-8.1.3 under Centos 4 > You should upgrade, at least to the latest minor release of the 8.1 > series (8.1.11), as there has been a bunch of important bug and security > fixes. O

Re: [PERFORM] Very poor performance loading 100M of sql data using copy

John Rouillard wrote: We are running postgresql-8.1.3 under Centos 4 You should upgrade, at least to the latest minor release of the 8.1 series (8.1.11), as there has been a bunch of important bug and security fixes. Or even better, upgrade to 8.3, which has reduced the storage size of espec