Indexes

This is a simple idea:

• Create a data structure and store it somewhere that lets us go straight to where the information we want is, instead of having to plough through every record until we find the ones we want. The data structure emulates a binary search ordered list.
• This structure can also point to more than one row (say it’s an index on a foreign key) so you can quickly get to multiple rows of data.

There’s also a useful idea that’s used when looking for data, if you have some selection criteria but don’t need to retrieve the data, for example does a record exist that matches some joining criterion, you can perform an index probe that scans the index and says whether or not something exists with a particular key without having to go and fetch the data.

Indexes are created by reading every record in a file or table and placing a link to that record against the key in the data structure.

Partial indexes

Indexes can be on more than one column and filters can use the index to match multiple columns.

Unique indexes

This does what it says on the tin. Unique indexes will cause an error if you try and insert the same data twice in the indexed table. Adding unique indexes to an existing table can be challenging if there is duplicate data in the columns you now believe should be unique. Depending on which RDBMS you are using you can sometimes get it to create the index anyway, but not allow any new rows to be created that break the rule. Postgres does not allow this if my reading of the documentation is correct.