Re: [PERFORM] encouraging index-only scans

On Mon, Sep 9, 2013 at 9:33 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> I thought it was well known, but maybe I was overly optimistic. I've
>>> considered IOS to be mostly useful for data mining work on read-mostly
>>> tables, which you would probably vacuum manually after a bulk load.
>>>
>>> For transactional tables, I think that trying to keep the vm set-bit
>>> density high enough would be a losing battle. If we redefined the
>>> nature of the vm so that doing a HOT update would not clear the
>>> visibility bit, perhaps that would change the outcome of this battle.
>>
>> Wouldn't it make the Vacuum bit in-efficient in the sense that it will
>> skip some of the pages in which there are only
>> HOT updates for cleaning dead rows.
>
> Maybe. But anyone is competent to clean up dead rows from HOT
> updates, it is not exclusively vacuum that can do it, like it is for
> non-HOT tuples.

Yes, that is right, but how about freezing of tuples, delaying that
also might not be good. Also it might not be good for all kind of
scenarios that always foreground operations take care of cleaning up
dead rows leaving very less chance for Vacuum (only when it has to
scan all pages aka anti-wraparound vacuum) to cleanup dead rows.

If we are sure that Vacuum skipping pages in a database where there
are less non-HOT updates and deletes (or mostly inserts and
Hot-updates) is not having any significant impact, then it can be
quite useful for IOS.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com