When database relations are very large, it is very inefficient and expensive to scan all tuples of a relation to find those tuples that match a given condition. In most relational database management system in order to avoid the scenario, indexes are created on tables, it is considered as the single greatest tuning technique that a database administrator can perform. It is helpful in the following scenarios:
- locating a single of a small slice of tuples out of the whole
- multiple table joins via foreign keys
- correlating data across tables
- aggregation data
- sorting data
However, indexing also has negative impacts on performance when data in a relation is inserted or updated more often. During inserting or updating, if there are indexes created on a relation, relative indexes have to be changed as well. So indexes need to be monitored, analyzed, and tuned to optimize data access and to ensure that the negative impact is not greater than positive impact.
Postgres provides several index types: B-tree, Hash, GiST and GIN. Each index type uses a different algorithm that is best suited to different types of queries. One can also build customized index types.
We'll discuss these indexing types in the next article.
Top comments (0)