Re: [PERFORM] encouraging index-only scans

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company