Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
Date: 2012-11-29 11:50:01
Message-ID: CABOikdMMGWs0DKrYVndXxTSWDHMVhoC1JswE9gDNzkY=_0xugA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Nov 29, 2012 at 5:03 PM, Guillaume Cottenceau <gc(at)mnc(dot)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. The code paths that VACUUM FULL and
LAZY VACUUM takes are now completely different.

Even with the old VACUUM FULL we would have seen some impact on heap
fetches because it used to move tuples around and thus potentially
resetting visibility map bits. But its definitely going to be worse with
the new implementation.

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.

Thanks,
Pavan

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andres Freund 2012-11-29 12:12:28 Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
Previous Message Guillaume Cottenceau 2012-11-29 11:33:50 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL