Re: the big picture for index-only scans

On Sun, Jun 19, 2011 at 7:59 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> On Jun19, 2011, at 23:16 , Robert Haas wrote:
>> On Sun, Jun 19, 2011 at 5:10 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>>> On Jun19, 2011, at 20:40 , Robert Haas wrote:
>>>> 2. Since VACUUM and ANALYZE often run together, we will be estimating
>>>> the percentage of rows on all-visible pages just at the time when that
>>>> percentage is highest.  This is not exactly wonderful, either...
>>>
>>> Hm, doesn't autovacuum run ANALYZE quite a bit more frequently than
>>> VACUUM by default?
>>
>> The autoanalyze threshold is, by default, 10%; and the autovacuum
>> threshold, 20%.
>
> Hm, so you could ignore (or rather dampen) the results of
> VACUUM+ANALYZE and rely on the ANALYZE-only runs to keep
> the estimate correct. Still doesn't sound that bad...

Yeah, there are a lots of possible approaches. You could try to keep
a count of how many visibility map bits had been cleared since the
last run... and either adjust the estimate directly or use it to
trigger an ANALYZE (or some limited ANALYZE that only looks at
visibility map bits). You could gather statistics on how often the
queries that are actually running are finding the relevant visibility
map bits set, and use that to plan future queries. You could do what
you're suggesting... and there are probably other options as well.

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