-1

I found this shared buffers how to look into usefull link. My tables

 Schema |           Name            |   Type   |  Owner   |    Size    | Description 
--------+---------------------------+----------+----------+------------+-------------
 public | course                    | table    | postgres | 16 kB      | 
 public | documents                 | table    | postgres | 8192 bytes | 
 public | documents_document_id_seq | sequence | postgres | 8192 bytes | 
 public | enrolled                  | table    | postgres | 16 kB      | 
 public | pg_buffercache            | view     | postgres | 0 bytes    | 
 public | student                   | table    | postgres | 8192 bytes | 
 public | student_sid_seq           | sequence | postgres | 8192 bytes | 
 public | testdecimals              | table    | postgres | 496 MB     | 
 public | testreals                 | table    | postgres | 346 MB     | 

I try to run this file for testreals

SELECT c.relname, c.relkind, count(*)
FROM pg_database AS a, pg_buffercache AS b, pg_class AS c
WHERE c.relfilenode = b.relfilenode
AND b.reldatabase = a.oid
AND c.oid >= 16384
AND a.datname = 'testreals'
GROUP BY 1, 2
ORDER BY 3 DESC, 1;

It does not work.I am newbie to PostgreSQL.

 relname | relkind | count 
---------+---------+-------
(0 rows)

What do .oid and .relfilenode stand for? How to edit the code, to inspect shared buffers for this table?

I tried what Laurentz suggested

select * from testdecimals limit 10;
select * from testreals limit 10;

Now I have output

   relname    | relkind | count 
--------------+---------+-------
 testreals    | r       |    33
 testdecimals | r       |     1
(2 rows)
1

I have two explanations for what you observe:

  • There is no database testreals.

  • No table, index, sequence or materialized view in the database testreals has been touched since the database server was started, so nothing is cached.

Try and SELECT from a table in that database, then try the query again.

oid is the object ID, which for metadata tables like the ones in your query identifies the object (the database in the case of pg_database and the table or index or sequence or materialized view in the case of pg_class).

relfilenode is the name of the file storing the data in a table or index or sequence or materialized view.

See the documentation for the system catalog for details.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.