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
|
Список | 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 по дате отправления: