Re: Not HOT enough

On Wed, Nov 23, 2011 at 5:01 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Wed, Nov 23, 2011 at 3:20 PM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>>> Maybe not pg_database or pg_tablespace and such, but I'm not so sure
>>> about pg_shdepend. (Do we record pg_shdepend entries for temp tables?)
>
>> Normal catalog access does not use HOT and never has.
>
> You are mistaken.

Normal catalog access against shared catalogs via heap_scan does not
use HOT cleanup, because it uses SnapshotNow.
Page cleanup when reading a page only happens when scan->rs_pageatatime is set.
scan->rs_pageatatime = IsMVCCSnapshot(snapshot);

Index access does use HOT cleanup, which is probably "normal".

However, since we're talking about these tables only

postgres=# select relname, pg_relation_size(oid) from pg_class where
relisshared and relkind = 'r';
relname | pg_relation_size
--------------------+------------------
pg_authid | 8192
pg_database | 8192
pg_tablespace | 8192
pg_pltemplate | 8192
pg_auth_members | 0
pg_shdepend | 8192
pg_shdescription | 8192
pg_db_role_setting | 0
(8 rows)

then I think it's fair to say that they are seldom updated/deleted and
so the effect of HOT cleanup is not important for those tables.

The real question is do we favour HOT cleanup on those small 8 tables,
or do we favour HOT cleanup of every other table? There are clearly
pros and cons but the balance must surely be in favour of better
cleaning of user tables since they are accessed millions of times more
frequently than shared catalog tables.

If we are concerned about those 8 tables then we can always set
autovacuum more intensively.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services