PostgreSQL – Size of Indexes

In this article, we will look into a function that is used to get the size of the PostgreSQL database indexes of a table. The pg_indexes_size() function takes in the table name or respective OID and returns the size of all the attached indexes from a table.
For the purpose of example, we will be using a sample database for reference which is described here and can be downloaded from here
The pg_indexes_size() function is used to get the total size of all indexes attached to a table.

Syntax: select pg_indexes_size('table_name');

Example 1:
Here we will query for the total size of indexes attached to the “film” table of our sample database using the below command: 

SELECT pg_indexes_size('film');

Output:

To make the result readable, one can use the pg_size_pretty() function. The pg_size_pretty() function takes the result of another function and formats it using bytes, KB, MB, GB, or TB as required. 

SELECT pg_size_pretty (pg_indexes_size('film'));

Output:

Example 2:
Here we will query for the total size of indexes attached to the “customer” table of our sample database using the below command: 

SELECT pg_size_pretty (pg_indexes_size('customer'));

Output:

Example 3:
Here we will query for the total size of indexes attached to the “actor” table of our sample database using the below command: 

SELECT pg_size_pretty (pg_indexes_size('actor'));

Output: