Re: Turning off HOT/Cleanup sometimes

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> Robert Haas escribi:
>> But the argument that we don't need a parameter because one
>> behavior is best for everyone is not going to fly.

> In the above, there's the underlying assumption that it doesn't matter
> *what* we do with the page after doing or not doing pruning. But this
> is not necessarily the case: in the case of an UPDATE, having the space
> be freed beforehand is beneficial because there's the option of putting
> the new version of the tuple in the same page, potentially saving lots
> of I/O (bring up another destination page for the new tuple, write the
> new tuple there, end up dirtying two pages instead of one). But in a
> SELECT, the effect is only that you will have to skip less dead tuples,
> which is not as exciting.

Yeah. Once they're hinted dead, it doesn't cost that much to skip over
them. Not to mention that you might well never visit them at all, if
this is an indexscan that knows which TIDs it needs to look at.

It's possible that it can be shown that different use-cases have
sufficiently different behaviors that we really do need a user-visible
parameter. I don't want to start from that position though. If we
did have a simple GUC parameter, it'd likely end up in the same boat
as, say, enable_seqscan, which is way too blunt an instrument for real
world use --- so I'm afraid this would soon bloat into a request for
per-table settings, planner hints, or god knows what to try to confine
the effects to the queries where it's appropriate. Let's not go there
without proof that we have to. It's a much better thing if we can get
the system's native behavior to be tuned well enough by depending on
things it already knows.

regards, tom lane