Date: Fri, 6 Sep 2013 15:13:30 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Andres Freund <andres@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Peter Geoghegan <peter@2ndquadrant.com>,
 Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Message-ID: <20130906191330.GA13193@momjian.us>
References: 
 <CAEYLb_U1dTg=bXkDL=yvFTrMw5oC13ivgZA=jdQXnbgaDJ+e-Q@mail.gmail.com>
 <20130904205655.GR21874@momjian.us>
 <20130906001437.GA29264@momjian.us>
 <CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
 <20130906020043.GH27195@momjian.us>
 <20130906052236.GF6067@eldon.alvh.no-ip.org>
 <20130906130854.GB600952@alap2.anarazel.de>
 <20130906163056.GA13158@momjian.us>
 <20130906163647.GA626072@alap2.anarazel.de>
 <20130906170159.GB13158@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20130906170159.GB13158@momjian.us>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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         | timestamp with 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. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers