One of the critical parts of your SQL queries' performance is the index selection. If you are like most developers, you are probably using the default indexes on your databases, such as BTrees, which is usually a very safe option. But understanding the Index types, the internal representation, and why they are there can lead you to essential improvements in your query performance by changing or adding your indexes to get the better of them.

PostgreSQL supports multiple index formats. The most common one is the BTree, but there are also others like Hash, GiST, SP-GiST, GIN, and BRIN.

In this talk, we will explore each of them by explaining how the data is structured and how it helps to access the data quickly by showing an example of how the index is accessed by the queries.

After explaining the different indexes, I'll explain specific use cases where they are valuable and show some examples in a Postgres instance with data in it.