Re: Turning off HOT/Cleanup sometimes

On Mon, Apr 20, 2015 at 12:28:11PM -0700, Jeff Janes wrote:
> But why should 1 SELECT or 20 SELECTs or 200 SELECTs have to do a job, while
> the user waits, which is fundamentally VACUUM's duty to do in the background? 
> If there are a handful of very hot pages, then it makes sense not to wait for
> vacuum to get to them.  And that is what a block-count limit does.  
>
> But if the entire table is very hot, I think that that is just another of way
> of saying that autovacuum is horribly misconfigured.  I think the purpose of

Well, we have to assume there are many misconfigured configurations ---
autovacuum isn't super-easy to configure, so we can't just blame the
user if this makes things worse. In fact, page pruning was designed
spefically for cases where autovacuum wasn't running our couldn't keep
up.

> this patch is to fix something that can't be fixed through configuration alone.
>  
>
> If there are future writes, they would dirty the pages and
> cause even more pruning, but the 5% gives me the maximum pruning number
> of SELECTs.  If there aren't another 19 SELECTs, do I care if the table
> is pruned or not? 
>
>
> The use case I see for this is when there is a mixed workload.  There is one
> select which reads the entire table, and hundreds of thousands of selects/
> updates/insert that don't, and of course vacuum comes along every now and then
> and does it thing.  Why should the one massive SELECT have horrible performance
> just because it was run right before autovacuum would have kicked in instead of
> right after if finished?

I see your point, but what about the read-only workload after a big
update? Do we leave large tables to be non-pruned for a long time?
Also, consider cases where you did a big update, the autovacuum
thresh-hold was not met, so autovacuum doesn't run on that table ---
again, do we keep those non-pruned rows around for millions of scans?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +