Re: [PERFORM] encouraging index-only scans

On Sun, Sep 8, 2013 at 12:47:35AM +0200, Andres Freund wrote:
> Hi,
>
> On 2013-09-07 12:50:59 -0400, Bruce Momjian wrote:
> > That seems very complicated. I think it would be enough to record the
> > current xid at the time of the vacuum, and when testing for later
> > vacuums, if that saved xid is earlier than the RecentGlobalXmin, and
> > there have been no inserts/updates/deletes, we know that all of
> > the pages can now be marked as allvisible.
>
> But that would constantly trigger vacuums, or am I missing something? Or
> what are you suggesting this xid to be used for?

OK, let me give some specifices. Let's suppose we run a vacuum, and at
the time the current xid counter is 200. If we later have autovacuum
check if it should vacuum, and there have been no dead rows generated
(no update/delete/abort), if the current RecentGlobalXmin is >200, then
we know that all the transactions that prevented all-visible marking the
last time we ran vacuum has completed. That leaves us with just
inserts that could prevent all-visible.

If there have been no inserts, we can assume that we can vacuum just the
non-all-visible pages, and even if there are only 10, it just means we
have to read 10 8k blocks, not the entire table, because the all-visible
is set for all the rest of the pages.

Now, if there have been inserts, there are a few cases. If the inserts
happened in pages that were previously marked all-visible, then we now
have pages that lost all-visible, and we probably don't want to vacuum
those. Of course, we will not have recorded which pages changed, but
any decrease in the all-visible table count perhaps should have us
avoiding vacuum just to set the visibility map. We should probably
update our stored vm bit-set count and current xid value so we can check
again later to see if things have sabilized.

If the vm-set bit count is the same as the last time autovacuum checked
the table, then the inserts happened either in the vm-bit cleared pages,
or in new data pages. If the table size is the same, the inserts
happened in existing pages, so we probably don't want to vacuum. If the
table size has increased, some inserts went into new pages, so we might
want to vacuum, but I am unclear how many new pages should force a
vacuum.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +