Indexing in PostgreSQL is completely programmable. It's traightforward to create a totally custom index type, even with customized operators for how to compare values. A few unusual index types are included with the core database.


The standard index type is the B-tree, where the B stands for balanced. A balanced tree is one where the amount of data on the left and right side of each split is kept even, so that the amount of levels you have to descend to reach any individual row is approximately equal.

The B-tree can be used to find a single value or to scan a range, usually works with the following operators:


Optimizer can also uses B-tree index to combine these operators, including between , in, and like, However, when use like, it only works with the pattern is a constant or a anchar to a beginning of a string such as like 'student%', but not '%52'.

B-tree also can be used to retrive data in sorted order, this is not always faster than a table scan.

Text operator classes

It's possible to use an index on a text field to speed finding rows that start with a substring of interest. It can use an index on that string field to return answers more quickly. This is not useful if your comparson tries to match the middle or end of the column though. In addition, if your database uses something other than the simple C locale, the default way values are compared in  indexes, can't be used for this purpose.


The hash index type can be useful in cases where you are only doing equality searching on an index, and you don't allow NULL values in it. However, it is easy for hash indexes to become corrupted after a database crash, and therefore ineffective for queries until manually rebuilt. The advantages to using a hash index instead of a B-tree are small compared to this risk

You normally shouldn't ever use the hash index type. But if you are willing to spend a considerable amount of time analyzing its usefulness and ensuring the index will be rebuilt if it becomes corrupt, it is possible you can find a purpose for them.


Regular indexes are optimized for the case where a row has a single key value associated with it, so that the mapping between rows and keys is generally simple.

The Generalized Inverted Index(GIN) is useful for a different sort of organization. GIN stores a list of keys with what's called a posting list of rows, each of which contain that key. A row can appear on the posting list of multiple keys too.

GIN is useful for indexing array values, which allows operations such as searching for all rows where the array column contains a particular value, or has some elements in common with an array you're matching against.It's also one of the ways to implement full text search.


A Generalized Search Tree(GiST) provide a ay to build a balanced tree structure for storing data, such as the build in B tree, just by defining how keys are to be treated. This allows using the index to quickly answer questions that go beyond the usual equality and range comparisons handled by a B tree. For example, the geometric data types that are included in PostgreSQL include operators to allow an index to sort by the distance between items and whether they intersect.

GiST can also be used for full text search. When you need a custommized type of index, with operators that go beyod the normal comparison ones, but structure is still like a tree, consider using GiST when a standard database table doesn't perform well.

Comments powered by CComment