From: Bruce Momjian <bruce@momjian.us>
To: adunham@arbormetrix.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
Date: Tue, 17 Mar 2020 17:21:36 -0400
Message-ID: <20200317212136.GB17915@momjian.us> (raw)
In-Reply-To: <158155175140.23798.2189464781144503491@wrigleys.postgresql.org>
References: <158155175140.23798.2189464781144503491@wrigleys.postgresql.org>
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 +
Attachments:buffercache.diff (text/x-diff, 2.0K) downloaddiff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, adunham@arbormetrix.com, pgsql-docs@lists.postgresql.org
Subject: Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
In-Reply-To: <20200317212136.GB17915@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox