Re: Turning off HOT/Cleanup sometimes

Robert Haas escribió:

> Unfortunately, there's no categorical answer. You can come up with
> workloads where HOT pruning on selects is a win; just create a bunch
> of junk and then read the same pages lots of times in a row. And you
> can also come up with workloads where it's a loss; create a bunch of
> junk and then read them just once. I don't know how easy it's going
> to be to set that parameter in a useful way for some particular
> environment, and I think that's possibly an argument against having
> it. 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.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services