Explore the need and usage of hypothetical indexes in PostgreSQL.

In this blog, we will first cover what are hypothetical indexes and what is needed for these types of indexes. Secondly, we shall see the practical usage of these indexes.

Need of Hypothetical Indexes:

As the name suggests, these are not real indexes, these are hypothetical indexes i.e. they are virtual indexes that PostgreSQL query planner does not consider when running queries.

Now the question arises where these **Hypothetical Indexes **are useful?

First, let’s discuss one scenario. We have a large table that is currently in the production environment and we need to make some indexes on live DB, and we are not sure whether that index will be useful or not. We don’t even know if by making that index our production environment may be down!

So, the solution to the above problem will be following:

• Let’s ignore the risk and make the index on the live table, which can result in the following scenario
• First of all, it will take lots of time depending on the data present in the table.
• Live queries may get affected badly if we are not sure if the index we are making will increase or decrease the cost.
• We also do not know the size of the index it may be too large, which can again impact the production database server.
• Another solution is to replicate the production database to the local dev environment and then apply all the hits and try there and then apply at the production environment. It seems a very safe and effective approach in almost all cases, but this will take too much time in setting up the things and testing with actual scenarios.
• The third solution is Hypothetical Indexes, as this functionality will create imaginary indexes, not real indexes. But there are some things to note about these indexes:
• It creates an index in our connection’s private memory. None of the catalog tables nor the original tables are actually touched.
• The only way to see if we can benefit from that index is by running an EXPLAIN QUERY.
• If you wish to run an EXPLAIN ANALYZE that runs that SQL and provides the run time stats, it would not be able to use that hypothetical index. This is because a hypothetical index does not exist in reality.
• Currently, these indexes will work on BTREE ONLY. However, you can try if it works on other types of indexes.

#database #postgresql #postgres