Date: Thu, 29 Nov 2012 13:12:28 +0100
From: Andres Freund <andres@2ndquadrant.com>
To: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Guillaume Cottenceau <gc@mnc.ch>, pgsql-performance@postgresql.org
Subject: Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM
 FULL
Message-ID: <20121129121228.GC15483@awork2.anarazel.de>
References: <m3mwy0myn5.fsf@mnc.ch>
	<CABOikdMMGWs0DKrYVndXxTSWDHMVhoC1JswE9gDNzkY=_0xugA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CABOikdMMGWs0DKrYVndXxTSWDHMVhoC1JswE9gDNzkY=_0xugA@mail.gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2012-11-29 17:20:01 +0530, Pavan Deolasee wrote:
> On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau <gc@mnc.ch> wrote:
>
> > Hello,
> >
> > I am toying around with 9.2.1, trying to measure/determine how
> > index-only scans can improve our performance.
> >
> > A small script which is attached to this mail, shows that as long
> > as the table has been VACUUM FULL'd, there is a unusual high
> > amount of heap fetches. It is strange that the visibilitymap_test
> > predicate fails in these situations, is the visibility map
> > somehow trashed in this situation? It should not, or at least the
> > documentation[1] should state it (my understanding is that vacuum
> > full does *more* than vacuum, but nothing less) (note to usual
> > anti vacuum full trollers: I know you hate vacuum full).
> >
> >
> I don't find it very surprising given that VACUUM FULL is now implemented
> as a CLUSTER command which rewrites the entire heap, thus invalidating all
> the visibility map info whatsoever.

Me neither.

> Now can CLUSTER or VACUUM FULL recreate the visibility map with all bits
> set to visible, thats an entirely different question. I don't think it can,
> but then I haven't thought through this completely.

It can't set everything to visible as it also copies RECENTLY_DEAD
tuples and tuples which are not yet visible to other transactions, but
it should be relatively easy to keep enough information about whether it
can set the current page to all visible. At least for the data in the
main relation, the toast tables are a different matter.
Just tracking whether the page in rewriteheap.c's state->rs_buffer
contains only tuples that are clearly visible according to the xmin
horizon seems to be enough.

The current effect of resetting the VM has the disadvantage of making
the next autovacuum basically a full table vacuum without any
benefits...

Greetings,

Andres

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


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