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

[PERFORM] Very poor performance with Nested Loop Anti Join

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 default false, UNIQUE(onp_user_id) ); CREATE TABLE onp_crm_activity_log( id bigserial PRIMARY KEY, relation_id integer RE

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

@postgresql.org> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Aaron Burnett Sent: Monday, August 16, 2010 6:07 PM To: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org> Subject: [PERFORM] Very poor performance Hi, I'm hoping someone can offer som

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

rsselectindex(memberid) where answerid between 127443 and 127448" Mr From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Aaron Burnett Sent: Monday, August 16, 2010 6:07 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Very poor

Re: [PERFORM] Very poor performance

esql.org] On Behalf Of Aaron Burnett Sent: Monday, August 16, 2010 6:07 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Very poor performance Hi, I'm hoping someone can offer some help here. The query and explain analyze and table layout are below and attached in a text file if the

[PERFORM] Very poor performance

Hi, I'm hoping someone can offer some help here. The query and explain analyze and table layout are below and attached in a text file if the formatting is bad. The query is part of a bigger query that our front end runs. This is the part that takes forever (84 minutes in this case) to finish

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

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

Hi all: We are loading in a number (100+) of sql files that are about 100M in size. It takes about three hours to load the file. There is very little load on the database other than the copy from operations. We are running postgresql-8.1.3 under Centos 4 on a RAID 1/0 array with 4 disks (so we h