When it comes to indexing in PostgreSQL, it's essential to understand why the database might not be using our carefully crafted indexes. A recent article highlights some common pitfalls that can lead to index underutilization.

Firstly, it's crucial to configure indexes according to our queries. If an index stores data in a different order than our query requests, it won't be used. Similarly, if we query only two columns but the index stores three, the index will be ignored.

Another issue arises when using functions or casting data types in our queries. In such cases, creating a specific index for that query can help improve performance.

Furthermore, not all operators are supported by regular B-tree indexes. For instance, ILIKE requires a different type of indexing. It's essential to use the appropriate operators to ensure efficient index usage.

Fortunately, tools like HypoPG and Metis' Index Advisor allow us to test and analyze indexes without creating them in the database. This can help identify potential issues and optimize our indexing strategy.

Source: https://dev.to/metis/why-doesnt-postgres-use-my-index-2b6e