Date: Tue, 31 Mar 2020 17:16:56 -0400
From: Bruce Momjian <bruce@momjian.us>
To: adunham@arbormetrix.com, pgsql-docs@lists.postgresql.org
Subject: Re: pg_buffercache query example results misleading, grouping by
 just relname, needs schema_name
Message-ID: <20200331211656.GD17676@momjian.us>
References: <158155175140.23798.2189464781144503491@wrigleys.postgresql.org>
 <20200317212136.GB17915@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200317212136.GB17915@momjian.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


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