Thread: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
pg_buffercache query example results misleading, grouping by just relname, needs schema_name
From
PG Doc comments form
Date:
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
Re: pg_buffercache query example results misleading, grouping byjust relname, needs schema_name
From
Bruce Momjian
Date:
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 +
Attachment
Re: pg_buffercache query example results misleading, grouping byjust relname, needs schema_name
From
Bruce Momjian
Date:
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 +