pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Slow query even with aggressive auto analyze 2+ messages / 2 participants [nested] [flat]
* Slow query even with aggressive auto analyze @ 2013-02-08 12:36 Karolis Pocius <kpocius@northplains.com> 0 siblings, 1 reply; 2+ messages in thread From: Karolis Pocius @ 2013-02-08 12:36 UTC (permalink / raw) To: pgsql-performance This query http://pgsql.privatepaste.com/359bed8e9e gets executed every 500 ms and normally completes really quickly http://explain.depesz.com/s/poVQ, but the more job_batches table (http://pgsql.privatepaste.com/eaf6d63fd2) gets used, the slower this query gets, to the point where it takes minutes to execute http://explain.depesz.com/s/rDJ. Analyzing job_batches table resolves the issue immediately. This table gets about a thousand new records an hour that are also updated three times as the status changes. No deletion is occurring. I've tried changing autovacuum_analyze_scale_factor as well as setting job_batches table to auto analyze every 500 changes (by setting scale factor to 0 and threshold to 500), but I still keep running into that issue, sometimes minutes after the table was analyzed. I checked pg_locks to see if anything had granted=false, but that doesn't seem to be the case. This issue is occurring on two separate instances 9.0.4 and 9.1.4 - both have nearly identical settings, just run on a different hardware. Config changes http://pgsql.privatepaste.com/8acfb9d136 Any ideas what is going wrong here? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Slow query even with aggressive auto analyze @ 2013-02-10 06:52 Amit kapila <amit.kapila@huawei.com> parent: Karolis Pocius <kpocius@northplains.com> 0 siblings, 0 replies; 2+ messages in thread From: Amit kapila @ 2013-02-10 06:52 UTC (permalink / raw) To: Karolis Pocius <kpocius@northplains.com>; pgsql-performance On Friday, February 08, 2013 6:06 PM Karolis Pocius wrote: > I've tried changing autovacuum_analyze_scale_factor as well as setting > job_batches table to auto analyze every 500 changes (by setting scale > factor to 0 and threshold to 500), but I still keep running into that > issue, sometimes minutes after the table was analyzed. > I checked pg_locks to see if anything had granted=false, but that >doesn't seem to be the case. > This issue is occurring on two separate instances 9.0.4 and 9.1.4 - both > have nearly identical settings, just run on a different hardware. > Config changes http://pgsql.privatepaste.com/8acfb9d136 > Any ideas what is going wrong here? I think you can verify in Logs whether analyze is happening as per your expectation. You can set log_autovacuum_min_duration = 0, so that auto_analyze can be logged everytime it happens. With Regards, Amit Kapila. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-02-10 06:52 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-02-08 12:36 Slow query even with aggressive auto analyze Karolis Pocius <kpocius@northplains.com> 2013-02-10 06:52 ` Amit kapila <amit.kapila@huawei.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox