pg_buffercache query example results misleading, grouping by just relname, needs schema_name
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/pgbuffercache.html
Description:
The pg_buffercache query example results are misleading. The "group by" uses
just by relname. It needs to include pg_namespace.nspname, without it, if
the same object exists in multiple schemas, the buffer count is summed for
those multiple distinct objects.
In: https://www.postgresql.org/docs/12/pgbuffercache.html
Alternative SQL (the count is now correct for tables in multiple schemas):
SELECT ts.nspname AS schema_name,c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace ts ON ts.oid = c.relnamespace
GROUP BY ts.nspname,c.relname
ORDER BY buffers DESC
LIMIT 10;
Example Results:
Current Query returns 1 row with buffer count summed for 3 tables:
relname buffers
tab1 72401
Modified Query:
schema_name relname buffers
schema1 tab1 1883
schema2 tab1 69961
schema3 tab1 557
On Wed, Feb 12, 2020 at 11:55:51PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/pgbuffercache.html
Description:The pg_buffercache query example results are misleading. The "group by" uses
just by relname. It needs to include pg_namespace.nspname, without it, if
the same object exists in multiple schemas, the buffer count is summed for
those multiple distinct objects.
In: https://www.postgresql.org/docs/12/pgbuffercache.html
Alternative SQL (the count is now correct for tables in multiple schemas):
SELECT ts.nspname AS schema_name,c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace ts ON ts.oid = c.relnamespace
GROUP BY ts.nspname,c.relname
ORDER BY buffers DESC
LIMIT 10;Example Results:
Current Query returns 1 row with buffer count summed for 3 tables:
relname buffers
tab1 72401Modified Query:
schema_name relname buffers
schema1 tab1 1883
schema2 tab1 69961
schema3 tab1 557
Very good point! Patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +Attachments:
buffercache.difftext/x-diff; charset=us-asciiDownload+16-14
Patch applied through PG 9.5. Thanks.
---------------------------------------------------------------------------
On Tue, Mar 17, 2020 at 05:21:36PM -0400, Bruce Momjian wrote:
On Wed, Feb 12, 2020 at 11:55:51PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/pgbuffercache.html
Description:The pg_buffercache query example results are misleading. The "group by" uses
just by relname. It needs to include pg_namespace.nspname, without it, if
the same object exists in multiple schemas, the buffer count is summed for
those multiple distinct objects.
In: https://www.postgresql.org/docs/12/pgbuffercache.html
Alternative SQL (the count is now correct for tables in multiple schemas):
SELECT ts.nspname AS schema_name,c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace ts ON ts.oid = c.relnamespace
GROUP BY ts.nspname,c.relname
ORDER BY buffers DESC
LIMIT 10;Example Results:
Current Query returns 1 row with buffer count summed for 3 tables:
relname buffers
tab1 72401Modified Query:
schema_name relname buffers
schema1 tab1 1883
schema2 tab1 69961
schema3 tab1 557Very good point! Patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com+ As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml index b5233697c3..2479181c5f 100644 --- a/doc/src/sgml/pgbuffercache.sgml +++ b/doc/src/sgml/pgbuffercache.sgml @@ -148,27 +148,28 @@ <title>Sample Output</title><screen> -regression=# SELECT c.relname, count(*) AS buffers +regression=# SELECT n.nspname, c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) - GROUP BY c.relname - ORDER BY 2 DESC + JOIN pg_namespace n ON n.oid = c.relnamespace + GROUP BY n.nspname, c.relname + ORDER BY 3 DESC LIMIT 10;- relname | buffers ----------------------------------+--------- - tenk2 | 345 - tenk1 | 141 - pg_proc | 46 - pg_class | 45 - pg_attribute | 43 - pg_class_relname_nsp_index | 30 - pg_proc_proname_args_nsp_index | 28 - pg_attribute_relid_attnam_index | 26 - pg_depend | 22 - pg_depend_reference_index | 20 + nspname | relname | buffers +------------+------------------------+--------- + public | delete_test_table | 593 + public | delete_test_table_pkey | 494 + pg_catalog | pg_attribute | 472 + public | quad_poly_tbl | 353 + public | tenk2 | 349 + public | tenk1 | 349 + public | gin_test_idx | 306 + pg_catalog | pg_largeobject | 206 + public | gin_test_tbl | 188 + public | spgist_text_tbl | 182 (10 rows) </screen> </sect2>
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +