PostgreSQL 9.5.14 Documentation /Chapter 28 / 28.1. Determining Disk Usage

Started by PG Bug reporting formover 7 years ago2 messagesdocs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.5/static/disk-usage.html
Description:

PostgreSQL 9.5.14 Documentation / Chapter 28. Monitoring Disk Usage / 28.1.
Determining Disk Usage
(Applies for other Versions, too.)

I'm wondering if the two statements given showing the index space size as
described.
I've the impression they show the space size of the tables.

Regards --Michael

Extract of the manual:

You can easily display index sizes, too:
SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'customer' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname;

relname | relpages
----------------------+----------
customer_id_indexdex | 26

It is easy to find your largest tables and indexes using this information:
SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;

relname | relpages
----------------------+----------
bigtable | 3290

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: PG Bug reporting form (#1)
Re: PostgreSQL 9.5.14 Documentation /Chapter 28 / 28.1. Determining Disk Usage

On Wed, Sep 5, 2018 at 4:43 AM, PG Doc comments form <noreply@postgresql.org

wrote:

I'm wondering if the two statements given showing the index space size as
described.
I've the impression they show the space size of the tables.

"Each page is typically 8 kilobytes. (Remember, relpages is only updated by
VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.)"

So while it takes a bit of interpretation it is stated that "relpages" are
not a size on their own but a value to be multiplied by 8k to come up with
the disk size (typically since one can compile the server with a different
page size).

David J.