This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
There are three main index types. Each type is used for a specific query because they rely upon different algorithm calculations. The three types of indices are:
The B-tree index originated with Lehman-Yao's high-concurrency B-trees. This index is used to calculate comparison queries which involve the operators in the following chart.
Table 5-2. Operators which require B-tree
B-tree is also the default index created by the CREATE INDEX command.
This type of index performs searches for special data. It uses Guttman's quadratic split algorithm to perform an R-tree search. Creating an R-tree index requires you to specify USING RTREE in the command. To create an R-tree index, use the following syntax:
Example 5-3. RTREE index
CREATE INDEX name ON table USING RTREE (column);
The table below shows the operators used in a query that would initialize the system to use this R-tree index:
Table 5-3. Operators which require R-tree
A hash index uses the implementaion of Litwin's linear hashing. The only operator that requires a hash index to perform a comparison is the equal to (=) operator. Similar to an R-tree index, a hash index requires that you specify hash in the command statement. Use the following to create a hash index:
Example 5-4. Creating a Hash Index
CREATE INDEX name ON table USING HASH (column);
Hash indices are rarely used. You may have noticed that B-tree indices are also used when the equal to (=) operator is specified in a query. Currently, there is no evidence to support that a hash index is faster than using a B-tree index.
 
Continue to: