bufcache.out

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;
 usagecount | page_count | numeric 
------------+------------+---------
          5 |        100 |    2.44
          4 |         23 |    0.56
          3 |         16 |    0.39
          2 |         46 |    1.12
          1 |        630 |   15.38
            |       3281 |   80.10
(6 rows)

DROP TABLE IF EXISTS test;
DROP TABLE
CREATE TABLE test (x) AS SELECT random() FROM generate_series(1, 10000000);
SELECT 10000000
\dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | test | table | postgres | 346 MB | 
(1 row)

SHOW shared_buffers;
 shared_buffers 
----------------
 32MB
(1 row)

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;
 usagecount | page_count | numeric 
------------+------------+---------
          5 |        119 |    2.91
          4 |         27 |    0.66
          3 |         14 |    0.34
          2 |         62 |    1.51
          1 |        529 |   12.92
            |       3345 |   81.67
(6 rows)

SELECT * FROM test ORDER BY 1 LIMIT 1;
          x           
----------------------
 1.30385160446167e-08
(1 row)

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;
 usagecount | page_count | numeric 
------------+------------+---------
          5 |        119 |    2.91
          4 |         28 |    0.68
          3 |         13 |    0.32
          2 |         63 |    1.54
          1 |        560 |   13.67
            |       3313 |   80.88
(6 rows)

UPDATE test SET x = x + 1;
UPDATE 10000000
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;
 usagecount | page_count | numeric 
------------+------------+---------
          5 |        633 |   15.45
          4 |        399 |    9.74
          3 |        632 |   15.43
          2 |        384 |    9.38
          1 |       1282 |   31.30
          0 |        766 |   18.70
(6 rows)