pg_buffercache query example results misleading, grouping by just relname, needs schema_name

Поиск
Список
Период
Сортировка
От PG Doc comments form
Тема pg_buffercache query example results misleading, grouping by just relname, needs schema_name
Дата
Msg-id 158155175140.23798.2189464781144503491@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: pg_buffercache query example results misleading, grouping byjust relname, needs schema_name  (Bruce Momjian <bruce@momjian.us>)
Список pgsql-docs
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

В списке pgsql-docs по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Getting our tables to render better in PDF output
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Getting our tables to render better in PDF output