Re: [PERFORM] encouraging index-only scans

On 2013-09-06 15:13:30 -0400, Bruce Momjian wrote:
> 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.

I wonder if we shouldn't trigger most vacuums (not analyze!) via unset
fsm bits. Perhaps combined with keeping track of RecentGlobalXmin to
make sure we're not repeatedly checking for work that cannot yet be
done.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services