Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name

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 72401
> >
> > Modified Query:
> > schema_name relname buffers
> > schema1 tab1 1883
> > schema2 tab1 69961
> > schema3 tab1 557
>
> Very good point! Patch attached.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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 +