Re: [PERFORM] encouraging index-only scans

On Fri, Sep 20, 2013 at 11:51 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2013-09-20 11:30:26 -0400, Robert Haas wrote:
>> On Thu, Sep 19, 2013 at 6:59 PM, Andres Freund <andres@2ndquadrant.com> wrote:
>> > The reason I suggested keeping track of the xids of unremovable tuples
>> > is that the current logic doesn't handle that at all. We just
>> > unconditionally set n_dead_tuples to zero after a vacuum even if not a
>> > single row could actually be cleaned out. Which has the effect that we
>> > will not start a vacuum until enough bloat (or after changing this, new
>> > inserts) has collected to start vacuum anew. Which then will do twice
>> > the work.
>> >
>> > Resetting n_dead_tuples to the actual remaining dead tuples wouldn't do
>> > much good either - we would just immediately trigger a new vacuum the
>> > next time we check, even if the xmin horizon is still the same.
>>
>> One idea would be to store the xmin we used for the vacuum somewhere.
>> Could we make that part of the pgstats infrastructure?  Or store it in
>> a new pg_class column?  Then we could avoid re-triggering until it
>> advances.  Or, maybe better, we could remember the oldest XID that we
>> weren't able to remove due to xmin considerations and re-trigger when
>> the horizon passes that point.
>
> I suggested a slightly more complex variant of this upthread:
> http://archives.postgresql.org/message-id/20130907053449.GE626072%40alap2.anarazel.de

Ah, yeah.  Sorry, I forgot about that.

Personally, I'd try the simpler version first.  But I think whoever
takes the time to implement this will probably get to pick.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company