Skip site navigation (1) Skip section navigation (2)

Re: Not HOT enough

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Not HOT enough
Date: 2011-11-23 17:58:25
Message-ID: CA+U5nMKESsS+4=RCJJO+qqgYe-G=qLiuRRAHSEpy8aJucD1ODw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-11-23 18:15:58
Subject: Re: Not HOT enough
Previous:From: Pavan DeolaseeDate: 2011-11-23 17:07:11
Subject: Re: Not HOT enough

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group