Message-ID: <52292FFC.8020708@archidevsys.co.nz>
Date: Fri, 06 Sep 2013 13:29:32 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130805 Thunderbird/17.0.8
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: Bruce Momjian <bruce@momjian.us>, Peter Geoghegan <peter@2ndquadrant.com>,
 Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
References: <50C8F1EC.9010801@dunslane.net> <15321.1355347953@sss.pgh.pa.us>
 <50C90154.8060103@dunslane.net> <50C904DB.9090305@dunslane.net>
 <20121213024837.GB22363@momjian.us> <23298.1355370698@sss.pgh.pa.us>
 <CAEYLb_U1dTg=bXkDL=yvFTrMw5oC13ivgZA=jdQXnbgaDJ+e-Q@mail.gmail.com>
 <20130904205655.GR21874@momjian.us> <20130906001437.GA29264@momjian.us>
 <CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
In-Reply-To: 
 <CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------060202030506090009070103"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------060202030506090009070103
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 06/09/13 13:10, Robert Haas wrote:
> On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Actually, I now realize it is more complex than that, and worse.  There
>> are several questions to study to understand when pg_class.relallvisible
>> is updated (which is used to determine if index-only scans are a good
>> optimization choice), and when VM all-visible bits are set so heap pages
>> can be skipped during index-only scans:
>>
>>          1)  When are VM bits set:
>>                  vacuum (non-full)
>>                  analyze (only some random pages)
> Analyze doesn't set visibility-map bits.  It only updates statistics
> about how many are set.
>
>> The calculus we should use to determine when we need to run vacuum has
>> changed with index-only scans, and I am not sure we ever fully addressed
>> this.
> Yeah, we didn't.  I think the hard part is figuring out what behavior
> would be best.  Counting inserts as well as updates and deletes would
> be a simple approach, but I don't have much confidence in it.  My
> experience is that having vacuum or analyze kick in during a bulk-load
> operation is a disaster.  We'd kinda like to come up with a way to
> make vacuum run after the bulk load is complete, maybe, but how would
> we identify that time, and there are probably cases where that's not
> right either.
>
How about a 'VACUUM AFTER' command (part of the 'BEGIN' transaction 
syntax?) that would:

 1. only be valid in a transaction
 2. initiate a vacuum after the current transaction completed
 3. defer any vacuum triggered due to other criteria

If the transaction was rolled back: then if there was a pending vacuum, 
due to other reasons, it would then be actioned.

On normal transaction completion, then if there was a pending vacuum it 
would be combined with the one in the transaction.

Still would need some method of ensuring any pending vacuum was done if 
the transaction hung, or took too long.


Cheers,
Gavin

--------------060202030506090009070103
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 06/09/13 13:10, Robert Haas wrote:<br>
    </div>
    <blockquote
cite="mid:CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com"
      type="cite">
      <pre wrap="">On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian <a class="moz-txt-link-rfc2396E" href="mailto:bruce@momjian.us">&lt;bruce@momjian.us&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="">Actually, I now realize it is more complex than that, and worse.  There
are several questions to study to understand when pg_class.relallvisible
is updated (which is used to determine if index-only scans are a good
optimization choice), and when VM all-visible bits are set so heap pages
can be skipped during index-only scans:

        1)  When are VM bits set:
                vacuum (non-full)
                analyze (only some random pages)
</pre>
      </blockquote>
      <pre wrap="">
Analyze doesn't set visibility-map bits.  It only updates statistics
about how many are set.

</pre>
      <blockquote type="cite">
        <pre wrap="">The calculus we should use to determine when we need to run vacuum has
changed with index-only scans, and I am not sure we ever fully addressed
this.
</pre>
      </blockquote>
      <pre wrap="">
Yeah, we didn't.  I think the hard part is figuring out what behavior
would be best.  Counting inserts as well as updates and deletes would
be a simple approach, but I don't have much confidence in it.  My
experience is that having vacuum or analyze kick in during a bulk-load
operation is a disaster.  We'd kinda like to come up with a way to
make vacuum run after the bulk load is complete, maybe, but how would
we identify that time, and there are probably cases where that's not
right either.

</pre>
    </blockquote>
    <font size="-1">How about a 'VACUUM AFTER' command</font> (part of
    the 'BEGIN' transaction syntax?) that would:<br>
    <ol>
      <li>only be valid in a transaction</li>
      <li>initiate a vacuum after the current transaction completed</li>
      <li>defer any vacuum triggered due to other criteria<br>
      </li>
    </ol>
    If the transaction was rolled back: then if there was a pending
    vacuum, due to other reasons, it would then be actioned.<br>
    <br>
    On normal transaction completion, then if there was a pending vacuum
    it would be combined with the one in the transaction.<br>
    <br>
    Still would need some method of ensuring any pending vacuum was done
    if the transaction hung, or took too long.<br>
    <br>
    <br>
    Cheers,<br>
    Gavin<br>
  </body>
</html>

--------------060202030506090009070103--