How to determine what type of index to use in Postgres?

Question

I have a Postgres database that has 2 columns that are not primary keys (nor can be), but are searched on a lot and are compared for equality to 2 columns in other tables.

I believe this is a perfect case for adding an index to my tables. I have never used indexing on a database before so I am trying to learn the proper way of doing this.

I have learned that there are multiple types of indexing I can pick from. How do I determine what method will be the most efficient for my database? Also would the proper method be to create a single index that covers both columns?

Answer 1

Postgres support B-tree, R-tree, Hash, GiST and GIN indexing types. B-tree indexing is the most common and fits most common scenarios. This is the syntax:

CREATE INDEX idex_name ON table_name USING btree(column1, column2); 

Here is the createindex documentation and here is more info on different indextypes in postgres.

What type of index you should use depends on what types of operations you want to perform. If you simply want equality checking then hash index is the best. For most common operations(e.g. comparison, pattern matching) B-tree should be used. I have personally never used GiST or GIN indexing. ANY Guru out there?

The documentation describes all these types. They can help you better than me :)

Hope this helps.

Answer 2

Try to understand the queryplanner as well, because this part of PostgreSQL has to work with your indexes. EXPLAIN ANALYZE will be essential to optimise your queries.