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

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

From
Bruce Momjian
Date:
How do we want to handle the case where VACUUM FULL clears the
visibility map, causing loss of index-only scans?
http://archives.postgresql.org/pgsql-performance/2012-11/msg00317.php

Do we document this behavior or add a TODO item?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +



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

From
Josh Berkus
Date:
On 01/09/2013 03:07 PM, Bruce Momjian wrote:
> How do we want to handle the case where VACUUM FULL clears the
> visibility map, causing loss of index-only scans?
> 
>     http://archives.postgresql.org/pgsql-performance/2012-11/msg00317.php
> 
> Do we document this behavior or add a TODO item?
> 

Both?

Surely VACUUM FULL should rebuild the visibility map, and make tuples in
the new relation all-visible, no?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com



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

From
Amit Kapila
Date:
On Thursday, January 10, 2013 6:09 AM Josh Berkus wrote:
> On 01/09/2013 03:07 PM, Bruce Momjian wrote:
> > How do we want to handle the case where VACUUM FULL clears the
> > visibility map, causing loss of index-only scans?
> >
> >     http://archives.postgresql.org/pgsql-performance/2012-
> 11/msg00317.php
> >
> > Do we document this behavior or add a TODO item?
> >
>
> Both?
>
> Surely VACUUM FULL should rebuild the visibility map, and make tuples
> in
> the new relation all-visible, no?

I think it cannot made all visible.
How about if any transaction in SSI mode is started before Vacuum Full, should it see all tuples.

With Regards,
Amit Kapila.




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

From
Pavan Deolasee
Date:
On Thu, Jan 10, 2013 at 11:45 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
> On Thursday, January 10, 2013 6:09 AM Josh Berkus wrote:

>>
>> Surely VACUUM FULL should rebuild the visibility map, and make tuples
>> in
>> the new relation all-visible, no?
>
> I think it cannot made all visible.
> How about if any transaction in SSI mode is started before Vacuum Full, should it see all tuples.
>

We can definitely do better than what we are doing today and that
should fix many use cases and rebuild the VM for large part of the
table if not all. More precisely, in cluster.c we can see what does
HeapTupleSatisfiesVacuum() returns for every tuple in a page. If there
are only DEAD or LIVE tuples in a page, we can set the VM bit. We may
need similar additional checks for LIVE tuples like we have in vacuum
code path. But its certainly doable.

I'd also suggested doing something similar for cases when a table is
created and written in the same transaction. But that's more invasive.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee



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

From
Amit Kapila
Date:
On Thursday, January 10, 2013 12:01 PM Pavan Deolasee wrote:
> On Thu, Jan 10, 2013 at 11:45 AM, Amit Kapila <amit.kapila@huawei.com>
> wrote:
> > On Thursday, January 10, 2013 6:09 AM Josh Berkus wrote:
> 
> >>
> >> Surely VACUUM FULL should rebuild the visibility map, and make
> tuples
> >> in
> >> the new relation all-visible, no?
> >
> > I think it cannot made all visible.
> > How about if any transaction in SSI mode is started before Vacuum
> Full, should it see all tuples.
> >
> 
> We can definitely do better than what we are doing today and that
> should fix many use cases and rebuild the VM for large part of the
> table if not all. More precisely, in cluster.c we can see what does
> HeapTupleSatisfiesVacuum() returns for every tuple in a page. If there
> are only DEAD or LIVE tuples in a page, we can set the VM bit. We may
> need similar additional checks for LIVE tuples like we have in vacuum
> code path. But its certainly doable.

>> Do we document this behavior or add a TODO item?

> Both?

IMO, we should do both.

With Regards,
Amit Kapila.