Thread: More buffers used than a relation's relpages

Hello,

In what cases can a relation use more buffers (in shared memory) than
its pg_class.relpages?

For example, look at the second row in the following:

postgres=# SELECT c.relname,count(*) AS buffers, c.relpages
FROM pg_class c INNER JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode INNER JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
GROUP BY c.relname,c.relpages
ORDER BY 2 DESC;
              relname              | buffers | relpages
-----------------------------------+---------+----------
 abc_idx          |    4800 |     4800
 abc                             |    3548 |     3547
abc_2_idx                |    3209 |     3209
...
...



--
Amit Langote

Hello,

In what cases can a relation use more buffers (in shared memory) than
its pg_class.relpages?

On Tue, Jun 4, 2013 at 12:17 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Monday, June 3, 2013, Amit Langote wrote:
>>
>> Hello,
>>
>> In what cases can a relation use more buffers (in shared memory) than
>> its pg_class.relpages?
>
>
> I think relpages only counts pages in the main fork, while the buffer cache
> will also have buffers for the other forks (visibility map, free space map).
> Also, relpages can be out of date if the table has grown since the last
> vac/analyze.
>

Is there a way to refer to pages of these other forks like
pg_class.relpages is for referring to relation's disk pages as of the
last analyze (is that right?)


--
Amit Langote

On 2013-06-04 11:29:44 +0900, Amit Langote wrote:
> In what cases can a relation use more buffers (in shared memory) than
> its pg_class.relpages?

relpages is primarily updated by (auto-)vacuum, (auto-)analyze, so it
frequently lags behind reality in a growing relation. Why do you need an
exact value?

Greetings,

Andres Freund

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

On Tue, Jun 4, 2013 at 10:04 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2013-06-04 11:29:44 +0900, Amit Langote wrote:
>> In what cases can a relation use more buffers (in shared memory) than
>> its pg_class.relpages?
>
> relpages is primarily updated by (auto-)vacuum, (auto-)analyze, so it
> frequently lags behind reality in a growing relation. Why do you need an
> exact value?
>

I think I forgot that pg_class.relpages is a statistic that needs
updates to reflect its current value.

I don't need it (the value) for anything per se, was just wondering if
I was missing something about such an observation. I was using
pg_buffercache to understand what kind of page traffic a pg_trgm index
generates and in one case when it just finished building a pg_trgm
index (say, abc_idx) over a relation (say, abc), I observed that the
relation's (abc's) shared buffers count was one more than its relpages
at that moment.


--
Amit Langote