Date: Thu, 5 Jun 2014 23:43:32 -0700 (PDT)
From: tim_wilson <tim.wilson@telogis.com>
To: pgsql-performance@postgresql.org
Message-ID: <1402037012706-5806302.post@n5.nabble.com>
In-Reply-To: <1402016119875-5806283.post@n5.nabble.com>
References: <1400459729388-5804416.post@n5.nabble.com>
 <11299.1400559202@sss.pgh.pa.us> <1402016119875-5806283.post@n5.nabble.com>
Subject: Re: autovacuum vacuum creates bad statistics for planner when it
 log index scans: 0
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

I have now created a repeatable test for this ...bug, well that may be
debatable, but getting the query plan this wrong after vacum and analyze
have run certainly looks like a bug to me.

I have created a test case that matches my problem domain but can probably
be simplified.
postgres_bug.sql
<http://postgresql.1045698.n5.nabble.com/file/n5806302/postgres_bug.sql>  

Even after autovac vacuum and autovac analyze have run the query plan goes
from using indexes on the big table to table scanning them as it thinks my
unit_test table is large due to the error in the estimate for rows in the
table that autovac generated. Once you run a cluster on the table all goes
back to using the correct indexes. And the next autovacuum gets things
straight again on the stats table.

Look forward to solution as this is hurting us. A very hot table on our
system goes bad every night and needs constant watching.

regards
Tim


--
View this message in context: http://postgresql.1045698.n5.nabble.com/autovacuum-vacuum-creates-bad-statistics-for-planner-when-it-log-index-scans-0-tp5804416p5806302.html
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