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:
Contact Us