From: tim_wilson <tim.wilson@telogis.com>
To: pgsql-performance@postgresql.org
Subject: Re: autovacuum vacuum creates bad statistics for planner when it log index scans: 0
Date: Fri, 6 Jun 2014 04:22:32 -0700 (PDT)
Message-ID: <1402053752755-5806320.post@n5.nabble.com> (raw)
In-Reply-To: <1402037012706-5806302.post@n5.nabble.com>
References: <1400459729388-5804416.post@n5.nabble.com>
<11299.1400559202@sss.pgh.pa.us>
<1402016119875-5806283.post@n5.nabble.com>
<1402037012706-5806302.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Yes I can create a simpler version that exhibits the problem:
postgres_bug_simpler.sql
<http://postgresql.1045698.n5.nabble.com/file/n5806320/postgres_bug_simpler.sql;
This only now involves one smaller table 60K rows, and a linked table with
20M rows. I tried with 6K and 1M but could not get problem to occur. Both
are now unchanging in size. The smaller table gets updated frequently, and
then starts exhibiting the bad query plan, it seems especially after the 2nd
auto vacuum and auto analyze. When the dead_rows goes to zero in the stats
the live_tup can stay at an huge factor larger than the table really is for
some time.
In my system the smaller table that is updated frequently grows only
slightly if at all. I never want a table scan to happen of the big table,
but even with enable_seq_scan=false set in functions that query these tables
I can get the bad query plan.
Would it be possible to have a setting on a table that gave an expression
for determining the table size? IE For key highly updated tables I could set
and maintain the meta-data for the size table and even shape of the data.
Then for these tables autovac would not need to make the effort of having to
estimate size.
regards
Tim
--
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: <1402053752755-5806320.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