Re: [PERFORM] encouraging index-only scans

On 2013-09-06 12:30:56 -0400, Bruce Momjian wrote:
> > I am not sure I understand this though. What would be the point to go
> > and set all visible and not do the rest of the vacuuming work?
> >
> > I think triggering vacuuming by scanning the visibility map for the
> > number of unset bits and use that as another trigger is a good idea. The
> > vm should ensure we're not doing superflous work.
>
> Yes, I think it might be hard to justify a separate VM-set-only scan of
> the table. If you are already reading the table, and already checking
> to see if you can set the VM bit, I am not sure why you would not also
> remove old rows, especially since removing those rows might be necessary
> to allow setting VM bits.

Yep. Although adding the table back into the fsm will lead to it being
used for new writes again...

> Another problem I thought of is that while automatic vacuuming only
> happens with high update/delete load, index-only scans are best on
> mostly non-write tables, so we have bad behavior where the ideal case
> (static data) doesn't get vm-bits set, while update/delete has the
> vm-bits set, but then cleared as more update/deletes occur.

Well, older tables will get vacuumed due to vacuum_freeze_table_age. So
at some point they will get vacuumed and the vm bits will get set.

> The more I look at this the worse it appears. How has this gone
> unaddressed for over a year?

It's been discussed several times including during the introduction of
the feature. I am a bit surprised about the panickey tone in this
thread.
Yes, we need to overhaul the way vacuum works (to reduce the frequency
of rewriting stuff repeatedly) and the way it's triggered (priorization,
more trigger conditions) but all these are known things and "just" need
somebody with time.

Greetings,

Andres Freund

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