From: tim_wilson <tim.wilson@telogis.com>
To: pgsql-performance@postgresql.org
Subject: autovacuum vacuum creates bad statistics for planner when it log index scans: 0
Date: Sun, 18 May 2014 17:35:29 -0700 (PDT)
Message-ID: <1400459729388-5804416.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On a 9.3.1 server , I have a key busy_table in that is hit by most
transactions running on our system. One DB's copy of this table has 60K rows
and 1/3 of that tables rows can updated every minute.
Autovacuum autovacuum_analyze_scale_factor is set 0.02, so that analyse runs
nearly every minute. But when autovacuum vacuum runs I sometimes see the
following message in logs:
LOG: automatic vacuum of table "busy_table":* index scans: 0*
pages: 0 removed, 22152 remain
tuples: 0 removed, 196927 remain
buffer usage: 46241 hits, 478 misses, 715 dirtied
avg read rate: 0.561 MB/s, avg write rate: 0.839 MB/s
system usage: CPU 0.07s/0.06u sec elapsed 6.66 sec
and the tuples remaining is then overestimated by a factor >3 , and have
seen this over estimate as large at >20 times IE 5M
This causes the query planner to then fail to get the best plan, in fact
this can result in queries that take 30 Minutes that normally return in 4-6
seconds.
IE it starts table scanning tables in joins to busy_table rather than using
the index.
As soon as following appears:
LOG: automatic vacuum of table "busy_table": *index scans: 1*
all is well again for the queries that follow this , but for the 20-30 user
interactions during the bad period never return as they take too long.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/autovacuum-vacuum-creates-bad-statistics-for-planner-when-it...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tim.wilson@telogis.com
Subject: Re: autovacuum vacuum creates bad statistics for planner when it log index scans: 0
In-Reply-To: <1400459729388-5804416.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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