34

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?

Improve this question

2 Answers 2

Reset to default
30

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks, I updated my questions just a bit. I am actually looking purely for equality so I am guessing a hash index will be the best.
Scratch that, hash doesn't work across multiple columns. Guess I will have to do some experimentation to see if 2 separate hashes will be faster then 1 single b-tree
You generally don't want to use a hash index. From the pg 9.1 docs: "Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. They are also not replicated over streaming or file-based replication. For these reasons, hash index use is presently discouraged."
GiST is (among other things) used to index the geometry data type that comes with the PostGIS extension
4

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.