pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_buffercache query example results misleading, grouping by just relname, needs schema_name
3+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2020-03-31 9b2009c4cf d684700136 9e8aec433a ce5d82f2d0 dd9f34ca00 7c8fb4a5e2 doc: add namespace column to pg_buffercache example query
* pg_buffercache query example results misleading, grouping by just relname, needs schema_name
@ 2020-02-12 23:55 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2020-02-12 23:55 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: adunham@arbormetrix.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
@ 2020-03-17 21:21 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Bruce Momjian @ 2020-03-17 21:21 UTC (permalink / raw)
To: adunham@arbormetrix.com; pgsql-docs@lists.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) download
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>
* Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
@ 2020-03-31 21:16 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2020-03-31 21:16 UTC (permalink / raw)
To: adunham@arbormetrix.com; pgsql-docs@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-31 21:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-12 23:55 pg_buffercache query example results misleading, grouping by just relname, needs schema_name PG Doc comments form <noreply@postgresql.org>
2020-03-17 21:21 ` Bruce Momjian <bruce@momjian.us>
2020-03-31 21:16 ` Bruce Momjian <bruce@momjian.us>
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