From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: adunham@arbormetrix.com
Subject: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
Date: Wed, 12 Feb 2020 23:55:51 +0000
Message-ID: <158155175140.23798.2189464781144503491@wrigleys.postgresql.org> (raw)
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
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: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, adunham@arbormetrix.com
Subject: Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name
In-Reply-To: <158155175140.23798.2189464781144503491@wrigleys.postgresql.org>
* 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