Re: [PERFORM] encouraging index-only scans

On Tue, Feb 11, 2014 at 05:51:36PM -0200, Claudio Freire wrote:
> > We track relpages and relallvisible, which seems like a more direct measure.
> > Once analyze is done (which is already triggered by inserts) and sets those,
> > it could fire a vacuum based on the ratio of those values, or the autovac
> > process could just look at the ratio after naptime. So just introduce
> > autovacuum_vacuum_visible_factor. A problem there is that it would be a lot
> > of work to aggressively keep the ratio high, and pointless if the types of
> > queries done on that table don't benefit from IOS anyway, or if pages are
> > dirtied so rapidly that no amount of vacuuming will keep the ratio high.
> > Would we try to automatically tell which tables were which, or rely on the
> > DBA setting per-table autovacuum_vacuum_visible_factor for tables that
> > differ from the database norm?
>
>
> Why not track how many times an IOS would be used but wasn't, or how
> many heap fetches in IOS have to be performed?
>
> Seems like a more direct measure of whether allvisible needs an update.

Now that is in interesting idea, and more direct.

Do we need to adjust for the insert count, i.e. would the threadhold to
trigger an autovacuum after finding index lookups that had to check the
heap page for visibility be higher if many inserts are happening,
perhaps dirtying pages? (If we are dirtying via update/delete,
autovacuum will already trigger.)

We are aggressive in clearing the page-all-visible flag (we have to be),
but I think we need a little more aggressiveness for setting it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +