Contents

pg_indexes_size()

A function for returning the size of a table's indexes

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

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');;

Categories

System function

See also

pg_table_size(), pg_relation_size(), pg_total_relation_size()