Re: [PERFORM] Advise needed for a join query with a where conditional

Note that the inner query is fast (takes only about 0.5 second): SELECT "fb_ad_groups"."id" FROM "fb_ad_groups" WHERE "fb_ad_groups"."id" IN (SELECT "fb_ad_groups"."id" FROM "fb_ad_groups" INNER JOIN "custom_tags_fb_ad_groups" ON "fb_ad_groups"."id" = "custom_tags_fb_ad_groups"."fb_ad_group_id" IN

[PERFORM] Advise needed for a join query with a where conditional

Hi Folks, I am a newbie to this mailing list. Tried searching the forum but didn't find something similar to the problem I am facing. Background: I have a Rails app with Postgres db. For certain reports, I have to join multiple tables. However, certain join queries are dog slow and I am wonderin

Re: [PERFORM] checkpoints, proper config

On 12/10/2015 11:45 PM, Alvaro Herrera wrote: Tomas Vondra wrote: Also, I don't think it makes much sense to set (checkpoint_warning > checkpoint_timeout) as it kinda defeats the whole purpose of the warning. I agree, but actually, what is the sense of checkpoint_warning? I think it

Re: [PERFORM] checkpoints, proper config

Tomas Vondra wrote: > Also, I don't think it makes much sense to set > >(checkpoint_warning > checkpoint_timeout) > > as it kinda defeats the whole purpose of the warning. I agree, but actually, what is the sense of checkpoint_warning? I think it was useful back when we didn't have log_che

Re: [PERFORM] checkpoints, proper config

On 12/10/2015 06:20 PM, Joshua D. Drake wrote: On 12/10/2015 01:12 AM, Tory M Blue wrote: checkpoint_timeout = 5min checkpoint_completion_target = 0.9 The above is your problem. Make checkpoint_timeout = 1h . Also, considering turning synchronous_commit off. I doubt that. The report menti

Re: [PERFORM] checkpoints, proper config

On 12/10/2015 12:58 PM, Tory M Blue wrote: synchronous is commented out, is it on by default? Yes it is on by default. This is a slony slave node, so I'm not too worried about this particular host losing it's data, thus fsync is off, thanks again sir Tory -- Command Prompt, Inc. - htt

Re: [PERFORM] checkpoints, proper config

On Thu, Dec 10, 2015 at 12:00 PM, Joshua D. Drake wrote: > On 12/10/2015 10:35 AM, Tory M Blue wrote: > > >> Thiis valid regardless of the workload? >> > > Yes. > > > Seems that I would be storing a >> ton of data and writing it once an hour, so would have potential perf >> hits on the hour. I gu

[PERFORM] postgresql upgrade from 9.3 to 9.4 error

Guys I m trying to upgrade postgresql 9.3 -> 9.4 OS VERSION : CentOS release 6.3 (Final) (2.6.32-279.el6.x86_64.x86_64) When I run the upgrade script /usr/pgsql-9.4/bin/pg_upgrade It gives me this error in the log file. pg_restore: creating VIEW tables pg_restore: [archiver (db)] Error whi

Re: [PERFORM] checkpoints, proper config

On 12/10/2015 10:35 AM, Tory M Blue wrote: Thiis valid regardless of the workload? Yes. Seems that I would be storing a ton of data and writing it once an hour, so would have potential perf hits on the hour. I guess I'm not too up to date on the checkpoint configuration. No, that isn't

Re: [PERFORM] checkpoints, proper config

On Thu, Dec 10, 2015 at 9:20 AM, Joshua D. Drake wrote: > On 12/10/2015 01:12 AM, Tory M Blue wrote: > > checkpoint_timeout = 5min >> >> checkpoint_completion_target = 0.9 >> >> > The above is your problem. Make checkpoint_timeout = 1h . Also, > considering turning synchronous_commit off. > > JD

Re: [PERFORM] checkpoints, proper config

On 12/10/2015 01:12 AM, Tory M Blue wrote: checkpoint_timeout = 5min checkpoint_completion_target = 0.9 The above is your problem. Make checkpoint_timeout = 1h . Also, considering turning synchronous_commit off. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 P

[PERFORM] checkpoints, proper config

9.3.4 CentOS 256Gb system total_checkpoints | minutes_between_checkpoints ---+- 109943 | 0.0274886580556895 I've just bumped then to 150. # - Checkpoints - checkpoint_segments = 150 checkpoint_timeout = 5min checkpoint_comp