I wanted to know the contents of the buffer cache in 7.4, so I created
this function and view that seems to work ok:
/* Takes relfilenode (from pg_class) and returns the number of
* buffer cache pages it is using.
*/
PG_FUNCTION_INFO_V1(block_count);
Datum
block_count(PG_FUNCTION_ARGS)
{
Oid relfilenode = PG_GETARG_OID(0);
BufferDesc *buf;
Buffer buffer;
int count = 0;
// 0 is not a valid relfilenode although pg_xactlock
// uses it
if (relfilenode == 0) {
PG_RETURN_INT32(0);
}
for (buffer=1; BufferIsValid(buffer); ++buffer) {
buf = &(BufferDescriptors[buffer - 1]);
if (buf->tag.rnode.relNode == relfilenode) {
count ++;
}
}
PG_RETURN_INT32(count);
}
/* Returns the number of buffer cache pages the specified relfilenode
* is using. Using "buffer" view is probably easier.
*/
CREATE OR REPLACE FUNCTION block_count (oid)
RETURNS integer
LANGUAGE 'C' STABLE STRICT AS '/foo';
/* View to look at how much of the buffer cache different tables are
* currently using.
* Example:
* foo=# select * from buffer limit 10;
* relname | block_count
* ---------------+-------------
* foo | 13782
* bar | 2575
* ...
* (10 rows)
*/
CREATE OR REPLACE VIEW buffer AS
SELECT * FROM (SELECT relname, block_count(relfilenode) FROM pg_class) t
WHERE block_count > 0 ORDER BY block_count DESC;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
