From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | adunham(at)arbormetrix(dot)com, pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_buffercache query example results misleading, grouping by just relname, needs schema_name |
Date: | 2020-03-17 21:21:36 |
Message-ID: | 20200317212136.GB17915@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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(at)momjian(dot)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 | Content-Type | Size |
---|---|---|
buffercache.diff | text/x-diff | 2.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2020-03-18 00:52:29 | Re: Clarification on interactions between query parameters and partial indexes |
Previous Message | Bruce Momjian | 2020-03-17 21:02:25 | Re: COPY performance vs insert |