Useful Queries For PostgreSQL Index Maintenance

PostgreSQLPostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with the actual table, therefore there is a need for cleanup.

There is a wiki page that has some queries related to PostgreSQL Index Maintenance.

Before writing the queries I want to introduce a catalog table pg_index. The table contains information about the index. This is the basic catalog table, all the index-based views use the same table.

1 – Sometimes you need to see how many indexes your table has. This query will show the schema-qualified table name and its index names.

2 – As we all know, an index is a performance feature, but along with that, it is also used to ensure uniqueness. But to ensure the uniqueness we need a separate type of index called a unique index. To check whether an index is unique or not, pg_index has a column named “indisunique” to identify the uniqueness of the index.

3 – There is a pretty simple way to get the size of the index of PostgreSQL. Here is a query to list the PostgreSQL with size.

4 – Here is a list of the indexes with total table size and size of the index, which is very useful to compare your table size with its corresponding indexes.  It’s very good to know the size of your table, index, and the total size of the table.

pg_relation_size: Function gives the size of relation. It is used to get the size of the table/index.

pg_total_relation_size:  This is a special function that gives the total size of the table along with its all indexes.

5 – Get the query of the index. This query will show the index creation query.

6 – In case your index becomes corrupted or bloated, you need to build that index again. At the same time, you don’t want to block the operation on your table, so this REINDEX CONCURRENTLY command is your choice for that.

7 – PostgreSQL has many index methods like BTree, Hash, BRIN, GIST, and GIN. Sometimes we want to create some specific index on a column but are unable to do that. PostgreSQL has limitations that some indexes cannot be created on some data types and operators, and that makes sense too. For example, the Hash index can only be used for equal operators. Here is a query to get the list of the supported data types for a particular index.

8 – This query will find the unused indexes. If index_scans is 0 or close to 0 then you can drop those indexes. But be careful, as maybe those indexes are for unique purposes.

9 – Query used to find a duplicate index. In this example, pgbench_accounts has two of the same indexes. There is no need to have multiple same indexes with a different name on a table. As we already discussed, in case of update/insert, all the indexes get updated along with the actual table, which hurts the performance.

Conclusion

PostgreSQL has catalog tables to store the index information, and therefore, we can write as many queries as we need. This blog shows some basic queries and shows how to use the catalog tables to write the queries.


Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF

Share this post

Leave a Reply