pg_indexes_size()
pg_indexes_size() is a system function determining the total on-disk size of a table's indexes.
pg_indexes_size() was added in PostgreSQL 9.0.
Usage
pg_indexes_size (regclass) →bigint
The caller does not require any permissions on the table to determine the size of its indexes. An ACCESS EXCLUSIVE LOCK on the table will however cause the function to stall until the lock is released.
To calculate the total size of data associated with a table (i.e. the cumulative total of the values returned by pg_table_size() and pg_indexes_size(), use pg_total_relation_size().
Source code
The source code for pg_indexes_size() is contained in src/backend/utils/adt/dbsize.c; the main work is carried out by calculate_indexes_size().
Change history
- PostgreSQL 9.0
- added (commit 4f15699d)
Examples
Basic usage example for pg_indexes_size():
postgres=# SELECT pg_size_pretty(pg_indexes_size('object_property')); pg_size_pretty ---------------- 19 MB (1 row)
Note that as of PostgreSQL 14, pg_indexes_size() will accept the name of any valid relation, not just tables, in which case it will always report an index size of 0:
postgres=# SELECT pg_indexes_size('object_property_pkey');
pg_indexes_size
-----------------
0
(1 row)
Attempting to retrieve the total index size of a non-existent relation:
postgres=# SELECT pg_indexes_size('foo');
ERROR: relation "foo" does not exist
LINE 1: SELECT pg_indexes_size('foo');;
References
- PostgreSQL documentation: Database Object Size Functions