bufcache.sql

text/plain

Download

CREATE EXTENSION pg_buffercache;

WITH usage AS (
	SELECT	usagecount, COUNT(*) AS page_count
	FROM 	pg_buffercache
	GROUP BY 1
	ORDER BY 1 DESC NULLS LAST
)
SELECT usagecount, page_count, (100 * page_count / SUM(page_count) OVER())::numeric(4,2)
FROM usage;

DROP TABLE IF EXISTS test;

CREATE TABLE test (x) AS SELECT random() FROM generate_series(1, 10000000);

\dt+

SHOW shared_buffers;

WITH usage AS (
	SELECT	usagecount, COUNT(*) AS page_count
	FROM 	pg_buffercache
	GROUP BY 1
	ORDER BY 1 DESC NULLS LAST
)
SELECT usagecount, page_count, (100 * page_count / SUM(page_count) OVER())::numeric(4,2)
FROM usage;

SELECT * FROM test ORDER BY 1 LIMIT 1;

WITH usage AS (
	SELECT	usagecount, COUNT(*) AS page_count
	FROM 	pg_buffercache
	GROUP BY 1
	ORDER BY 1 DESC NULLS LAST
)
SELECT usagecount, page_count, (100 * page_count / SUM(page_count) OVER())::numeric(4,2)
FROM usage;

UPDATE test SET x = x + 1;

WITH usage AS (
	SELECT	usagecount, COUNT(*) AS page_count
	FROM 	pg_buffercache
	GROUP BY 1
	ORDER BY 1 DESC NULLS LAST
)
SELECT usagecount, page_count, (100 * page_count / SUM(page_count) OVER())::numeric(4,2)
FROM usage;