Understanding PostgreSQL index types

So far, only binary trees have been discussed. However, in many cases, B-trees are just not enough. Why is that? As we've already discussed in this chapter, B-trees are basically based on sorting. The <, <=, =, >=, and > operators can be handled using B-trees. The trouble is, not every data type can be sorted in a useful way. Just imagine a polygon. How would you sort these objects in a useful way? Sure, you can sort by the area covered, its length, and so on, but doing this won't allow you to actually find them using a geometric search.

The solution to this problem is to provide more than just one index type. Each index will serve a special purpose and do exactly what is needed. The following six index types are available (as of PostgreSQL 10.0):

test=# SELECT * FROM pg_am;
 amname  | amhandler   | amtype
---------+-------------+--------
 btree   | bthandler   | i
 hash    | hashhandler | i 
 GiST    | gisthandler | i 
 gin     | ginhandler  | i 
 spgist  | spghandler  | i 
 brin    | brinhandler | i 
(6 rows)

B-trees have already been discussed in great detail, but what are those other index types useful for? The following sections will outline the purpose of each index type that's available in PostgreSQL.

Note that there are some extensions out there that can be used on top of what you can see here. Additional index types that are available on the web are rum, vodka, and, in the future, cognac.