Take the 2-minute tour ×

I am thinking of starting to use a PostgreSQL database.

I often have to do different things with the the same data. For example sometimes I have to search for similar strings with LIKE and sometimes I have to check whether a value does or doesn't exist.

In the latter case a Hash index would be best. In the former case, I'm not sure which is best, but I am pretty sure it's not a Hash.

Is it possible to create two different types of index on the same data and then tell the DB which to use as part of an SQL statement.

I'd be interested in any information that is specific to postgresql or relates to the capabilities of relational DMSs in general.

EDIT: My title was incorrect: I changed the word hashes to indexes

share|improve this question
The answers below are correct in the handling of multiple indexes. Based on your question, I'd just like to add one thing - don't use hash indexes in PostgreSQL. They are currently not crash safe. All the other index types (btree, gist, gin) and methods are perfectly fine, of course. –  Magnus Hagander Jul 8 '11 at 9:26
add comment

2 Answers

It is possible to have multiple indexes, and Postgres will automatically use the one it thinks is best suited for your query.

share|improve this answer
Great thanks, but is there a performance hit in that decision process? Would it be better if it was possible to tell the DB which to use? e.g. if I have to do 250 million inserts, but before each one I need to check if the value exists or not, I'd imagine there's some kind of slowing down of performance by not being able to specify the best index to use. –  Ankur Jul 8 '11 at 5:12
For your insert scenario, you should just create a unique index, and Pg will do the rest for you. If you have very unique query requirements where you need to use a special index (i.e. a full-text search), there are ways to use the proper index. –  Flimzy Jul 8 '11 at 5:14
add comment

Postgres will use the best index it can and the overhead of checking which index to use is minimal - it shouldn't even be measurable. You can use prepared statements to eliminate it completely. Far more important is a time needed to manage additional index while inserting and updating data.

So if your table changes often you should limit the number of indexes to minimum.

If your table changes are rare or can be slow then you can create as many indexes as you like. For example in not yet released PostgreSQL 9.1 (but there's second beta available now, suitable for development) you can speed up LIKE searches significantly at the cost of slow updates/inserts.

share|improve this answer
add comment

Your Answer


