Обсуждение: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL
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. +
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
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.
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
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.