Re: [PERFORM] encouraging index-only scans

On Fri, Sep  6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
> This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
> manual VACUUM was required for index-only scans.  That thread ended with
> us realizing that pg_upgrade's ANALYZE runs will populate
> pg_class.relallvisible.
> 
> What I didn't see in that thread is an analysis of what cases are going
> to require manual vacuum, and I have seen no work in 9.3 to improve
> that.  I don't even see it on the TODO list.

OK, let's start the discussion then.  I have added a TODO list:
Improve setting of visibility map bits for read-only and insert-only workloads

So, what should trigger an auto-vacuum vacuum for these workloads? 
Rather than activity, which is what normally drives autovacuum, it is
lack of activity that should drive it, combined with a high VM cleared
bit percentage.

It seems we can use these statistics values:
 n_tup_ins           | bigint                    n_tup_upd           | bigint                    n_tup_del           |
bigint                   n_tup_hot_upd       | bigint                    n_live_tup          | bigint
n_dead_tup          | bigint                    n_mod_since_analyze | bigint                    last_vacuum         |
timestampwith time zone  last_autovacuum     | timestamp with time zone 
 

Particilarly last_vacuum and last_autovacuum can tell us the last time
of vacuum.  If the n_tup_upd/n_tup_del counts are low, and the  VM set
bit count is low, it might need vacuuming, though inserts into existing
pages would complicate that.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +