An index is a physical database object that's defined in a table column or a list of columns. In PostgreSQL, there are many types of indexes and several ways to use them. Indexes can be used, in general, to do the following:
- Optimize performance: An index allows for the efficient retrieval of a small number of rows from the table. Whether the number of rows is considered small is determined by the total number of rows in the table and the execution planner settings.
- Validate constraints: An index can be used to validate the constraints on several rows. For example, the UNIQUE check constraint creates a unique index on the column behind the scenes.
The following example shows how to use GIST to forbid overlapping between date ranges. Checking for date overlapping is very important in reservation systems, such as car and hotel reservation systems. For more information...
