Re: [PERFORM] encouraging index-only scans

On Thu, Sep 5, 2013 at 8:14 PM, Bruce Momjian <bruce(at)momjian(dot)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