Re: Turning off HOT/Cleanup sometimes

On 3/9/15 1:36 PM, Jeff Janes wrote:
> Did versions 7 and 8 of this patch address Andres' concern about
> performance regressions?

I don't think so. Andres basically wanted a nontrival algorithm to
determine how much pruning to do during a read-only scan. And Robert
basically said, that's not really possible.

The presented patch actually has a hardcoded prune limit of 4 per scan,
which I don't see mentioned in the discussion anywhere (except in very
early versions, where this was exposed as a knob).

I think most people were of the opinion that scans on system catalogs
should not be affected by this behavior change. Makes sense to me:
System catalog bloat is likely a bigger problem than speeding up queries
on catalogs with large live data.

And then there is still some disagreement whether just turning this on
is tolerable for all uses. Andres mentioned workloads that have trouble
getting a cleanup lock. README.HOT seems to think that cleaning up
during reads is important because skipping over dead tuples is
expensive. Nobody seems to like the idea of (implicitly) pushing more
responsibility on VACUUM. We have seen some benchmarks that show
significant improvements. We have seen some (constructed ones) that
show problems.

I don't know how to move forward. We could give users a knob: This
might make your queries faster or not -- good luck. But of course
nobody will like that either.