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

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@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 +